Čudnovati slučaj iteriranja kroz kursor

Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 09:15 · pre 214 meseci #1

Pozdrav svima. Evo jednog za istinske ljubitelje kurioziteta i specifičnih problema SQL Servera. Naime, imam kursor:

Code:

DECLARE myCursor CURSOR
    FOR
        SELECT customerId, readingId, price, categoryDiscount, readValue, flatDiscount, consumption
        FROM ViewBillingDataPreliminary
        ORDER BY customerId, readingId

i u nastavku vrlo elementarnu operaciju u while petlji. Kada izvršim proceduru, ona se vrti i vrti i vrti... i na kraju ništa ne napravi.

Međutim, ako poslije SELECT-a stavim TOP 10000000 (treba li uopšte reći, view iz kojeg selektujem ima znatno manje recorda), procedura se bez problema izvrši za cca 3 sekunde.

Ima li iko ideju šta i zašto se dešava?

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 10:58 · pre 214 meseci #2

pa nema smisla uopste. u masi slucajeva (u koje verovatno spada i tvoj, ne znam kakav ti je view) TOP filter se primenjuje kao poslednja operacija u execution plan-u (kad je recordset vec formiran) tako da je sa i bez TOP-a svejedno. Pitanje, dal se kurzor uopste vrti ili je jednostavno zakucao pri prvom elementu (stavi neki print u petlju i vidi dal vrti)? Ako je zakucao u startu onda je najverovatnije to neki locking problem.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 11:18 · pre 214 meseci #3

Probao... Stavio sam mu da isprinta do kojeg je reda došao. u messages do 167. reda ispiše ko munja i onda stane... kad ga nasilno zaustavim, ispiše još par redova (npr ako ga zaustavim poslije 10 sekundi, ispiše još do 177.). Probao sam umjesto iz view-a puniti kursor iz tabele, i onda je sve OK. Vjerovatno je nešto do ovog view-a, dosta je složen, sastoji se od 9 tabela. Hvala ti puno na hintu

Ne znaš možda šta bih prvo trebao da pogledam da vidim šta ne valja sa view-om?

edit: mada mi još uvijek nije jasno kako mu onaj TOP pomogne

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 11:32 · pre 214 meseci #4

Pa okaci view ako ti nije problem, a mozes i telo kurzora. Generalno view sam po sebi nema nikakvog uticaja na query execution plan, view se tretira jednostavno kao "inline" select u FROM delu a sam query optimizer posle skrati sta moze posmatrajuci ceo select koji koristi view. Dinamicki kurzori su uopste prilicno nesrecno resenje jer generalno sprecavaju query engine da optimalno granulira lockove a i bespotrebno produzava drzanje lockova. Da li ti menjas nesto u iteracijama nad tabelama koje se koriste u view-u (update/insert?)?

Moj prvi predlog bi ti npr bio da kurzor (ako vec ne mozes drugacije, a pretpostavljam sa bar 80% sigurnosti da mozes da uradis sta hoces i bez kurzora) prebacis iz dinamickog u staticki forward-only. Onda ce query biti izvrsen iz cuga i iteracija ce se obaviti nad kopijom podataka i smanjices pritisak na lockove nad tabelama. Ja sam i dalje prilicno ubedjen da tvoj problem lezi u lockovima.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 12:17 · pre 214 meseci #5

E, došlo je do novog razvoja situacije... Kada iz upita kojim punim kursor uklonim jedan od kriterija za sortiranje, radi ko zmaj:

Code:

    DECLARE myCursor CURSOR
    FOR
        SELECT customerId, readingId, price, categoryDiscount, readValue, flatDiscount, consumption
        FROM dbo.ViewBillingDataPreliminary
        ORDER BY /*customerId,*/ readingId

Inače View:

Code:
SELECT     dbo.Reading.readingId, dbo.Reading.readValue, dbo.Reading.readingDate, dbo.Reading.readingForMonth, dbo.Reading.flow,
dbo.Meter.meterId, dbo.Contract.numberOfMembers, dbo.Customer.customerId, dbo.Customer.name, dbo.Customer.surname,
dbo.Customer.balance, dbo.Services.serviceId, dbo.Services.serviceName, dbo.SevicesPricelist.priceUnit, dbo.SevicesPricelist.price,
dbo.CustomerType.customerTypeId, dbo.CustomerType.type, dbo.CustomerCategory.customerCategoryId, dbo.CustomerCategory.Category,
dbo.CustomerCategory.categoryDiscount, dbo.CustomerCategory.flatDiscount, dbo.Reading.consumption, dbo.Reading.cost
FROM         dbo.Reading INNER JOIN
                      dbo.Meter ON dbo.Reading.meterId = dbo.Meter.meterId INNER JOIN
                      dbo.Contract_Meter ON dbo.Meter.meterId = dbo.Contract_Meter.meterId INNER JOIN
                      dbo.Contract INNER JOIN
                      dbo.Customer ON dbo.Contract.customerId = dbo.Customer.customerId ON dbo.Contract_Meter.contractId =
dbo.Contract.contractId INNER JOIN
                      dbo.Services ON dbo.Meter.serviceId = dbo.Services.serviceId INNER JOIN
                      dbo.SevicesPricelist ON dbo.Services.serviceId = dbo.SevicesPricelist.serviceId INNER JOIN
                      dbo.CustomerType ON dbo.Customer.customerTypeId = dbo.CustomerType.customerTypeId INNER JOIN
                      dbo.CustomerCategory ON dbo.Contract.customerCategoryId = dbo.CustomerCategory.customerCategoryId

tijelo kursora radi update tabele, ali isto se ponaša i kada ne radi ništa, odnosno kad samo iterira... znači ovo je ostalo od njega:

Code:
SET @cursorRowCount = 1

    DECLARE myCursor CURSOR
    FOR
        SELECT customerId, readingId, price, categoryDiscount, readValue, flatDiscount, consumption
        FROM dbo.ViewBillingDataPreliminary
        ORDER BY customerId, readingId
    OPEN myCursor

    FETCH NEXT FROM myCursor INTO @customerId, @readingId, @price, @categoryDiscount, @readValue, @flatDiscount, @consumption

    WHILE @@FETCH_STATUS = 0
        BEGIN
          PRINT @cursorRowCount
          SELECT @cursorRowCount = @cursorRowCount+1

          FETCH NEXT FROM myCursor INTO @customerId, @readingId, @price, @categoryDiscount, @readValue, @flatDiscount,
@consumption
        END

    CLOSE myCursor
    DEALLOCATE myCursor;

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 15:03 · pre 214 meseci #6

Citat:

Probao... Stavio sam mu da isprinta do kojeg je reda došao. u messages do 167. reda ispiše ko munja i onda stane... kad ga nasilno zaustavim, ispiše još par redova (npr ako ga zaustavim poslije 10 sekundi, ispiše još do 177.). Probao sam umjesto iz view-a puniti kursor iz tabele, i onda je sve OK. Vjerovatno je nešto do ovog view-a, dosta je složen, sastoji se od 9 tabela

To sto stane na nekom redu, moze biti do viewa. Mi smo imali slucaj kad je u viewu vrsena implicitna konverzija varchar(1) u int i od 150,00 redova on stane na s300, zato sto je jedan varcha(1) umesto cifre sadrzao 'X'. ne cicni mi se da tvoj view radi nesto slicno, ali je moguce da ti se ne slazu tipovi podataka za JOIN i slicno. Kad sam malo formatirao SELECT za view, vidim ovo:

Code:
SELECT     dbo.Reading.readingId, dbo.Reading.readValue, dbo.Reading.readingDate, dbo.Reading.readingForMonth,
dbo.Reading.flow, dbo.Meter.meterId,dbo.Contract.numberOfMembers, dbo.Customer.customerId, dbo.Customer.name,
dbo.Customer.surname, dbo.Customer.balance, dbo.Services.serviceId, dbo.Services.serviceName, dbo.SevicesPricelist.priceUnit,
dbo.SevicesPricelist.price, dbo.CustomerType.customerTypeId,  dbo.CustomerType.type, dbo.CustomerCategory.customerCategoryId,
dbo.CustomerCategory.Category, dbo.CustomerCategory.categoryDiscount,
dbo.CustomerCategory.flatDiscount, dbo.Reading.consumption, dbo.Reading.cost
FROM         dbo.Reading
INNER JOIN dbo.Meter ON dbo.Reading.meterId = dbo.Meter.meterId
INNER JOIN dbo.Contract_Meter ON dbo.Meter.meterId = dbo.Contract_Meter.meterId
INNER JOIN dbo.Contract
INNER JOIN dbo.Customer
ON dbo.Contract.customerId = dbo.Customer.customerId
ON dbo.Contract_Meter.contractId = dbo.Contract.contractId
INNER JOIN dbo.Services ON dbo.Meter.serviceId = dbo.Services.serviceId
INNER JOIN dbo.SevicesPricelist ON dbo.Services.serviceId = dbo.SevicesPricelist.serviceId
INNER JOIN dbo.CustomerType ON dbo.Customer.customerTypeId = dbo.CustomerType.customerTypeId
INNER JOIN dbo.CustomerCategory ON dbo.Contract.customerCategoryId = dbo.CustomerCategory.customerCategoryId

Ovaj deo
INNER JOIN dbo.Contract
INNER JOIN dbo.Customer
ON dbo.Contract.customerId = dbo.Customer.customerId
ON dbo.Contract_Meter.contractId = dbo.Contract.contractId

ne izgleda dobro. ON ide odmah iza JOIN. Na nesrecu, MS SQL se nebuni ako napise ovako kako je napisano, ali su rezultati nepredvidivi. Kod mene (SQL 2005) ne prijavljuje sintaksnu gresku, ali kasljuca na ovakvim slucajevima.

Nesto sa view nije u redu.

[mod: skratio linije u kodu zbog formatiranja]

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 15:28 · pre 214 meseci #7

Zdravo @Zidar, hvala na odgovoru.

Ne znam, to za view ne bi trebalo da pravi neke probleme. Pravio sam ga u dizajneru, i samo sam kopirao kod. U međuvremenu sam skonto da se ne zaustavi nego uspori na 167. redu... nakon određenog vremena ispiše ih još stotinjak, pa tako u naletima, uglavnom jako sporo. Mislim da uzrok ovome leži u sortiranju

Code:
   DECLARE myCursor CURSOR
    FOR
        SELECT customerId, readingId, price, categoryDiscount, readValue, flatDiscount, consumption
        FROM dbo.ViewBillingDataPreliminary
        ORDER BY customerId, readingId

jer kada zakomentarišem customerId, sve ide ok. Inače imam 167 recorda za jedan customerId, pa onda stotinjak za drugi itd... Vjerujem da je nešto do toga, ali ne poznajem tu problematiku dovoljno dobro da bih mogao da upirem prstom.

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 15:51 · pre 214 meseci #8

Implicitna konverzija sama po sebi ne moze biti uzrok blokiranja procesa. Moze i izbaci runtime gresku (Conversion failed) i pukne i mislim ali nisam siguran da to ni ne moze da se spreci (tj da ga nateras da ignorise tu gresku). Njemu je ako sam dobro razumeo problem sto se proces zaglupi i blokira bez ikakve greske. Isto i INNER JOIN i ON ne prave problem jer se prica svodi na navodjenje tabela u FROM i redjanje ON uslova u where, tako da poredak i nije nesto presudan kod inner-a (jedino sto je brate ruznjikavo i bode oci).

Elem ovako necemo mnogo postici, uradi sledece:

uradi samo select iz declare cursor (bez kurzora) i ukljuci "Include actual execution plan" i kad se zavrsi udji u taj tab i snimi ga na desno dugme kao .sqlplan fajl i okaci taj fajl. Ako te ne mrzi uradi i za TOP x i za bez order by customerid (isto za sve tri varijante vidi dal ce da ti blokira proces)

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 16:33 · pre 214 meseci #9

Citat:

