kurzorok úgy nézhetnek ki, mint egy fejlesztő parancsikonjai. Ha van egy összetett feladat, hogy végre, és meg kell manipulálni a sorok egy táblázatban, a leggyorsabb módja úgy tűnik, hogy végigmegy a sorok egyesével egy Transact-SQL kurzor. Végül is, mivel az ügyféloldalon a saját kódjában lévő adatstruktúrákon keresztül kell iterálni, kísértés lehet ugyanezt tenni, ha SQL Server adatokkal foglalkozik., De a Transact-SQL kurzorokon keresztül történő iterálás gyakran nem terjed ki jól, és remélem, hogy meggyőzöm Önt arról, hogy ez nem is jó tervezési vagy építészeti gyakorlat.

A Cursor Experience

ezt azért hozom fel, mert néhány hónappal ezelőtt foglalkoznom kellett egy szállító Transact-SQL szkriptjével, amely frissítette adatbázis-összetevőjét az eladó alkalmazásának új verziójára. A szkriptet úgy tervezték, hogy egy nagyon nagy táblázatot forgasson el, és a releváns adatokat vízszintesen tárolja az új táblázatban, összekapcsolt karakterláncokként., Az eladó a teljesítményt úgy akarta javítani, hogy az asztal kisebb lett, ezért úgy döntöttek, hogy a részletadatokat vízszintesen tárolják, vesszővel elválasztott karakterláncként minden szülőazonosítóhoz. A kliens alkalmazás gyorsabban lekérdezheti a kapott vesszővel elválasztott karakterláncokat, mint hogy mindegyiket egyedi sorként kapja meg, a kontextusban pedig a változásnak volt értelme, és javította az alkalmazás teljesítményét.

azonban az eladó Transact-SQL szkriptje az adatok elforgatásához a frissítés során 16 órát vett igénybe egy tesztgépen történő futtatáshoz, az ügyfél pedig nem engedhetett meg magának több mint néhány órányi leállást a frissítéshez., Amikor megvizsgáltuk az eladó forgatókönyvet, láttuk, hogy a fejlesztő volt kódolva a állítási folyamat két lépésben: a kurzort halad át a szülő táblázat azonosítók építeni egy üres előre formázott táblázatot, majd egy másik forgatókönyvet, hogy összefűzni a szálakat, újra használja a kurzor.

egy set-alapú megközelítés segítségével a feldolgozási időt 16 plusz óráról kevesebb, mint öt percre tudtuk csökkenteni. Követtük a fejlesztő eredeti stratégiáját, elkészítettük az üres táblát SELECT utasításokkal, és ennek a lépésnek az idejét kevesebb mint két percre csökkentettük., Ezután összefűztük a karakterláncokat egy frissítési utasítás segítségével, végrehajtva szülőazonosítónként. A szülőazonosítókon keresztüli iterációnk egy rövid ciklust használt, és kevesebb, mint három perc alatt befejeződött.

az elkerülhetetlensége iteráció

sok hozzáférést adatbázis adatokat kell iteratív valamilyen módon annak érdekében, hogy előkészítse az adatokat a további manipuláció. Még az SQL Server motor is átmegy az adatokon, amikor beolvassa vagy összekapcsolja az adatokat a rendelkezésre álló különféle típusú csatlakozások segítségével. Ezt akkor láthatja, ha megvizsgálja az SQL Server lekérdezési tervét egy olyan lekérdezéshez, amely sok sort ad vissza egy nagy adatkészletből., Egy csatlakozni, akkor a leggyakrabban látni egy beágyazott hurok, de néha egy merge vagy hash csatlakozni. Az egyszerűbb lekérdezéseknél előfordulhat, hogy fürtözött vagy nem fürtözött indexszkennelés történik. Ez csak abban az esetben, ha az SQL Server vissza egy sor vagy kis sorokat, és a táblázat egy megfelelő index, hogy látni fogja a keresést egy index.

