Perché sono relazionali basati su un insieme di query meglio di cursori?

Durante la scrittura di query di database in qualcosa di simile a T o PLSQL, abbiamo spesso una scelta di scorrere le righe con un cursore per realizzare il compito, o di creazione di una singola istruzione SQL che fa lo stesso lavoro tutto in una volta.

Inoltre, abbiamo la possibilità di semplicemente tirando un ampio set di dati nella nostra applicazione, e quindi l’elaborazione di una stessa fila, con C# o Java o PHP o qualsiasi altra cosa.

Perché è meglio utilizzare basa su un insieme di query? Che cosa è la teoria dietro questa scelta? Che cosa è un buon esempio di un cursore a base di soluzione e relazionale equivalente?

 

11 Replies
  1. 18

    Il motivo principale per cui io sono a conoscenza è che si basa su un insieme di operazioni può essere ottimizzato attraverso il motore è in esecuzione su più thread. Per esempio, pensare a un quicksort – è possibile separare la lista che stiamo ordinamento in più “blocchi” e ordinare separatamente nel loro proprio thread. SQL motori possono fare cose simili con enormi quantità di dati in una query basata su set.

    Quando si esegue il cursore a base di operazioni, il motore può solo eseguire in sequenza e il funzionamento a singolo thread.

  2. 16

    In aggiunta a quanto sopra “lasciate che i DBMS fare il lavoro” (che è un’ottima soluzione), ci sono un paio di altri buoni motivi per lasciare la query al DBMS:

    • È (soggettivo) di più facile lettura. Quando guardando il codice più tardi, sarebbe piuttosto che provare e analizzare un complesso di stored procedure (o il codice client-side) con i loop e le cose, o sarebbe piuttosto guardare un breve istruzione SQL?
    • Evita di round trip in rete. Perché spingere tutti i dati al client e quindi spingere più indietro? Perché thrash rete, se non è necessario?
    • È uno spreco. Il DBMS e app server(s) sarà necessario un buffer alcuni/tutti i dati di lavoro su di esso. Se non si dispone di infinite memoria è probabile che la pagina altri dati; perché il calcio forse le cose importanti da memoria per il buffer di un set di risultati che è in gran parte inutile?
    • Perché no? Hai comprato (o altro uso) altamente affidabile, molto veloce DBMS. Perché non usarlo?
    • Sono d’accordo con Matt. La lettura di alcuni Joe Celko libri aiuta anche quando si effettua alcune di queste decisioni.
    • Hai dimenticato di citare l’ottimizzazione delle query e natura dichiarativa di SQL; cursori e altre riga gli approcci di rendere definire esattamente come recuperare/elaborare i dati, dove query SQL solo di definire che cosa fare – RDBMS è poi libero di venire con il miglior piano basato sulle statistiche (per esempio a seconda delle statistiche di ricerca indice potrebbe essere peggiore o migliore approccio quindi indice di scansione; RDBMS può fare una distinzione; la riga base di approcci non può…)
  3. 15

    Impostare le query sono (di solito) più veloce perché:

    1. Dispongono di maggiori informazioni per il query optimizer per ottimizzare
    2. Possono batch legge dal disco
    3. C’è meno la registrazione coinvolti per ripristini, i registri delle transazioni, etc.
    4. Meno blocchi, che riduce l’overhead
    5. In base logica è la messa a fuoco di Rdbms, così è stato pesantemente ottimizzato per esso (spesso, a scapito di prestazioni procedurale)

    Estrazione dei dati a livello intermedio di processo può essere utile, però, perché elimina l’overhead di elaborazione off il DB server (che è la cosa più difficile da scalare, e normalmente facendo altre cose). Inoltre, in genere non hanno le stesse spese generali (o i benefici) nel livello intermedio. Cose come la registrazione transazionale, built-in di blocco, etc. – a volte sono necessari e utili, altre volte sono solo uno spreco di risorse.

    Un semplice cursore con una logica procedurale vs basato esempio (T-SQL) che assegnerà un codice di area in base al telefono di cambio:

    --Cursor
    DECLARE @phoneNumber char(7)
    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
       SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
    OPEN c
    FETCH NEXT FROM c INTO @phoneNumber
    WHILE @@FETCH_STATUS = 0 BEGIN
       DECLARE @exchange char(3), @areaCode char(3)
       SELECT @exchange = LEFT(@phoneNumber, 3)
    
       SELECT @areaCode = AreaCode 
       FROM AreaCode_Exchange 
       WHERE Exchange = @exchange
    
       IF @areaCode IS NOT NULL BEGIN
           UPDATE Customer SET AreaCode = @areaCode
           WHERE CURRENT OF c
       END
       FETCH NEXT FROM c INTO @phoneNumber
    END
    CLOSE c
    DEALLOCATE c
    END
    
    --Set
    UPDATE Customer SET
        AreaCode = AreaCode_Exchange.AreaCode
    FROM Customer
    JOIN AreaCode_Exchange ON
        LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
    WHERE
        Customer.AreaCode IS NULL
    • UPDATE Customer SET AreaCode = AreaCode_Exchange.AreaCode FROM Customer JOIN AreaCode_Exchange ON LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange WHERE Customer.AreaCode IS NULL',Can you explain this one SINISTRA(il Cliente.PhoneNumber, 3)` e la sua funzionalità
  4. 8

    Si voleva alcuni esempi di vita reale. La mia azienda ha un cursore che hanno avuto più di 40 minuti per il processo di 30.000 record (e ci sono stati momenti in cui avevo bisogno di aggiornare oltre 200.000 record). Ci sono voluti 45 secondi per fare la stessa operazione senza il cursore. In un altro caso ho rimosso un cursore e inviate il tempo di elaborazione da oltre 24 ore a meno di un minuto. Uno era un inserto utilizzando i valori clausola invece di selezionare e l’altro è stato un aggiornamento che ha usato le variabili invece di un join. Una buona regola è che se si tratta di un insert, update o delete, si dovrebbe cercare un set di base di un modo per eseguire il compito.

    I cursori hanno i loro usi (o il codice non essere loro, in primo luogo), ma dovrebbero essere molto rari, quando una query di un database relazionale (ad Eccezione di Oracle, che è ottimizzato per l’utilizzo di loro). Un luogo dove si può essere più veloce è fare i calcoli in base al valore del precedente record (totali parziali). Ma anche che dovrebbe essere testato.

    Un altro limitato caso di utilizzo di un cursore, è l’elaborazione in batch. Se si sta cercando di fare troppo in una volta nel set di base di moda, è possibile bloccare la tabella di altri utenti. Se si havea veramente grande, può essere meglio per rompere in piccoli set a base di inserimenti, eliminazioni o aggiornamenti che non tenere il blocco troppo lungo e quindi eseguire attraverso il set utilizzando un cursore.

    Un terzo utilizzo di un cursore per eseguire stored procedure di sistema, attraverso un gruppo di valori di input. Dal momento che questo è limitato a generalmente piccolo insieme e nessuno dovrebbe pasticciare con il sistema di procedure, questa è una cosa accettabile per un amministratore di fare. Io non consiglio di fare la stessa cosa con un utente creato per la stored procedure, al fine di elaborare un batch di grandi dimensioni e per il riutilizzo del codice. È meglio scrivere un set-versione base che sarà un esecutore migliore come prestazioni dovrebbe trump riutilizzo del codice nella maggior parte dei casi.

  5. 3

    Penso che la vera risposta è, come tutti gli approcci di programmazione, che dipende da quale è meglio. In generale, un set di base di lingua sta per essere più efficienti, perché questo è ciò che è stato progettato per fare. Ci sono due luoghi in cui un cursore è in vantaggio:

    1. Si sta aggiornando un ampio set di dati in un database, in cui chiusura righe non è accettabile (durante le ore di produzione forse). Un set di base aggiornamento ha la possibilità di bloccare una tabella per alcuni secondi (o minuti), in cui un cursore (se scritto correttamente) non. Il cursore si snodano attraverso le righe di aggiornamento, uno alla volta, e non è necessario preoccuparsi di influenzare qualsiasi altra cosa.

    2. Il vantaggio di utilizzare SQL è che il grosso del lavoro per l’ottimizzazione è gestita dal motore di database nella maggior parte dei casi. Con la classe enterprise motori db i progettisti sono andati all’accurata lunghezze per assicurarsi che il sistema è efficiente al trattamento dei dati. Lo svantaggio è che SQL è un set di base di lingua. Devi essere in grado di definire un set di dati da utilizzare. Anche se questo sembra facile, in alcune circostanze non è. Una query può essere così complesso che l’interno degli ottimizzatori di potenza del motore non può effettivamente creare un percorso di esecuzione, e indovinate cosa succede… il tuo super potente con 32 processori utilizza un singolo thread per eseguire la query, perché non sanno fare altro, in modo che si rifiuti di tempo del processore del server di database che in genere è uno solo invece di più application server (in modo che alla ragione 1, si esegue in risorse contese con le altre cose la necessità di eseguire sul database server). Con una riga di base del linguaggio C#, PHP, JAVA, etc.), hai più il controllo di ciò che accade. È possibile recuperare un set di dati e la forza per eseguire il modo in cui si desidera. (Separare i set di dati a correre su più thread, ecc). La maggior parte del tempo, ancora non sta per essere efficiente in esecuzione sul motore di database, perché si avrà ancora accesso il motore per aggiornare la riga, ma quando si ha a che fare 1000+ calcoli per aggiornare una riga (e diciamo che sono un milione di righe), un server di database può iniziare ad avere problemi.

  6. 1

    Penso che si tratta di usare il database è stato progettato per essere utilizzato. Database relazionale server sono stati sviluppati e ottimizzati specificamente per rispondere meglio alle domande espresso nella logica.

    Funzionalmente, la pena per i cursori variano molto da prodotto a prodotto. Alcune (la maggior parte?) rdbms sono costruito, almeno parzialmente, sulla cima di isam motori. Se la domanda è appropriato, e impiallacciatura sottile abbastanza, potrebbe infatti essere il più efficiente utilizzare un cursore. Ma una delle cose che si dovrebbe diventare intimamente familiare con, in termini di brand di dbms, prima di tentare di esso.

  7. 1

    Come è stato detto, il database è ottimizzato per le operazioni di impostazione. Letteralmente ingegneri sedette e debug/sintonizzato che database per lunghi periodi di tempo. Le possibilità di ottimizzazione di loro sono abbastanza sottile. Ci sono tutti i tipi di divertimento trucchi si può giocare se si dispone di un set di dati da utilizzare come dosaggio disco si legge/scrive insieme, la memorizzazione nella cache, il multi-threading. Anche alcune operazioni hanno un alto costo, ma se lo si fa per un po ‘ di dati in una sola volta il costo di ogni pezzo di dati è bassa. Se si lavora solamente una riga alla volta, un sacco di questi metodi e operazioni appena non può accadere.

    Per esempio, basta guardare il modo in cui il database di join. Cercando di spiegare i piani si possono vedere diversi modi di fare il join. Probabilmente con un cursore che si va riga per riga in una tabella e quindi selezionare i valori desiderati da un’altra tabella. In pratica è come un ciclo nidificato solo senza la tenuta del ciclo (che è più probabile compilato in linguaggio macchina e super ottimizzato). SQL Server sulla propria ha tutta una serie di modi per partecipare. Se le righe sono ordinate, si potrà utilizzare un certo tipo di algoritmo di merge, se una tabella è piccola, può trasformare una tabella in un hash tabella di ricerca e fare il join di esecuzione O(1) ricerche da una tabella nella tabella di ricerca. Ci sono un certo numero di strategie di join che molti DBMS, che ci batte, ricerca di valori da una tabella in un cursore.

    Basta guardare l’esempio di creazione di un hash tabella di ricerca. Per costruire la tabella è probabilmente di operazioni di m se si stanno unendo due tabelle, una di lunghezza n e uno di lunghezza m dove m è il tavolo più piccolo. Ogni ricerca deve essere costante, tanto che è n operazioni. quindi, fondamentalmente, l’efficienza di un hash join è di circa m (setup) + n (ricerche). Se lo fate voi stessi e non assumendo ricerche/indici, quindi, per ciascuna delle n righe si dovrà cercare m records (in media pari a m/2 ricerche). Quindi, fondamentalmente, il livello delle operazioni va da m + n (unione di un gruppo di record in una volta) a m * n /2 (facendo ricerche tramite un cursore). Anche le operazioni sono semplificazioni. A seconda del tipo di cursore, il recupero di ogni riga di un cursore può essere la stessa cosa che fare un altro selezionare dalla tabella.

    Blocca anche uccidere. Se si dispone di cursori su un tavolo si sono blocco di righe (in SQL server questo è meno grave per la statica e l’opzione forward-only cursori…ma la maggior parte del codice del cursore, vedo che appena si apre un cursore senza specificare una qualsiasi di queste opzioni). Se si esegue l’operazione in un insieme, le righe saranno ancora bloccato, ma per una minore quantità di tempo. Anche l’ottimizzatore può vedere cosa si sta facendo e si può decidere è il più efficiente per bloccare l’intera tabella, invece di un mucchio di righe o pagine. Ma se vai in linea per linea, l’ottimizzatore non ha alcuna idea.

    L’altra cosa è che ho sentito che in Oracle caso è super ottimizzato per fare operazioni del cursore, quindi è in nessun posto vicino la stessa pena per il set base di operazioni contro i cursori in Oracle come in SQL Server. Io non sono un esperto Oracle, quindi non posso dire con certezza. Ma più di un Oracle persona mi ha detto che i cursori sono il modo più efficiente in Oracle. Quindi, se hai sacrificato il tuo figlio primogenito di Oracle, non si può avere a preoccupare i cursori, consultare il vostro locale altamente pagato DBA Oracle 🙂

  8. 0

    L’idea preferendo fare il lavoro in query è che il motore di database in grado di ottimizzare riformulare esso. Questo è anche il motivo per cui vuoi eseguire SPIEGARE su query, vedere ciò che il db è effettivamente facendo. (ad esempio, sfruttando gli indici, le dimensioni della tabella e, talvolta, anche la conoscenza circa le distribuzioni dei valori in colonne.)

    Che ha detto, per ottenere un buon rendimento nelle concrete ed effettive caso, potrebbe essere necessario piegare o rompere le regole.

    Oh, un altro motivo potrebbe essere vincoli: l’Incremento di un unica colonna, uno potrebbe essere giusto se i vincoli sono verificate dopo tutti gli aggiornamenti, ma genera una collisione se fatto uno per uno.

  9. 0

    set di base è fatto in una sola operazione
    cursore molte operazioni come il set di righe con il cursore

  10. 0

    La VERA risposta è andare a prendere uno di E. F. Coddi libri e pennello su algebra relazionale. Quindi ottenere un buon libro su Notazione O grande. Dopo quasi due decenni questo è, IMHO, una delle grandi tragedie del moderno MIS o CS grado: Molto pochi in realtà lo studio del calcolo. Sai…”calcola”, parte di “computer”? Structured Query Language (e tutti i suoi superset) è semplicemente una pratica applicazione dell’algebra relazionale. Sì, RDBMS hanno ottimizzato la gestione della memoria e di lettura/scrittura, ma lo stesso si potrebbe dire per i linguaggi procedurali. Come ho letto, la domanda originale non è l’IDE, il software, ma l’efficienza di un metodo di calcolo contro l’altro.

    Anche una rapida familiarizzazione con la notazione O Grande inizierà a far luce sul motivo per cui, quando si tratta di insiemi di dati, l’iterazione è più costoso di una dichiarazione.

  11. 0

    Semplicemente, nella maggior parte dei casi, è più veloce/più facile lasciare che il database di farlo per voi.

    Del database scopo nella vita è quello di archiviare e recuperare/manipolare i dati nel set di formati e di essere molto veloce. Il VB.NET/ASP.NET il codice è probabile che in nessun posto vicino come veloce come un motore database dedicato. Sfruttando questo è un sapiente uso delle risorse.

Lascia un commento