U međuvremenu sam skonto da se ne zaustavi nego uspori na 167. redu... nakon određenog vremena ispiše ih još stotinjak, pa tako u naletima, uglavnom jako sporo.

To je vec nesto drugo i moze imati milion razloga. Kao sto rece mmix, zakaci "actual execution plan".

Hvala mmix na pojasnjenju. Mi smo u stvari radili matematiku sa ciframa koje su bile varchar(1) i INSERT INTO i sve je radilo lepo dok nije pogodio vrednost 'x' i nije pukao nego jednostavno stao. Nije pukao znaci nije javio nikakvu gresku, samo je stao sa INSERTom. Kad smo provalili sta je i ocistili podatke, proslo je bez promlema, konverzija i sve. Za redosled u FROM JOIN ON teorijski moze kako hoces ali sam upravo testirao i nije htelo kad nisu bili poredjani lepo, a proradilo je kad se poredjaju kako treba. Microsoftu nije verovati....

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 16:37 · pre 214 meseci #10

drug, snimio sam fajlove, al ostarih tražeći kako da ih atačiram... :-/

[mod: poslao sam ti pm, u principu prvo moras da okacis post pa onda imas dole dugme]

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 16:58 · pre 214 meseci #11

Citat:

Zidar: Mi smo u stvari radili matematiku sa ciframa koje su bile varchar(1) i INSERT INTO i sve je radilo lepo dok nije pogodio vrednost 'x' i nije pukao nego jednostavno stao. Nije pukao znaci nije javio nikakvu gresku, samo je stao sa INSERTom. Kad smo provalili sta je i ocistili podatke, proslo je bez promlema, konverzija i sve.

Ovo mi je isto cudno, skontao sam da ste varchar tretirali kao int, ali konverzija 'X' u int mora da pukne runtime greskom, pa kako god da okrenes. Potvrdicu 100% kad mi stigne SQL Internals, ali ono cime baratam sada je da SQL proces/task moze da se "zablokira" samo tako sto jedan od threadova koji ga opsluzuju ode u WAIT state, a moze da ode u WAIT state iz jednog od tri razloga:

1. Najcesci, ceka zavrsetak asinhrone IO operacije (npr da se ucita indeks u memoriju, etc, etc). Iako najcesci, obicno je i najkraci i ne zaglupljuje proces.
2. Cekanje na oslobadjanje table/page/row lock-a, koji je uglavnom uzrocnik ovakvih problema (neko negde drzi pesimisticki lock i ne dozvoljava ti da nastavis)
3. Cekanje na druge threadove da zavrse svoj posao ako je query paralelizovan a on sam je zavrsio svoj chunk pre drugih, sto takodje ne traje dugo.

Naravno, pod uslovom da nema bugova u query execution engine-u, ali bi mi bilo veoma cudno da postoji blocking bug u operaciji kao sto je implicitna konverzija. Ja iskreno mislim da ste i vi natrcali na neki locking problem pa ste tokom dijagnostike otkrili 'X', pa kad ste sledeci put pustili query locka vise nije bilo.

@JustInTIme, pitanje koje bi moglo da bude vazno. Izmeri koliko sekundi prodje od kad se zakoci dok ne nastavi.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 17:48 · pre 214 meseci #12

evo estimated execution planova za TOP i TOPless, da tako kažem

Ono što mi prvo pada u oči je Cost:75% za ovaj nested iteration u TOPlessu kojeg nema u TOP-u, ali moram priznati da ne znam kako ovo da interpretiram...

SelectNoTop.sqlplan64.25k SelectTop.sqlplan45.9k

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 18:23 · pre 214 meseci #13

rekoh actual, ne estimated

estimated je procena, procena ne ukljucuje blokade, zato nam treba actual plan jer je to ono sto je proslo kroz engine.

U svakom slucaju, procenti nisu toliko vazni ako su cene svih operacija priblizne, apsolutni iznosi su vazniji. i daj mi notop bez kurzora, samo select.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 12.03.2009. u 18:36 · pre 214 meseci #14