Gondolj bele: a Microsoft évek óta optimalizálja és hangolja az SQL Server motorját, hogy a lehető leghatékonyabban iterálja a rendelkezésre álló adatokat., Képzelje el, ha lenne ideje, és hajlandó lenne energiát költeni, valószínűleg alacsony szintű hozzáféréseket írhat az adatbázis-adatfájlokhoz, amelyek elég hatékonyak lennének. Ez azonban csak az Ön előtt álló egyéni feladatra lenne hatékony, hibakeresésre lenne szükség, és lehet, hogy teljesen újra kell írnia, ha az adathozzáférés hatóköre megváltozik. Valószínűleg évekbe telne, amíg a kódot teljesen optimalizálják és általánosítják, és még akkor sem lenne közel a kód hatékonyságához az SQL Server storage engine-en belül.,

tehát hol van a kerék újbóli feltalálásának nyeresége? Ez csak azért van, mert az SQL Server motor annyira jól optimalizált, hibakeresés, hogy jobb, ha hagyja, hogy ez az iterating az Ön számára, és kihasználják a kiterjedt fejlesztés, tesztelés, amely már be van ágyazva az adatbázisba.

ha közelebbről megvizsgálja az adatfeldolgozási feladatokat, azt hiszem, azt fogja találni, hogy nagyon kevés olyan alkalom van, amikor kurzorokra van szükség. Először is, gyakran lehet elérni a célt támaszkodva a Set-alapú SQL parancsok Transact-SQL, figyelmen kívül hagyva a sorrendben a táblázat sorai., Másodszor, a Transact-SQL kurzorok csak egy módja annak, hogy iterálják egymást egy táblázat soronként. Ha egyedileg azonosítja a táblázat minden sorát, amelyet meg kell ismételnie, akkor a kurzor helyett egy WHILE loop-ot használhat, és potenciálisan jobb teljesítményt érhet el. Hadd mutassak egy példát, hogy megmutassam, miért.

az iterációs stratégiák összehasonlítása

feltételezzük, hogy egyedileg azonosíthatja a táblázat minden sorát, mivel a táblázat egyedi kulccsal vagy egyedi oszlopcsoporttal rendelkezik., EGY WHILE hurokban mindössze annyit kell tennie, hogy megtalálja az egyedi állapot legalacsonyabb értékét, majd minden egyes iteráció során megtalálja a következő legmagasabb értéket. Íme egy példa az SQL Server 2005 AdventureWorks minta adatbázisok termelés.TransactionHistory táblázat. Az elsődleges kulcson fürtözött index van, a WHILE loop pedig minden alkalommal megkeresheti a sort.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Itt ugyanaz a hurok használata GYORS ELŐRE-kurzor, amely a leghatékonyabb típusú Transact-SQL kurzor csak olvasási adatok:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

a laptop, miután futottam egy párszor, hogy győződjön meg arról, hogy az adatok csak a cache, a while ciklus veszi kilenc másodperccel a kurzor veszi 17 másodperc. A saját időtartama változhat. Vegye figyelembe, hogy annak ellenére, hogy a példa valóban nem tesz semmit az adatokkal, a WHILE loop gyorsabb. A kurzor nyilvánvalóan több felső értéket ad.,

a kurzor további parancsokat is igényel, amelyek miatt a kód zsúfoltnak tűnik. Anélkül, hogy a részleteket, hogyan kurzorok munka, amely a Microsoft magyarázza teljes mértékben a Microsoft SQL Server 2005 Books Online, figyeljük meg, hogy amikor egy while ciklus, nem követelmény, hogy állapítsa meg, megnyitása, bezárása, valamint a deallocate semmit. A logika egyszerűbb, sőt szabadon frissítheti a sorokat az út mentén. A sorok frissítéséhez a kurzor segítségével meg kell változtatnia a kurzor típusát.

még egy ideig hurok hozzáadja az iteráció felső részét., Lehet, hogy kicserélheti egy set-alapú SELECT parancsra, vagy kicserélheti a hurokban kívánt frissítéseket a set-based UPDATE paranccsal, majd az iterációt az SQL Server motorra hagyja. Egy egyszerű SELECT utasítás, hogy ugyanazokat az adatokat kapjuk, mint a kurzorunk, míg a fenti hurok kevesebb, mint 3 másodpercet vesz igénybe, és a sorokat visszaadja az ügyfélnek, ami több munka, mint a két korábbi hurok.

SELECT *FROM Production.TransactionHistory

Ez a kijelölés az SQL Serverre támaszkodik az adatok iterálására, és messze a leggyorsabb az általunk vizsgált három adathozzáférési módszer közül.,

A zsákoktól a

