---

Jeg skal lave en stored procedure, der (i skeletform) skal gøre følgende:

Hent et antal rækker fra table1
loop igennem resultatet
for hver række
hvis tabel1.felt1 = 1
hent rækker fra tabel2
updater rækker i tabel3 med fx tabel2.felt3


Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min stored
procedure skal bruge en fast forward-only cursor, men hvordan bruger jeg
den? Jeg har kigget i BOL, men jeg kan ikke rigtigt finde et eksempel, hvor
et eksempel er beskrevet.

Samtidig kan jeg se, at cursors er performance-hæmmende, hvilket reelt set
er rigtigt uheldigt i dette tilfælde, hvor performance skal være i top. Min
SP skal afvikles hvert minut og kunne håndtere 150.000 "rækker" i timen.

Hvordan løser jeg mit problem bedst?

--
Jesper Stocholm
http://stocholm.dk

---

Jesper Stocholm skrev:

> Hent et antal rækker fra table1
> loop igennem resultatet
> for hver række
> hvis tabel1.felt1 = 1
> hent rækker fra tabel2

Hvilken relation er der mellem tabel1 og tabel2? (skal du hente
samtlige rækker i tabel 2 eller er der mulighed for join?) Dækker
"tabel2" over flere tabeller?

> updater rækker i tabel3 med fx tabel2.felt3


Samme spørgsmål her - hvilken relation er der mellem tabel2 og
tabel3?


> Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min
> stored procedure skal bruge en fast forward-only cursor, men
> hvordan bruger jeg den?

Prøv at kigge på de templates der er tilgængelige i Query Analyzer
(fanebladet ved siden af "Objects"). Her kan man hurtigt få leveret
en skabelon til en cursor.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen <jens@gyros.invalid> wrote in
news:Xns94846EAB930ECjcdmfdk@gyrosmod.dtext.news.tele.dk:

> Jesper Stocholm skrev:
>
>> Hent et antal rækker fra table1
>> loop igennem resultatet
>> for hver række
>> hvis tabel1.felt1 = 1
>> hent rækker fra tabel2
>
> Hvilken relation er der mellem tabel1 og tabel2? (skal du hente
> samtlige rækker i tabel 2 eller er der mulighed for join?) Dækker
> "tabel2" over flere tabeller?
[snip]
> Samme spørgsmål her - hvilken relation er der mellem tabel2 og
> tabel3?


Table 1: Delivery
PK DeliveryId

Table 2: Tendance
PK TendanceId
FK DeliveryId

Table 3: TendanceLine
PK TendanceLineId
FK TendanceId
FK DeliveryId

>> Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min
>> stored procedure skal bruge en fast forward-only cursor, men
>> hvordan bruger jeg den?
>
> Prøv at kigge på de templates der er tilgængelige i Query Analyzer
> (fanebladet ved siden af "Objects"). Her kan man hurtigt få leveret
> en skabelon til en cursor.



Mange tak,

--
Jesper Stocholm
http://stocholm.dk

---

Jesper Stocholm wrote:
> Jens Gyldenkærne Clausen <jens@gyros.invalid> wrote in
> news:Xns94846EAB930ECjcdmfdk@gyrosmod.dtext.news.tele.dk:
>
>> Jesper Stocholm skrev:
>>
>>> Hent et antal rækker fra table1
>>> loop igennem resultatet
>>> for hver række
>>> hvis tabel1.felt1 = 1
>>> hent rækker fra tabel2
>>
>> Hvilken relation er der mellem tabel1 og tabel2? (skal du hente
>> samtlige rækker i tabel 2 eller er der mulighed for join?) Dækker
>> "tabel2" over flere tabeller?
> [snip]
>> Samme spørgsmål her - hvilken relation er der mellem tabel2 og
>> tabel3?
>
>
> Table 1: Delivery
> PK DeliveryId
>
> Table 2: Tendance
> PK TendanceId
> FK DeliveryId
>
> Table 3: TendanceLine
> PK TendanceLineId
> FK TendanceId
> FK DeliveryId
>
>>> Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min
>>> stored procedure skal bruge en fast forward-only cursor, men
>>> hvordan bruger jeg den?
>>
>> Prøv at kigge på de templates der er tilgængelige i Query Analyzer
>> (fanebladet ved siden af "Objects"). Her kan man hurtigt få leveret
>> en skabelon til en cursor.
>
>> o)
>
> Mange tak,

