SQL Server Silmukan läpi Taulukon Rivit ilman Kursori

By: Daniel Farina | Päivitetty: 2019-09-12 | Kommentit (2) | Liittyvät: Enemmän > T-SQL

Ongelma

Sinulla on aina kuullut, että sinun pitäisi välttää osoittimet inyour T-SQL-koodin kuten SQL Server parhaita käytäntöjä, becausecursors ovat haitallisia performanceand joskus aiheuttaa ongelmia. Mutta joskus on tarpeen silmukan läpi tiedot yhden rivin kerrallaan, joten tässä vinkki tarkastellaan vertaamalla, kuinka doa silmukka ilman kursoria.,

Ratkaisu

Me kaikki tiedämme, että SQL Server, kuten jokainen relaatiotietokannan avulla käyttäjä asettaa toimimaan perustuvat toiminnot. Myös, kuten monet tietokantatoimittajat tekevät, SQL Server includesa procedural extension, joka on T-SQL kieli. Se lisää konstruktioita löytyy procedural kielet mahdollistaa suoraviivaisempi koodaus kehittäjille. Theseconstructs lisättiin syystä ja joskus tämä on ainoa lähestymistapa, tothe tehtävään.,

While-Silmukan Sijaan Osoittimet SQL Server

Jos olet koskaan työskennellyt withcursors, saatat löytää tämä otsikko hieman confusingbecause sen jälkeen, kun kaikki, osoittimet käyttää kun rakentaa kerrata välillä rivien. Mutta haluan näyttää teille, että joissakin olosuhteissa, kun käytämme kursoria toistamaan rivejä, voimme muuttaa sen hetkeksi silmukaksi. Tällaisissa tapauksissa ainoa haaste on valita oikea poistumisedellytys.,

Hyviä ja Huonoja puolia Käyttäen Osoittimet Kerrata Läpi Taulukon Rivit SQL Server

kaikki ei Ole vikaa, osoittimet, niillä on myös joitakin advantagesover muiden silmukoiden tekniikoita.

• kursorit ovat päivitettävissä: kun luot kursorin, määrität sen kyselyllä ILMOITUSKURSORIN ohjeella. Käyttämällä cursorcreation-lausuman päivitysvaihtoehtoa voit päivittää sarakkeet kursorin sisällä.,
• Voit siirtyä eteenpäin ja taaksepäin kursori: käyttämällä SELAA optionin JULISTAA KOHDISTIN lausunnon voit navigoida ympäri kohdistin kirjaa kummassakin ryhmässä suuntiin noutaa vaihtoehtoja, ENSIMMÄINEN, VIIMEINEN, ENNEN, ENSI, SUHTEELLINEN andABSOLUTE. Muista, että VIERITYSVAIHTOEHTO on yhteensopimaton FORWARD_ONLYand FAST_FORWARD-vaihtoehtojen kanssa.
• Osoittimet voidaan siirtää tallennetut: Jos käytät MAAILMANLAAJUINEN optionto luoda osoitin, sitä voidaan käyttää missä tahansa tallennettu menettely tai erän executedin samassa yhteydessä. Näin voit käyttää kursoreita sisäkkäisiin tallennettuihin menettelyihin.,
• Osoittimet on paljon erilaisia vaihtoehtoja: osoittimet sinulla on tilaisuus käyttää erilaisia vaihtoehtoja, jotka vaikuttaa siihen, miten he käyttäytyvät osalta lukitus.
• kursorit eivät tarvitse ehtoa: kursoreita käyttämällä käsipallorivejä ennätyksenä. Tämän avulla voit siirtyä kursorin yli ilman tarvetta ottaa Boolean kunnossa. Esimerkiksi, voit luoda cursorwith nimi tietokannat oleskelevien SQL Server-esiintymä ilman tarve korvike avain työskennellä testi kunnossa kuin WHILE-silmukka.,

On olemassa myös joitakin kielteisiä näkökohtia, jotka sinun pitäisi olla tietoinen, kun käytät cursorsinstead muita looping vaihtoehtoja.