készletekig néha szükség lehet kurzorokra. Ha egyszerűen át kell iterálni az adatbázis adatait, egymás után, fizikai sorrendben, néha csak a kurzor fog működni. Ez leggyakrabban akkor fordul elő, ha ismétlődő sorok vannak, és nincs mód arra, hogy egy adott sort egyedileg azonosítsunk a táblázatban. Ezek a táblázatok zsákok, nem készletek, adatok, mivel a “táska” nem szünteti meg a duplikált értékeket, mint egy készlet.

Az ilyen adatcsomagok általában akkor fordulnak elő, amikor adatokat importál egy külső forrásból, és nem bízhat teljesen az adatokban., Például, ha a AdventureWorks tranzakciós előzménytáblán nem volt olyan oszlopcsoport, amelyet egyedinek hívhat, és / vagy ismétlődő sorokkal rendelkezik, akkor azt gondolhatja, hogy kurzort kell használnia.

azonban, akkor mindig viszont egy zsák sorok egy normalizált táblázat. Még akkor is, ha duplikált sorok vannak a táblázatban, vagy nincs olyan oszlopkészlet, amelyre hivatkozhat az egyediség érdekében, hozzáadhat egy identitás oszlopot a táblázathoz, majd az identitást az 1 számozáshoz. Ez egy egyedi kulcsot ad a táblához, amely lehetővé teszi, hogy kurzor helyett egy WHILE hurkot használjon., Miután rendelkezik egy egyedi kulccsal, eltávolíthatja a másolatokat a Transact-SQL set-based UPDATE paranccsal.

A logikai API adatbázis adatok

segítségével set-base műveletek jobb, mint iterating az adatokat magát legalább két módon.

először is, a set-alapú SQL parancsok hatékonyabbak, mert az SQL Server erősen optimalizált motorját használja az iteráció elvégzéséhez. Ha Ön is átmásolja az adatokat, akkor nem használja optimálisan az SQL Server storage engine-t. Helyette, Ön Borsos azt parancsokat letölteni csak egy sorban egy időben., Minden alkalommal, amikor egyetlen sort kér, a parancsnak át kell mennie az SQL Server optimizer-en, mielőtt eljuthat a tároló motorhoz, és végül nem használja az SQL Server storage engine optimalizált kódját. Ha megismételte magát, akkor az adatok feldolgozásakor a táblázattal kapcsolatos idegen fizikai információkra, nevezetesen a sorok sorrendjére is támaszkodik. A set-base Transact-SQL SELECT, UPDATE, and DELETE parancsok lehetőséget adnak arra, hogy figyelmen kívül hagyja a sorok sorrendjét, és csak az adatok jellemzői alapján befolyásolja őket-és gyorsabbak.,

Második, set-alapú parancsok logikus, mert gondoltam adatok készletben abstracts távol idegen részleteket, hogy jobban aggódik, hogy az adatok valóban elrendelte. Valójában a set-alapú parancsok, mint például a SELECT, UPDATE, and DELETE, ha közvetlenül a táblákra alkalmazzák, nem pedig a kurzorban vagy a WHILE hurokban, logikusan közelebb hozzák az adataihoz, pontosan azért, mert figyelmen kívül hagyhatja az adatok sorrendjét.,

Itt van egy másik módja annak, hogy gondolni, hogy ez a második pont-Pont, mint a tárolt eljárás, vagy a természetes API alkalmazások felület SQL Server programból, így a set-alapú SQL parancsokat a megfelelő API elérése a kapcsolati adatok. A tárolt eljárások elválasztják az alkalmazást az adatbázis belső oldalaitól, és hatékonyabbak, mint az Ad hoc lekérdezések. Hasonlóképpen, a Transact-SQL-en belüli set-base SQL parancsok logikai felületet adnak a relációs adatoknak, és hatékonyabbak, mivel az SQL Server storage engine-re támaszkodik az adatok iterálására.,

a lényeg nem az, hogy az adatokon keresztül történő iterálás rossz. Valójában gyakran elkerülhetetlen. Inkább az a lényeg, hogy hagyja, hogy a tárolómotor tegye meg az Ön számára, ehelyett a set-based Transact-SQL parancsok logikai felületére támaszkodjon. Azt hiszem, talál néhány, ha olyan helyzetekben, ahol meg kell ténylegesen használni a Transact-SQL kurzor.