Hvorfor skal det laves med en cursor?

I forhold til hvad du har fortalt os indtil nu, er denne update tilstrækkelig.

update tabel3
set felt_47 = tabel2.felt2
from tabel1 t1, tabel2 t2
where t1.deliveryID = t2.deliveryId
and t1.deliveryID = t3.deliveryID
and t2.deliveryID = t3.deliveryID
-- redundant, men hvad aldrig hvad optimizeren kan finde på
and t1.felt1 = 1
and t2.tendanceID = t3.tendanceID

--
Kristian Damm Jensen
damm (at) ofir (dot) dk

---

"Kristian Damm Jensen" <REdammMOVE@ofir.dk> wrote in
news:bvnt8r$uu0b6$1@ID-146708.news.uni-berlin.de:

> Jesper Stocholm wrote:
>> Jens Gyldenkærne Clausen <jens@gyros.invalid> wrote in
>> news:Xns94846EAB930ECjcdmfdk@gyrosmod.dtext.news.tele.dk:
>>
>>> Jesper Stocholm skrev:
>>>
>>>> Hent et antal rækker fra table1
>>>> loop igennem resultatet
>>>> for hver række
>>>> hvis tabel1.felt1 = 1
>>>> hent rækker fra tabel2
>>>
>>> Hvilken relation er der mellem tabel1 og tabel2? (skal du hente
>>> samtlige rækker i tabel 2 eller er der mulighed for join?) Dækker
>>> "tabel2" over flere tabeller?
>> [snip]
>>> Samme spørgsmål her - hvilken relation er der mellem tabel2 og
>>> tabel3?
>>
>>
>> Table 1: Delivery
>> PK DeliveryId
>>
>> Table 2: Tendance
>> PK TendanceId
>> FK DeliveryId
>>
>> Table 3: TendanceLine
>> PK TendanceLineId
>> FK TendanceId
>> FK DeliveryId
>>
>>>> Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min
>>>> stored procedure skal bruge en fast forward-only cursor, men
>>>> hvordan bruger jeg den?
>>>
>>> Prøv at kigge på de templates der er tilgængelige i Query
>>> Analyzer
>>> (fanebladet ved siden af "Objects"). Her kan man hurtigt få leveret
>>> en skabelon til en cursor.

> Hvorfor skal det laves med en cursor?

Det er netop det, som jeg er usikker på.

> I forhold til hvad du har fortalt os indtil nu, er denne update
> tilstrækkelig.
>
> update tabel3
> set felt_47 = tabel2.felt2
> from tabel1 t1, tabel2 t2
> where t1.deliveryID = t2.deliveryId
> and t1.deliveryID = t3.deliveryID
> and t2.deliveryID = t3.deliveryID
> -- redundant, men hvad aldrig hvad optimizeren kan
> finde på
> and t1.felt1 = 1
> and t2.tendanceID = t3.tendanceID

Jeg kan lige give her hele pseudokoden:

Hent relevante rækker i Table_U
For hver række i udtræk fra Table_U
   Set @dId_ = Table_D.DId
   Hent rækker fra Table_E hvor Table_E.DId = @dId_
   For hver række i udtræk af Table_E
       SET @EId_ = Table_E.EId
       Hvis Table_E.Felt1 = z
           Opdater eller opret række i Table_C med 6-8 felter fra Table_E
       Udtræk rækker fra Table_EL Hvor Table_EL.EId = @EId_
       For hver række i udtræk af Table_EL
           Hvis Table_EL.Felt1 = 0 og Table_EL.Felt2 = "gebyr"
               Opdater Table_D -> sæt Table_D.Dato = <tidspunkt> WHERE Table_D.DebitId = <someId>