• Jos käytät maailmanlaajuinen osoittimet koodissa olet ottaen riski facingerrors, koska kursori on suljettu joitakin tallennettu menettely sisäkkäisiä yourcode.
• yleensä kursoreilla on vähemmän suorituskykyä kuin vastaavalla silmukalla Whileloopilla tai CTE: llä.

Hyviä ja Huonoja puolia Käyttäen While-Silmukka Kerrata Läpi Taulukon Rivit SQL Server

On myös hyötyä käyttää WHILE-silmukka verrattuna kursori.,

• silmukat ovat cursoreja nopeampia.
• silmukoissa käytetään vähemmän lukkoja kuin kursoreissa.
• vähemmän Tempdb: n käyttöä: vaikka silmukat eivät luo kopiota datan intempdb: stä kuten kursori tekee. Muista, että cursors, riippuen vaihtoehdoista käytät luoda niitä voi aiheuttaa temp taulukoita luodaan.

seuraavassa luettelossa kerrotaan WHILE Loopsin negatiiviset puolet.

• eteenpäin tai taaksepäin on monimutkainen: Voit siirtyä eteen-tai taaksepäin aloop sinun täytyy dynaamisesti muuttaa iteraation ehto silmukan sisällä.,Tämä vaatii ylimääräistä huolellisuutta; muuten voi päätyä äärettömään silmukkaan.
• riski ääretön silmukka: kun kursori, et havea kiinteä joukko tiedon silmukka (eli tiedot palautetaan VALITSE statementin kohdistin-ilmoitus), sen sijaan, kun käytetään, KUN silmukka on määritellä boundarywith lauseke, joka arvioidaan, jotta true tai false.

Rakennus testiympäristön Osoittimet ja Silmukoita

