Johdanto
Relaatiotietokannoissa operaatiot tehdään joukolla rivejä. Esimerkiksi SELECT-käsky palauttaa joukon rivejä, joita kutsutaan tulosjoukoiksi. Joskus sovelluslogiikan on toimittava rivin kerrallaan eikä koko asetetun tuloksen kanssa kerralla. Yksi tapa tehdä tämä T-SQL: ssä on KURSORIN käyttäminen.
Jos sinulla on ohjelmointitaitoja, käyttäisit todennäköisesti FOR- tai WHILE-silmukkaa iteroidaksesi yhden kohteen kerrallaan, tee jotain tiedot ja työ on tehty. T-SQL: ssä CURSOR on samanlainen lähestymistapa, ja se voidaan suositella, koska se noudattaa samaa logiikkaa. Mutta ole hyvä ja noudata tätä polkua, ja ongelmia voi seurata.
Joissakin tapauksissa CURSORin käyttö ei aiheuta niin suurta sotkua, mutta yleensä niitä tulisi välttää. Alla näytetään esimerkkejä siitä, missä kohdistimen käyttö aiheuttaa suorituskykyongelmia, ja näemme, että sama työ voidaan tehdä monilla muilla tavoilla.
Tässä esittelyssä käytämme AdventureWorks2012-tietokantaa ja Oletetaan, että haluamme saada tietoja. taulukko jokaiselle tuotteelle, jonka valmistus vaatii alle päivän, eli taulukosta ..
Kohdistinesimerkki
Aloitetaan käyttämällä KURSORIA ja kirjoitetaan seuraava syntaksin:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
KÄYTÄ AdventureWorks2012
GO
ILMOITA @id int
ILMOITA kohdistin >
– PAIKALLINEN NOPEA_FORWARD
– PAIKALLINEN UUDELLEEN VAIN FORWARD_ONLY
FOR
SELECT ProductId
FROM AdventureWorks2012.Production.Product
WHERE DaysToManufacture < = 1
AVAA kohdistinT
TULEE SEURAAVA kohdistimesta kohtaan @id
KUN NÄIN @@ FETCH_STATUS = 0
BEGIN
SELECT * FROM Production.ProductInventory
WHERE ProductID = @ id
JATKA SEURAAVA kohdistimesta @idiin
END
SULJE kohdistinT
POISTA kohdistinT
|
Lyhyen kahvitauon jälkeen , kysely suoritettiin loppuun, palauttaen 833 riviä alla esitetyssä ajassa. On tärkeää mainita edellä valitut syntaksit vain demotarkoituksiin, enkä tehnyt hakemiston viritystä nopeuttamaan asioita.
Kohdistimen suorituksessa meillä on kaksi vaihetta. Vaihe yksi, paikannus, kun kohdistin asettaa sijaintinsa riville tulosjoukosta. Toinen vaihe, haku, kun se saa tiedot kyseiseltä riviltä FETCH-nimisessä toiminnossa.
Esimerkissämme kohdistin asettaa sijaintinsa ensimmäisen SELECTin palauttamalle ensimmäiselle riville ja hakee ProductID-arvon, joka vastaa WHERE-ehtoa @id-muuttujassa. Sitten toinen SELECT käyttää muuttujan arvoa saadakseen tietoja. ja seuraava rivi haetaan. Nämä toiminnot toistetaan, kunnes ei ole enää rivejä, joiden kanssa työskennellä.
ja 500 riviä. Jos joudumme selaamaan miljoonia rivejä sisältävien taulukoiden läpi, se vie huomattavan paljon aikaa ja tulokset eivät miellytä meitä.
Annetaan kohdistimellemme uusi mahdollisuus ja poistetaan komento rivistä PAIKALLINEN STAATTINEN. On monia argumentteja, joita voimme käyttää kohdistimen määrittelyssä, enemmän tästä linkistä KURSORIN argumentit, mutta nyt keskitytään siihen, mitä nämä kaksi sanaa tarkoittavat.
Kun määritämme LOCAL-avainsanan, kohdistimen laajuus on paikallinen erälle, jossa se luotiin, ja se on voimassa vain tässä laajuudessa. Kun erä on suoritettu loppuun, kohdistin jaetaan automaattisesti. Kohdistimeen voidaan viitata myös tallennetulla menettelyllä, liipaisimella tai erän paikallisen kohdistimen muuttujalla.
STATIC-avainsana tekee väliaikaisen kopion kursorin tempdb: ssä käyttämästä datasta väliaikaisessa taulukossa . Täällä meillä on joitain gotchoja. STATIC-kohdistin on vain luku -tilassa, ja sitä kutsutaan myös tilannekuvan osoittimeksi, koska se toimii vain tietojen kanssa siitä hetkestä lähtien, kun se avattiin, mikä tarkoittaa, että se ei näytä mitään muutoksia, jotka on tehty tietokantaan käyttäjän käyttämässä tietojoukossa. kohdistin.Pohjimmiltaan mitään kohdistimen avaamisen jälkeen tehtyjä päivityksiä, poistoja tai lisäyksiä ei näy kohdistinten tulosjoukossa, ellet sulje ja avaa kohdistinta uudelleen.
Ole tietoinen tästä ennen näiden argumenttien käyttöä ja tarkista, vastaako se tarpeitasi. Katsotaanpa, onko kohdistimesi nopeampi. Alla on tulokset:
12 sekuntia on paljon parempi kuin 4 minuuttia ja 47 sekuntia, mutta pidä mielessä edellä selitetyt rajoitukset.
Jos suoritamme syntaksin käyttämällä tätä argumenttia LOCAL READ_ONLY FORWARD_ONLY, saamme samat tulokset. READ_ONLY FORWARD_ONLY -kohdistinta voi vierittää vain ensimmäiseltä riviltä viimeiseen. Jos STATIC-avainsana puuttuu, kohdistin on dynaaminen ja käyttää dynaamista suunnitelmaa, jos se on käytettävissä. Mutta on tapauksia, joissa dynaaminen suunnitelma on huonompi kuin staattinen.
Mitä tapahtuu, kun poistamme kommentin –LOCAL FAST_FORWARD?
FAST_FORWARD vastaa READ_ONLY- ja FORWARD_ONLY-kohdistimia, mutta sillä on kyky valita parempi suunnitelma joko staattisesta tai dynaamisesta.
LOCAL FAST_FORWARD näyttää olevan paras valinta joustavuutensa vuoksi Valitse staattinen tai dynaaminen suunnitelma, mutta FORWARD_ONLY tekee myös työn erittäin hyvin. Vaikka saimme tuloksen keskimäärin 12 sekunnissa molempia menetelmiä käyttäen, nämä argumentit tulisi testata oikein ennen kuin valitset yhden niistä.
Saman tuloksen voi saada monella tapaa, paljon nopeammin ja vähemmän vaikutusta suorituskykyyn.
Kohdistinvaihtoehto
Yksi menetelmä käyttää JOINia, ja kuten voimme nähdä seuraavaksi, tulokset ovat huomattavasti parempia.
Ensinnäkin, meidän on sallittava tilastoaika SQL Serverin suoritusajan mittaamiseksi ja tehtävä INNER JOIN kahden taulukon väliin,. ja. ProductID -sarakkeessa. Napsautettuamme EXECUTE-painikkeen saimme aikaan samat tulokset 330 ms: ssä kuin kursorimenetelmän aika 04:47, ja meillä on hymy kasvoillamme.
|
1
2
3
4
5
6
7
8
9
|
KÄYTÄ AdventureWorks2012
GO
ASETA TILASTOTIIKKO PÄÄLLÄ
SELECT * FROM Production.ProductInventory pinv
INNER JOIN Production.Product nimellä pp
ON pinv.ProductID = pp.ProductID
WHERE pp.DaysToManufacture < = 1
|
Meidän ei tarvitse toistaa jokaisen rivin läpi saadaksemme tarvitsemamme. Meillä ei ole enää silmukoita, no while -lauseke, no iter Työskentelemme sen sijaan tietojoukkojen kanssa, saamme haluamasi nopeammin ja kirjoitamme vähemmän koodia.
Oikea kohdistimen käyttö
Nyt kun olemme nähneet kuinka paljon vahinkoa kohdistin voi tehdä, katsotaanpa esimerkki, jossa voimme käyttää sitä.
Oletetaan, että haluamme valita rivien koon ja määrän vain tietyille taulukoille tietokannasta. Tämän saavuttamiseksi saamme kaikki taulukoiden nimet kriteerien perusteella from information_schema.tables ja CURSORin avulla käymme läpi kunkin kyseisen taulukon nimen ja suoritamme tallennetun menettelyn sp_spaceused välittämällä yhden taulukon nimen kerrallaan saadaksemme tarvitsemamme tiedot .
Käytämme samaa AdventureWorks2012-tietokantaa ja haemme kaikki taulukot Myyntimallista, joka sisältää nimen Myynti. Jokaisesta palautetusta taulukon nimestä haluamme nähdä kaikki tiedot information_schema.tables-tiedostoista.
Alla on T-SQL-syntaksi ja saadut tulokset:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
KÄYTÄ AdventureWorks2012
GO
DECLARE @TableName VARCHAR (50) – taulukon nimi ”Sales” -mallista
DECLARE @Param VARCHAR (50) – parametri ”sp_spaceused” -proseduurille
DECLARE db_cursor CURSOR FOR
– valitse vain ”Myynti” tabl es
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME kuten ”% Sales%” ja TABLE_TYPE = ”BASE TABLE”
AVAA db_cursor
JATKA SEURAAVASTA db_cursorista @TableName-osioon @TableName
KUN NÄIN @ @ FETCH_STATUS = 0
BEGIN
–liitä kukin muuttujan taulukon nimi ja välitä se tallennettuun menettelyyn.
SET @ Param = ”Myynti.”+ @ TableName
– suorita tallennettu menettely jokaiselle taulukon nimelle kerrallaan
EXEC sp_spaceused @Param
FETCH SEURAAVA db_cursor INTO @TableName saa seuraavan taulukon nimen
END
SULJE db_cursor
POISTA db_cursor
|
Tämä on yksi menetelmä, jossa CURSOR on hyödyllinen iteroimalla joitain tietoja läpi rivi kerrallaan ja saa tarvittavan tuloksen. Tässä tapauksessa kohdistin saa työn valmiiksi vaikuttamatta suorituskykyyn ja sitä on helppo käyttää.
Johtopäätökset
Siinä se on. Esitimme joitain esimerkkejä hyvistä, pahoista ja rumaista kohdistimia käytettäessä. Useimmissa tapauksissa voimme käyttää JOINSia, vaikka SELKEÄ, SSIS paketteja tai muita vaihtoehtoisia menetelmiä saadaksesi saman tuloksen nopeammin, vaikuttamatta vähemmän suorituskykyyn ja jopa kirjoittamalla vähemmän syntaksirivejä.
Kun käsitellään OLTP-ympäristöä ja käsiteltäviä suuria tietojoukkoja, sanaa ”KURSORI” ei pidä puhua.
SQL: ssä on hyvä ajatella ajatella tehdä toimintoja tietojoukoilla eikä ajatella ohjelmallisesti, käyttäen iteraatioita tai silmukoita, koska tällaista lähestymistapaa ei suositella eikä tarkoitettu tähän käyttöön. Yritetään käyttää ohjelmointikielien FOR tai FOREACH kaltaisia silmukoita ja liittää tämä logiikka SQL-toimintoihin, on este oikean ratkaisun löytämiselle tarpeisiimme. Meidän on ajatteltava sarjapohjaisissa toiminnoissa eikä yhdellä rivillä kerrallaan saadaksesi tarvitsemamme tiedot.
Kohdistimia voitaisiin käyttää joissakin sovelluksissa sarjoitettuihin operaatioihin, kuten yllä olevassa esimerkissä on esitetty, mutta yleensä niiden tulisi olla välttää, koska ne vaikuttavat negatiivisesti suorituskykyyn, varsinkin kun käytetään suuria tietojoukkoja.
- Kirjoittaja
- Uusimmat viestit
Minulla on kandidaatin tutkinto tietotekniikasta ja hänellä on myös taitoja .NET, C # ja Windows Server. Minulla on kokemusta suorituskyvyn virittämisestä ja seurannasta, T-SQL-kehityksestä, varmuuskopiointistrategiasta, hallinnosta ja tietokantojen konfiguroinnista.
Olen intohimoinen IT-asioista, videopeleistä, hyvistä elokuvista ja electro-house-musiikista. meluisa.
Näytä kaikki viestit, jotka on kirjoittanut Sergiu Onet
- SQL Server -kohdistimien käyttö – Edut ja haitat – 23. maaliskuuta 2016