aha, nešto mi nije htjelo prikazat taj tab nakon izvršenja (iako je opcija bila uključena), pa sam mislio da će ovaj biti od pomoći :(

sad sam kući, pa atačiram sutra ujutro da pogledaš ako budeš imao vremena.

hvala puno i tebi i zidaru

Re: Čudnovati slučaj iteriranja kroz kursor 13.03.2009. u 08:56 · pre 214 meseci #15

Evo sva 4 slučaja, sa i bez topa i sa i bez sorta po customerId-u. Svi su actual

SelectNoTopActual.sqlplan45.83k SelectTopActual.sqlplan45.87k TopNoSortActual.sqlplan44.77k NoTopNoSortActual.sqlplan44.73k

Re: Čudnovati slučaj iteriranja kroz kursor 13.03.2009. u 10:20 · pre 214 meseci #16

OK, IO nije tvoj problem, ako pogledas sva cetiri plana su skoro identicna i verovatno ni na jednom od ovih querija nisi imao blokadu? A sve i da ti je spor IO, velicine tabela koje se ovde koriste su mizerno malo.

Ostaje samo dva moguca problem, locking i neispravan tempdb. Da bi eliminisali neduzne uradi sledece, kurzor deklarisi kao fast staticki (ostavi order) i vidi dal blokira kroz fetch petlju.

Code:
DECLARE myCursor CURSOR STATIC FAST_FORWARD
    FOR
        SELECT customerId, readingId, price, categoryDiscount, readValue, flatDiscount, consumption
        FROM dbo.ViewBillingDataPreliminary
        ORDER BY customerId, readingId

I druga stvar koju treba da probas je da ili prebacis bazu u single user mode (ako je to moguce, tj ako nije produkciona baza) ili ako ne moze onda da napravis backup i podignes kopiju baze u single user modu i pustis dinamicki kurzor nad njom. To bi nam eliminisalo locking.

U principu nevezano za ovaj problem, ako bas i moras da koristis kurzore, uvek gledaj da koristis staticke ako je ikako moguce. Staticki kurzor ce prvo da pripremi sve podatke u tempdb pre nego sto pusti petlju nad podacima i onda nit ti nekom smetas nit tebi neko smeta dok premotavas tu kopiju podataka. Sa keyset kurzorima je zeznuto jer SVAKI fetch izvrsava select top 1 where A>prethodnoA (gde je A neka matematika koju SQL iskoristi da odredi poredak redova u kurzoru, nevazno za ovu pricu) i svaki taj select vuce sa sobom sve posledice select komande, da bi ti na kraju imao preko 8500 select komandi kao deo kurzorske operacije. Ono na sta ja i dalje sumnjam je da ti imas jos procesa koji lose raspolazu lock resursima na customer tabeli (mozda uzimaju tab lock, ne znam) i svaki put kad ti treba novi customer red moras da cekas da se taj lock oslobodi i onda posle opet cekas sledeci i tako dalje, tako dalje. Ako je to problem onda ce oba gornja primera (static i single user) da prosljakaju. AKo je to situacija onda resenje za keyset kurzor lezi u pronalazenju ko gde i kako drzi lockove na customer tabeli.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: Čudnovati slučaj iteriranja kroz kursor 13.03.2009. u 15:59 · pre 214 meseci #17

mmix je ronaldo

izmajmuniso sam nešto sa dvije procedure tako da ne moram da sortiram po customerId-u, al ne dade mi vrag mira, i vratim sve kako je bilo. Deklarišem kursor kao STATIC FORWARD_ONLY i - gle čuda. radi ko zuca.

svaka čast majstore, i hvala puno!

edit: a osim što smo riješili problem, ponešto smo i naučili

Re: Čudnovati slučaj iteriranja kroz kursor 13.03.2009. u 17:10 · pre 214 meseci #18

Odlicno

, a sad lose vesti. To sto sad radi ko zuca znaci da imas ozbiljan locking problem

. Da sam na tvom mestu malo bih investirao vremena da otkrijem gde, ko i kako drzi lockove na customer tabeli jer ces pre ili kasnije opet da naidjes na ozbiljne performance probleme.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Forum kao aplikacija na telefonu

Srodne teme