Voit testata tämän, aion käyttää taulukon, jossa identiteetti sarake (CursorTestID),varchar sarake (Täyteaine) ja bigint sarake (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO 

ajatus on silmukan läpi taulukon rivit tilaamat CursorTestID columnand päivittää RunningTotal sarakkeen summa CursorTestID sarake valueand arvo RunningTotal sarake edellisen rivin.

mutta ennen aloittamista, ensin on luotava joitakin testirivejä seuraavalla skriptillä.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

käsikirjoitus edellä huomaat, että käytin vain yhden lisää statementand käytin erä erotin (GO 500000 komento) kuin shortcutto suorittaa tämän insert julkilausuman 500000 kertaa. Voit lukea lisää tästä methodto toista erää toteutus tässä vihje:Suorittamalla T-SQL-erän useita kertoja käyttäen MENNÄ.

Esimerkki Perus Kohdistin Silmukan läpi Taulukon Rivit SQL Server

luodaan kohdistin täyttää RunningTotal sarakkeessa. Huomautus nextscript että ilmoitin kursori vaihtoehto FAST_FORWARD., Tämä tehdään, jotta parantaa suorituskykyä kohdistin koska Microsoftin mukaan theFAST_FORWARD argumentti ”Määrittää FORWARD_ONLY, READ_ONLY-kohdistimen kanssa performanceoptimizations käytössä”. Toisin sanoen, Me ohjeistamme SQL Server useei lue vain kursori, joka voi vain siirtyä eteenpäin ja vierittää ensimmäisestä viimeiseen riviin.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

seuraava kuva on kuvakaappaus osoittaa toteuttaminen kirjoitus edellä.Kuten näette, se kesti kolme minuuttia ja viisi sekuntia päivittää 500000 rowsof meidän testi taulukko.,

Esimerkki Perus-While-Silmukka läpi Taulukon Rivit SQL Server

Nyt aion kirjoittaa edellinen kirjoitus käytön välttäminen kursori. Sinun willnotice, että se sisältää While-silmukka, joka on lähes identtinen thecursor script. Tämä on, kuten aiemmin sanoin, koska silloinkin, kun työskentelet cursoreiden kanssa, sinun on käytettävä iteratiivista ohjausrakennetta.

seuraava kuva on kuvakaappaus toteuttaminen kirjoitus edellä. Se tookless aika ajaa while silmukka kuin kursori.,

Toisen SQL Server-Kohdistin-Esimerkki

otetaan esimerkiksi kohdistin tipStandardize SQL Server data-tekstin haku ja korvaa-toiminto. Sana neuvo, jotta voit ajaa tämän koodin, sinun pitäisi seurata ohjeita kärki createthe testi ympäristössä.

Ja tässä on kohdistin koodi:

Jos me leikellä tätä koodia, voimme nähdä, että siellä on yksi osoitin, joka menee kehittämällä taulukko tuotteita, jotka olen kopioinut alla.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products 

SQL Server-Kohdistin Esimerkiksi Muuntaa While-Silmukka

jotta voit korvata tämän kohdistin VAIKKA SILMUKKA, meidän täytyy luoda temporarytable toteuttaa tally taulukko. Kaikille teille, jotka ette tiedä, mikä tallytable on, voimme määritellä sen taulukoksi, joka sisältää pari sarakkeita koostuen avaimesta ja sen arvosta. Tässä nimenomaisessa tapauksessa käytämme peräkkäistä kokonaislukua alkaen 1, joten voimme käyttää sitä iteraattorina. Tämä avain liitetään tuotepöydästä.,

aluksi, koska Tuotteet taulukossa on ProductID avain määritelty identityyou voi olla houkuttelevaa ohittaa tämän vaiheen, mutta sinun täytyy harkita, että todellinen casea rivi voisi olla poistettu, sen vuoksi et voi käyttää identitycolumn kuin iteraattorin. Lisäksi rivi voidaan poistaa, kun olemme käynnissä ourcode ja se voi johtaa suoritusvirheitä. Tämän välttämiseksi aiomme lisätä aTRY-CATCHblock. Käsittelen asiaa tarkemmin.

ennen WHILE Loopin aloittamista on asetettava sen alku-ja pysäytyskunto., Tähän asiaan lisäsin kaksi uutta kokonaislukumuuttujaa nimeltä @Iterator ja @MaxIterator.@MaxIterator-muuttujaa käytetään pitämään kohteiden lukumäärä #TallyTabletable ja asetamme sen arvon vain kerran ennen silmukan aloittamista. Myös @Iteraattori variableis alustetaan 1, koska määrittelimme sen kuin alkaa numero järjestyksessä, ja aiomme kasvattaa sen arvoa jokaisen iteraation.

Seuraavaksi

• oletko uusi osoittimet ja tarvitset jonkin käytännössä? Nexttipistä löydät selityksen, helposti ymmärrettävän kursorin esimerkin ja morerecommended readings:SQL Server Cursor Example.,
• Jos haluat muuntaa olemassa olevat osoittimet koodissa asettaa perustuu kyselyihin,katso tämä chapterSQL Palvelin Muuntaa Cursorto Asettaa Perustuu theSQL Server Tietokannan Suunnittelu Parhaita Käytäntöjä Opetusohjelma.
• tarvitsetteko toisen esimerkin While Loopin käytöstä? Katso tätä tipthat näytän sinulle, miten jakaa DML-lausekkeita erissä:Optimoida Suuri SQL ServerInsert, Päivittää ja Poistaa Prosesseja Käyttämällä Erissä.
• Jos et osaa käyttää TRY-valmistetta…CATCH exceptionhandling, vilkaise tätä vinkkiä: SQL Server yritä saalis poikkeus käsittely.,
• pysy kuulolla theSQL Server T-SQL Tipscategoryyn saadaksesi lisää koodausideoita.

Viimeksi Päivitetty: 2019-09-12

author

Daniel Farina oli syntynyt Buenos Aires, Argentiina. Itseoppinut, lapsesta asti hän osoitti intohimoa oppimiseen.
Katso kaikki vihjeeni

Related Resources

• lisää Tietokantakehittäjän vinkkejä…