<someId> findes ikke i Table_EL, men i Table_E, så den hentes via
FK EId i Table_EL, der er primær nøgle i Table_E.

Table Table_E:
--------------
PK EId
FK <someId>

Table Table_EL:
---------------
PK ELId
FK EId

Table Table_D:
--------------
PK:<someId>

--
Jesper Stocholm
http://stocholm.dk

---

Kristian Damm Jensen wrote:

> update tabel3
> set felt_47 = tabel2.felt2
> from tabel1 t1, tabel2 t2
[snip]
> -- redundant, men hvad aldrig hvad optimizeren kan finde på

Netop, og derfor er det god skik at bruge INNER JOIN i stedet for implicitte
joins.


--
Med venlig hilsen
Stig Johansen

---

Kristian Damm Jensen skrev:

> I forhold til hvad du har fortalt os indtil nu, er denne
> update tilstrækkelig.

Det ville jeg også tro. Dog er der - så vidt jeg kan se - et par
fejl i updatesætningen.

> update tabel3
> set felt_47 = tabel2.felt2
> from tabel1 t1, tabel2 t2
> where t1.deliveryID = t2.deliveryId
> and t1.deliveryID = t3.deliveryID
> and t2.deliveryID = t3.deliveryID
> -- redundant, men hvad aldrig hvad
> optimizeren kan finde på
> and t1.felt1 = 1
> and t2.tendanceID = t3.tendanceID

Du bruger tabel2 i stedet for t2 i 2. linje.
Tabel3 er ikke nævnt i from - og aliaset t3 er slet ikke defineret.


Jeg ville skrive:

update t3
set t3.felt_47 = t2.felt2
from tabel1 t1
   INNER JOIN tabel2 t2
       ON t1.deliveryID = t2.deliveryId
   INNER JOIN tabel3 t3
       ON t1.deliveryID = t3.deliveryID
   AND t2.tendanceID = t3.tendanceID
    and t2.deliveryID = t3.deliveryID /* redundant */
WHERE t1.felt1 = 1


Der er under alle omstændigheder meget performance at spare ved at
bruge en enkelt update i stedet for en cursor.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jesper Stocholm skrev:

[snip, kæmpecitat - overvej at klippe lidt ;)]


> Hent relevante rækker i Table_U
> For hver række i udtræk fra Table_U
> Set @dId_ = Table_D.DId
> Hent rækker fra Table_E hvor Table_E.DId = @dId_


Ovenstående kan klares med et join mellem Table_U og Table_D på
DId.

> For hver række i udtræk af Table_E
> SET @EId_ = Table_E.EId
> Hvis Table_E.Felt1 = z
> Opdater eller opret række i Table_C
> med 6-8 felter fra Table_E


Så vidt jeg kan se skulle du stadig kunne klare det hele med en
enkelt UPDATE.

> Udtræk rækker fra Table_EL Hvor Table_EL.EId
> = @EId_ For hver række i udtræk af Table_EL
> Hvis Table_EL.Felt1 = 0 og
> Table_EL.Felt2 = "gebyr"
> Opdater Table_D -> sæt
> Table_D.Dato = <tidspunkt> WHERE
> Table_D.DebitId = <someId>

- evt. en ekstra UPDATE til linjerne herover.


> <someId> findes ikke i Table_EL, men i Table_E, så den hentes
> via FK EId i Table_EL, der er primær nøgle i Table_E.

Du kan sagtens lave UPDATE-kommandoer med flere tabeller som kilde
(bare der kun er én tabel som destination). Det er langt mere
effektivt en enhver cursor. Det samme gælder i øvrigt for andre
handlingsforespørgsler.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen <jens@gyros.invalid> wrote in
news:Xns9484814B2B001jcdmfdk@gyrosmod.dtext.news.tele.dk:

> Du kan sagtens lave UPDATE-kommandoer med flere tabeller som kilde
> (bare der kun er én tabel som destination). Det er langt mere
> effektivt en enhver cursor. Det samme gælder i øvrigt for andre
> handlingsforespørgsler.

Jeg er endt med følgende update:

UPDATE Customer
SET
   DefaultTendanceTypeId = t.OrderType,
   ReceiverType = t.ReceiverType,
   ReceiverId = t.ReceiverId,
   CustomerId = t.CustomerId,
   CustomerIdTypeId = 1, /* Fixed number */
   [Name] = t.[Name],
   CountyId = t.CountyId,
   MemberOfDK = t.MemberOfDK,
   DeliveryAddressId = t.DeliveryId,
   DefaultPackingUnitId = t.PackageId,
   DeptorId = t.DebtorId
FROM
   Tendance t, Customer c
   WHERE t.UpdateCustomer = 1 AND
   c.CustomerId = t.CustomerId AND
   t.DeliveryId IN (
         SELECT DeliveryId
         FROM Delivery
         WHERE   DeliveryStatusId = XX AND
            OwnerShip IS NULL AND
            TransferredToAxapta = 0 AND
            DeliveryTypeId IN (1,2,3,4)
         )

Det ser faktisk ud til at virke korrekt :)

Men ... Jeg har lidt problemer med at få den sidste betingelse med ind i
opdateringen.

Kravet er, at hvis Tendance.StatusId = 1, så skal endnu to felter i
Customer opdateres

Customer.IssuerId = Tendance.IssuerId
Customer.IssuerInitials = Tendance.IssuerInitials

Er det ikke korrekt opfattet, at jeg er nødt til at lave en seperat UPDATE
for dette?

Tak for hjælpen indtil nu :)

--
Jesper Stocholm
http://stocholm.dk

---

"Jesper Stocholm" wrote

> Men ... Jeg har lidt problemer med at få den sidste betingelse med ind i
> opdateringen.
>
> Kravet er, at hvis Tendance.StatusId = 1, så skal endnu to felter i
> Customer opdateres
>
> Customer.IssuerId = Tendance.IssuerId
> Customer.IssuerInitials = Tendance.IssuerInitials
>
> Er det ikke korrekt opfattet, at jeg er nødt til at lave en seperat UPDATE
> for dette?
>
Jow jeg ville lave en ekstra update
Vær opmærksom på at du snildt kan lave flere updates i det samme kald til
MSSQL
Evt med trans begin/commit omkring

- Peter

---

Jesper Stocholm wrote:
> Jens Gyldenkærne Clausen <jens@gyros.invalid> wrote in
> news:Xns9484814B2B001jcdmfdk@gyrosmod.dtext.news.tele.dk:
>
>> Du kan sagtens lave UPDATE-kommandoer med flere tabeller som kilde
>> (bare der kun er én tabel som destination). Det er langt mere
>> effektivt en enhver cursor. Det samme gælder i øvrigt for andre
>> handlingsforespørgsler.
>
> Jeg er endt med følgende update:
>
> UPDATE Customer
> SET
> DefaultTendanceTypeId = t.OrderType,
> ReceiverType = t.ReceiverType,
> ReceiverId = t.ReceiverId,
> CustomerId = t.CustomerId,
> CustomerIdTypeId = 1, /* Fixed number */
> [Name] = t.[Name],
> CountyId = t.CountyId,
> MemberOfDK = t.MemberOfDK,
> DeliveryAddressId = t.DeliveryId,
> DefaultPackingUnitId = t.PackageId,
> DeptorId = t.DebtorId
> FROM
> Tendance t, Customer c
> WHERE t.UpdateCustomer = 1 AND
> c.CustomerId = t.CustomerId AND
> t.DeliveryId IN (
> SELECT DeliveryId
> FROM Delivery
> WHERE DeliveryStatusId = XX AND
> OwnerShip IS NULL AND
> TransferredToAxapta = 0 AND
> DeliveryTypeId IN (1,2,3,4)
> )
>
> Det ser faktisk ud til at virke korrekt :)
>
> Men ... Jeg har lidt problemer med at få den sidste betingelse med
> ind i opdateringen.
>
> Kravet er, at hvis Tendance.StatusId = 1, så skal endnu to felter i
> Customer opdateres
>
> Customer.IssuerId = Tendance.IssuerId
> Customer.IssuerInitials = Tendance.IssuerInitials
>
> Er det ikke korrekt opfattet, at jeg er nødt til at lave en seperat
> UPDATE for dette?

