Osoittimet voi näyttää pikakuvakkeet kehittäjä. Kun sinulla on monimutkaista työtä, suorittaa ja sinun täytyy manipuloida taulukon rivit, nopein tapa voi tuntua kerrata läpi rivit yksi kerrallaan käyttäen Transact-SQL-kursori. Loppujen lopuksi, koska sinun täytyy iteroida kautta tietorakenteiden oman koodin asiakkaan puolella, saatat olla houkutus tehdä sama, kun olet tekemisissä SQL Server tiedot., Mutta iterointi tietojen avulla Transact-SQL cursors usein ei skaalata hyvin, ja toivon vakuuttaa teille, että se ei ole myöskään hyvä suunnittelu tai arkkitehtoninen käytäntö.
Kursori Kokemus
tuon tämän esille, koska muutama kuukausi sitten, minun piti käsitellä toimittajan Transact-SQL-skripti, joka päivittää niiden tietokannassa komponentin uuden version toimittajan sovellus. He suunnittelivat skriptin kääntämään hyvin suuren taulukon ja tallentamaan asiaankuuluvat tiedot uuteen taulukkoon vaakatasossa, kongenoituina merkkijonoina., Myyjä halusi parantaa suorituskykyä tekemällä taulukko pienempi, joten he päättivät tallentaa yksityiskohtaisesti tiedot horisontaalisesti, kuten pilkulla erotettuna jouset kunkin vanhemman id. Asiakkaan sovellus voi kyselyn tuloksena pilkulla erotettuna jouset nopeammin kuin saada jokainen niistä yksittäisiä rivejä, ja siinä yhteydessä tehty muutos mielessä ja teki parantaa sovelluksen suorituskykyä.
Kuitenkin, toimittajan Transact-SQL script pivot-tietojen päivityksen aikana kesti 16 tuntia ajaa testi kone, ja asiakas ei ollut varaa enemmän kuin muutaman tunnin seisokkeja päivitykseen., Kun tutkimme toimittajan kirjoitus, näimme, että kehittäjä oli koodattu kääntyvä prosessin kahdessa vaiheessa: kursori kerrata läpi kaikki vanhemman taulukko tunnukset rakentaa tyhjä alustettu valmiiksi pöytään, ja sitten toinen kirjoitus concatenate strings, uudelleen käyttämällä kursoria.
käyttämällä set-pohjainen lähestymistapa, pystyimme vähentää käsittelyaika 16 tuntia alas alle viidessä minuutissa. Noudatimme rakennuttajan alkuperäistä strategiaa, rakensimme tyhjän pöydän SELECT-lausekkeilla, ja lyhensimme tuon vaiheen aikaa alle kahteen minuuttiin., Sen jälkeen sovimme merkkijonot käyttäen PÄIVITYSTILANNETTA, joka suoritetaan vanhempainpäivitystä kohden. Iteraatiomme kautta emotunnukset käytetään jonkin aikaa silmukka, ja valmis alle kolme minuuttia.
Väistämättömyyden Iteraation
Monet käyttää tietokannan tietojen on oltava iteratiivinen jollakin tavalla, jotta voidaan valmistella tiedot edelleen manipulointi. Jopa SQL Server engine iteroituu tietojen kautta, kun se skannaa tai liittää tietoja käyttäen erilaisia liitoksia käytettävissä. Näet tämän, kun tutkit SQL Server-kyselysuunnitelmaa kyselylle, joka palauttaa monta riviä suuresta datasarjasta., Jos haluat liittyä, näet yleisimmin sisäkkäisen silmukan, mutta joskus myös yhdistämisen tai hash liittyä. Yksinkertaisemmissa kyselyissä voi nähdä klusteroidun tai ei-klusteroidun indeksikuvauksen. Se on vain tapauksissa, joissa SQL Server voi palauttaa yhden rivin tai pieni joukko rivejä, ja pöytä on sopiva indeksi, että näet hakea indeksillä.
Mieti sitä: Microsoft on optimoitu ja viritetty SQL Server moottori vuotta kerrata sen kautta käytettävissä olevia tietoja mahdollisimman tehokkaasti., Kuvittele, jos sinulla olisi aikaa ja olisit valmis käyttämään energiaa, voisit luultavasti kirjoittaa matalan tason käyttöoikeudet tietokantaan tiedostoja, jotka olisivat melko tehokkaita. Kuitenkin, se olisi tehokas vain yksittäisen tehtävän edessä, ja sinun täytyy debug ja ehkä täysin muuttaa sitä, jos soveltamisala data access muuttaa. Se olisi todennäköisesti vie vuosia todella saada täysin optimoitu koodi ja yleistynyt, ja jopa niin et olisi lähellä tehokkuutta koodin sisällä SQL Server storage engine.,
joten missä on hyöty pyörän uudelleen keksimisestä? Se on vain, koska SQL Server-moottori on niin hyvin optimoitu ja debugged, että se on parempi anna sen tehdä iteroimalla sinulle ja hyödyntää laaja kehittäminen ja testaus, joka on jo upotettu tietokantaan.
jos tutkit tarkemmin tietojenkäsittelytehtäviäsi, huomaat, että cursoreita tarvitaan todella harvoin. Ensinnäkin, usein, voit saavuttaa tavoitteesi vetoamalla set-pohjainen SQL-komentoja Transact-SQL, ja unohdetaan järjestyksessä taulukon rivien., Toiseksi, Transact-SQL-kursorit ovat vain yksi tapa iteroida läpi taulukon rivi riviltä. Jos voit yksilöllisesti tunnistaa jokaisen rivin taulukon, että sinun täytyy iteroida, voit käyttää jonkin aikaa silmukka sijaan kursori, ja mahdollisesti saada paremman suorituskyvyn. Näytän sinulle esimerkin.
Vertaamalla Iteraation Strategiat
– Oletan, voit yksilöi kunkin rivin taulukkoon, koska taulukko on yksilöllinen avain tai ainutlaatuinen ryhmä sarakkeita., Hetken silmukka, sinun tarvitsee vain löytää alin arvo ainutlaatuinen ehto, ja sitten löytää seuraavaksi korkein arvo joka kerta iteroida. Tässä on esimerkki SQL Server 2005 AdventureWorks näyte tietokannat tuotanto.Transactionhistoriallinen taulukko. Siinä on pääavaimessa klusteroitu indeksi, ja WHILE-silmukka voi hakeutua riviin joka kerta.,
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)ENDTässä on sama silmukka käyttäen NOPEASTI ETEENPÄIN kohdistin, joka on tehokkain tyyppi Transact-SQL-kursori vain tietojen lukeminen:
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– minun laptop, kun juoksin sen muutaman kerran varmista, että tiedot on kaikki välimuisti, KUN silmukka kestää yhdeksän sekuntia ja kohdistin kestää 17 sekuntia. Omat kestosi voivat vaihdella. Huomaa, että vaikka esimerkki ei todellakaan tee mitään tietojen kanssa, vaikka silmukka on nopeampi. Kursori ilmeisesti lisää yläpuolta.,
kursori vaatii myös lisäkomennot, jotka saavat koodin näyttämään sekaiselta. Menemättä yksityiskohtiin siitä, miten osoittimet työtä, joka Microsoft selittää täysin Microsoft SQL Server 2005-Kirjoja Verkossa, huomaat, että kun käyttää WHILE-silmukka, ei ole mitään tarvetta julistaa, avata, sulkea, ja deallocate mitään. Logiikka on yksinkertaisempi, ja voit jopa päivittää rivejä vapaasti matkan varrella. Jos haluat päivittää rivit kursorin avulla, sinun on vaihdettava kursorin tyyppiä.
jo jonkin aikaa silmukka lisää iteraation ylimenoa., Voit ehkä korvata sen set-pohjainen VALITSE komento, tai korvata mitään päivityksiä haluat tehdä teidän silmukka set-pohjainen PÄIVITYS-komento, ja jättää iteroimalla SQL Server moottori. Yksinkertainen SELECT saada samat tiedot kuin meidän kursori ja KUN silmukka edellä vie vähemmän kuin 3 sekuntia, ja se palauttaa rivejä asiakas, joka on enemmän työtä kuin kaksi edellistä silmukoita tehdä.
SELECT *FROM Production.TransactionHistoryVALITSE Tämä perustuu SQL Server kerrata läpi tiedot, ja on ylivoimaisesti nopein kolme menetelmiä data access olemme katseli.,
pusseista setteihin
joskus kursorit saattavat tuntua tarpeellisilta. Kun sinun täytyy yksinkertaisesti iteroida tietokantatietojen kautta rivi riviltä, niiden fyysisessä järjestyksessä, joskus vain kursori toimii. Tämä tapahtuu yleisimmin, kun sinulla on päällekkäisiä rivejä eikä ole mitään keinoa yksilöidä tiettyä riviä taulukossa. Nämä taulukot ovat pusseja, ei sarjoja, tietoja, koska ”pussi” ei poista päällekkäisiä arvoja, kuten joukko tekee.
tällaisia tietopusseja esiintyy yleensä, kun tuot tietoja ulkoisesta lähteestä, etkä voi täysin luottaa tietoihin., Esimerkiksi, jos meidän AdventureWorks tapahtumahistoriataulukon ei ollut ryhmän sarakkeet, että sitä voisi kutsua ainutlaatuinen, ja/tai oli päällekkäisiä rivit, saatat ajatella, että sinun täytyy käyttää osoitinta.
voit kuitenkin aina kääntää rivipussin normalisoiduksi taulukoksi. Vaikka sinulla on päällekkäisiä riviä taulukossa, tai ei joukko sarakkeita voit luottaa ainutlaatuisuus, voit lisätä tunnistetiedot sarake taulukkoon ja siementen identiteetti aloittaa numerointi 1. Tämä lisää pöydälle ainutlaatuisen avaimen, jonka avulla voit käyttää kursorin sijaan jonkin aikaa., Kun sinulla on ainutlaatuinen avain, voit poistaa kaksoiskappaleet käyttämällä Transact-SQL-asetukseen perustuvaa PÄIVITYSKOMENTOA.
Looginen API-Tietokanta
Käyttämällä set-base toiminta on parempi kuin iteroimalla tiedot itse ainakin kahdella tavalla.
ensin set-pohjaiset SQL-komennot ovat tehokkaampia, koska iterointiin käytetään SQL Serverin erittäin optimoitua moottoria. Jos iteroit datan itse, et käytä SQL Server storage engine optimaalisesti. Sen sijaan, olet peppering sitä komennot hakea vain yhden rivin kerrallaan., Joka kerta kun pyytää yhden rivin komento täytyy mennä läpi SQL Server optimizer, ennen kuin se voi saada storage engine, ja voit päätyä ei ole käytössä SQL Server storage engine on optimoitu koodi. Jos iteroitava itse, voit myös luottavat ylimääräisiä fyysisiä tietoja taulukossa, eli rivien järjestyksen, kun tietojen käsittelyä. Set-base Transact-SQL-SELECT, UPDATE-ja DELETE-komentoja antaa sinulle tapa sivuuttaa rivien järjestyksen ja vaikuttaa niiden ominaisuuksien perusteella tiedot, ja ne ovat nopeampia.,
Toiseksi, set-pohjainen komentoja ovat enemmän looginen, koska ajattelen tiedot esitetään tiivistelmät sinut pois ylimääräisiä yksityiskohtia, jotka ovat enemmän huolissaan, miten tiedot on itse tilannut. Itse asiassa, set-komentoja, kuten SELECT, UPDATE ja DELETE, kun sitä sovelletaan taulukoita suoraan, ja ei-kohdistin-tai WHILE-silmukka, tuo sinut lähemmäksi loogisesti tietosi, juuri siksi, voit jättää tilauksen tietoja.,
Tässä on toinen tapa ajatella tämä toinen kohta-yhtä tallennettu menettelyt ovat kaikkein luonnollinen API-sovellukset, käyttöliittymä SQL Server ohjelmallisesti, joten set-pohjainen SQL-komennot ovat asianmukaiset API saatavuuden relaatio. Tallennetut irrottaa hakemuksesi tietokannasta sisäosat, ja ne ovat tehokkaampia kuin ad hoc-kyselyt. Samoin, set-base SQL-komentoja sisältä Transact-SQL antaa sinulle loogisen käyttöliittymän relaatio, ja ne ovat tehokkaampia, koska voit luottaa SQL Server storage engine for iteroimalla läpi tietoja.,
pohjavire ei ole se, että iterointi datan kautta olisi huono asia. Usein se on väistämätöntä. Pikemminkin pointti on, Anna tallennusmoottorin tehdä se puolestasi ja luottaa sen sijaan asetettujen Transact-SQL-komentojen loogiseen käyttöliittymään. Luulen, että löydät muutamia, jos mitään tilanteita, joissa sinun täytyy todella käyttää Transact-SQL kursori.