En case kunne vel klare problemet. Enten opdaterer man feltet med sig selv, eller også med den nye værdi.

Men jeg ville nok også lave det med en seperat update. Den der er rigeligt kompleks i forvejen.


--
Kristian Damm Jensen
damm (at) ofir (dot) dk

---

Jesper Stocholm skrev:

> Jeg er endt med følgende update:

[snip]

> t.DeliveryId IN (
> SELECT DeliveryId


IN-underforespørgsler kan være tunge at arbejde med, og de kan -
ifølge min SQL-bibel - med fordel erstattes med EXISTS hvor det er
muligt. Ovenstående IN-klausul kan omskrives til:


EXISTS (SELECT 1 FROM Delivery
WHERE DeliveryStatusId = XX
    AND OwnerShip IS NULL
    AND TransferredToAxapta = 0
    AND DeliveryTypeId BETWEEN 1 AND 4 /* evt. <= 4 */
    AND DeliveryId = t.DeliveryId
)



Hvis jeg husker tidligere tråde korrekt, er det dog sådan at MSSQL
selv kan optimere en forespørgsel, så der i praksis ikke
nødvendigvis er forskel på de to. Men generelt er det meget godt at
kende forskellen. I min bog er det beskrevet således:

,----
| When you use the EXISTS keyword, SQL Server doesn't have to perform
| a full row by row join. Instead, it can look through the records
| until it finds the first match, and then stop right there. As soon
| as there is a single match, the value of EXISTS is TRUE, so there
| is no needto go further.
`----



> Kravet er, at hvis Tendance.StatusId = 1, så skal endnu to
> felter i Customer opdateres
>
> Customer.IssuerId = Tendance.IssuerId
> Customer.IssuerInitials = Tendance.IssuerInitials
>
> Er det ikke korrekt opfattet, at jeg er nødt til at lave en
> seperat UPDATE for dette?

Du kan muligvis godt lave det i samme forespørgsel - noget i
retning af:

[SET ....]
IssuerID = CASE t.StatusId
           WHEN 1 THEN t.IssuerID
           ELSE IssuerID /* evt. NULL */
           END

- men det er nok lettere at lave to forespørgsler.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jesper Stocholm <j@stocholm.invalid> wrote in
news:Xns948469A87B49stocholmdk@130.225.247.90:

> Jeg skal lave en stored procedure, der (i skeletform) skal gøre
> følgende:
>
> Hent et antal rækker fra table1
> loop igennem resultatet
> for hver række
> hvis tabel1.felt1 = 1
> hent rækker fra tabel2
> updater rækker i tabel3 med fx tabel2.felt3
>
>
> Hvordan gør jeg det? Jeg har snuset mig frem til, at jeg i min stored
> procedure skal bruge en fast forward-only cursor, men hvordan bruger
> jeg den? Jeg har kigget i BOL, men jeg kan ikke rigtigt finde et
> eksempel, hvor et eksempel er beskrevet.
>
> Samtidig kan jeg se, at cursors er performance-hæmmende, hvilket reelt
> set er rigtigt uheldigt i dette tilfælde, hvor performance skal være i
> top. Min SP skal afvikles hvert minut og kunne håndtere 150.000
> "rækker" i timen.
>
> Hvordan løser jeg mit problem bedst?

Ok - jeg fandt - via et tidligere indlæg fra Jens Gyldenkærne [1] - en
artikel i BOL via søgning på DECLARE CURSOR. Imens jeg kigger på den, så
kan jeg jo vende mit spørgsmål om til: Er der andet end sletning af
cursors, som jeg skal være opmærksom på, når jeg anvender dem? Den
cursor-type jeg skal bruge er udelukkende en fast forward-only cursor.

[1] <Xns92AB7ABA9D7FFjcdmfdk@gyrosmod.dtext.news.tele.dk>

--
Jesper Stocholm
http://stocholm.dk