Nogle C5-kørsler tager bare for lang tid. Generelt kan man selvfølgelig prøve at optimere koden. Justere eller skifte algoritme. Lave simplere gennemløb. Samle flere gennemløb til få v.hj.a. temporære tabeller – eller måske undlade at bruge temporære tabeller og i stedet benytte arrays.
Naturligvis kan det også betale sig at tænke i om andre eller nye indexer kan speede tingene op – eller om der kan laves “mellemregning” i nye felter i tabellerne.
På SQL har man dog flere “skud i bøssen” som der dog er rigtigt mange konsulenter og programmører, der helt undlader at benytte. Det er en skam. De er faktisk indrettet sådan at de kan anvendes uanset om man kører Native eller SQL – så vender man sig til at bruge dem hver gang (og det ER faktisk en vane-sag), så er det blot ligegyldigt på Native, men kan optimere tingene rigtigt meget på SQL.
Bemærk også at selvom temporære tabeller faktisk oprettes på SQL-serveren, så lagres data lokalt i en temporær native datafil (der kommer aldrig data i tabellerne på serveren). SQL-optimeringerne ignoreres derfor også på temporære tabeller.
SELECT … #USING(<index>) i stedet for SELECT … USING <index>
På en SQL-server angiver man typisk ikke hvilket index, der skal bruges i et gennemløb. Det er Optimizerens (del af SQL-serveren) problem (bemærk dog at man nogle gange kan få lov til at komme med index-forslag til optimizeren, men det kommer vi ikke ind på i dette indlæg). Det betyder også at den rækkefølge man får poster tilbage i fra SQL, ikke er garanteret og altså skal betragtes som tilfældig.
Det kræver en anden måde at tænke på end med Native databasen, hvor man altid angiver et index, både for at hjælpe databasen med at søge posterne hurtigere frem – men også for at fastlægge deres rækkefølge.
Bruger man alligevel SEARCH … USING <index> … varianten med C5 på SQL, så sker der faktisk det at C5 sender en SQL-forespørgsel uden index-hint til SQL-serveren. Denne returnerer så et tilfældigt ordnet resultat, som C5-kernen så sorterer jf. index’et inden applikationen ser dataene. Kernen gør det for at din kode stadig fungerer, men det er naturligvis frygteligt dyrt performancemæssigt og i rigtig mange tilfælde helt unødvendigt.
Benyttes i stedet SEARCH … #USING(<index>) … signalerer applikationen overfor kernen at det er OK at posterne returneres i vilkårlig rækkefølge – og dermed spares hele sorteringen i kernen, når koden afvikles på SQL. Afvikles der på Native, gør de to varianter det samme – dvs. hjælper Native databasen med at finde data hurtigt med det angivne index og returnerer posterne sorteret i indexrækkefølgen.
Bemærk at man både på SQL og Native naturligvis kan bede kerne sorterer i en helt ande rækkefølge v.hj.a. SEARCH ….. ORDER BY <feltnavne> …
Eksempel 1 – Gennemløb alle ordrelinjer for at sætte SupplyNow til Qty-Delivered (rækkefølge ligegyldigt)
SEARCH SalesLine #USING(NumTransLineIdx) WHERE Number = &Number SET SupplyNow = Qty-Delivered UPDATE SalesLine END
Eksempel 2 – Klassisk, uoptimeret gennemløb alle ordrelinjer for at finde det højeste linjenummer
SET &MaxLineNo = #MinReal SEARCH SalesLine USING NumTransLineIdx WHERE Number = &Number SET &MaxLineNo = #Max(&MaxLineNo,LineNumber) END
Eksempel 3 – Gennemløb alle ordrelinjer for at finde det højeste beløb (bemærk – der findes en endnu mere effektiv metode til netop dette eksempel på SQL – se #SQLMaxList senere)
SEARCH SalesLine #USING(NumTransLineIdx) ORDER BY Amount DESCEND WHERE Number = &Number SET &MaxAmount = Amount BREAK END
SQL-serveren finder og retunerer jo alle felter på posten til C5-kernen og dermed til applikationen. Er det ikke spild af tid hvis man kun skal bruge et enkelt felt?
Jo, det er det. med kommandoenden #SQLFieldList(<tabelnavn>,<feltnavn 1>,<feltnavn 2….) kan man fortælle kernen og SQL-serveren at man kun ønsker at få returneret udvalgte felter… På et langsomt netværk og en stor tabel, kan det spare en del tid og netværksbelastning, og dermed forbedre den generelle hastighed både for den aktuelle bruger og alle andre.
Kommandoen sættes lige inden en SEARCH eller INTRODUCE med direkte opslag og findes i Makrobiblioteket SQL (så det skal du lige huske at loade). Native-kernen ignorerer kommandoen fuldstændig – så du kan roligt benytte den på Native for at gøre din kode SQL-parat, men vær OBS på at du så naturigvis ikke får testet rigtigheden af din kode hvis du ikke tester på SQL.
MEN PAS PÅ: Hvis du i din kode kører INSERT/UPDATE af de fremfundne data og du har en C5 tabeltrigger, der skal bruge felter som du IKKE har med i din #SQLFieldList, så får du problemer. Det samme gælder naturligvis hvis du overfører hele posten til et andet element, der bruger felter du har fravalgt.
På nyere netværk er der typisk kapacitet nok, så pga. ovenstående fejlmuligheder er det typisk ikke denne optimering du skal bruge mest tid på. Sandsynligheden for at du glemmer et felt – eller senere ændrer koden så du skal bruge felter du ikke har med (og glemmer at rette kommandoen) er relativ høj og kan medføre irriterende fejlsøgning.
Eksempel – Gennemløb alle ordrelinjer for at finde det højeste beløb:
#SQLFieldList(SalesLine,Amount) SEARCH SalesLine #USING(NumTransLineIdx) ORDER BY Amount DESCEND WHERE Number = &Number SET &MaxAmount = Amount BREAK END
Hvad nu hvis jeg blot skal finde størte/mindste værdi eller sum af værdierne i en tabel?
Ja, disse simple ting er der naturligvis også løsninger på – løsninger der vel at mærke flytter al arbejdet til SQL-serveren. Og er det 1.000.000 poster der skal findes største-/mindste-værdi eller sum på, ja så sparer man altså en del flytning af data over netværket og ikke mindst arbejde på klienten for C5 kernen.
Kommandoerne fungerer stort set ligesom #SQLFieldList ovenfor – dog med et enkelt tvist omkring SQL-kommandoen GROUP BY – mere om det senere. Kommandoerne hedder #SQLMinList, #SQLMaxList og #SQLSumList – og de ignoreres ligesom #SQLFieldList helt på Native databasen.
Kunsten her er at lave en stump kode der virker uanset om den afvikles på Native (og altså arbejder på alle poster der skal håndteres), eller næsten får resultatet foræret af SQL-serveren.
Eksempel – Gennemløb alle ordrelinjer for at finde det højeste beløb:
#SQLMaxList(SalesLine,Amount) SEARCH SalesLine #USING(NumTransLineIdx) ORDER BY Amount DESCEND WHERE Number = &Number SET &MaxAmount = Amount BREAK END
Bemærk: På SQL retuneres kun en enkelt post, nemlig posten med summen direkte i Amount-feltet. Den post kan C5-kernen hurtig sorterer (ORDER BY) og det betyder heller ikke noget at SEARCH afbrydes med BREAK efter at den eneste post er læst.
På Native fremfindes alle poster og sorteres faldende (ORDER BY). Dermed er første post den højeste og SEARCH afbrydes med BREAK så snart den er læst.
Umiddelbart kan ovenstående virke som det samme, men det er vigtigt at forstå at på SQL ville SQL-serveren skulle finde ALLE poster frem og sende dem over netværket til C5, som så skulle sortere dem efter Amount – blot for at applikationen kunne bruge den højeste af værdierne. Dermed sparer SQL-serveren og kernen rigtigt meget arbejde på SQL såfremt koden udnytter #SQLMaxList.
PAS PÅ: Det er en rigtig dårlig ide at lave en insert eller update på det resultat du får retur. Det giver i hvert fald sjældent mening…
Jamen, jeg skal bruge mindsteværdi / størsteværdi / sum af beløb pr. leveringsdato (blot et eksempel):
Du kan naturligvis vælge blot at køre flere SEARCH-løkker som ovenfor for at få de forskellige tal. Men det er jo ikke ligefrem pænt. Du kunne også vælge at behandle samtlige poster og så indbygge logik i koden til at beregne separate værdier… Eller du kan bruge en teknik der på SQL-servere kaldes for GROUP BY…
Eksempel:
// Bemærk: ,-, er adskiller til GROUP BY-felter #SQLMaxList(SalesLine,Amount,-,DeliveryDate) SEARCH SalesLine #USING(NumTransLineIdx) ORDER BY DeliveryDate,Amount DESCEND WHERE Number = &Number IF &i==0 OR &DeliveryDate[&i] <> DeliveryDate THEN #Add(&i,1) SET &DeliveryDate[&i] = DeliveryDate SET &MaxAmount[&i] = Amount ENDIF END
Eksemplet opbygger to arrays med hhv. leveringsdatoer og det tilsvarende største beløb. På Native sker dette ved at løbe alle linjer igennem, sorteret efter leveringsdato og faldende beløb. Der gemmes så en ny post hver gang leveringsdato skifter.
På SQL benyttes GROUP BY teknologien og SQL-serveren overlader en max pr. DeliveryDate. Disse poster håndteres så af applikationskoden (dvs. i praksis ignoreres ingen poster – modsat Native afviklingen).
Hvad nu hvis jeg blot skal checke antallet af poster?
Så skal du bruge den sidste variant af disse kommandoer, nemlig #SQLCountList. #SQLCountList virker ligesom #SQLMinList, #SQLMaxList og #SQLSumList, dog med den undtagelse at resultaterne (antal poster) ikke returneres i de udpegede felter – men altid i RowNumber-feltet. #SQLCountList virker naturligvis også med GROUP BY varianten.
Eksempel:
// Bemærk: ,-, er adskiller til GROUP BY-felter #SQLCountList(SalesLine,Amount,-,DeliveryDate) SEARCH SalesLine #USING(NumTransLineIdx) ORDER BY DeliveryDate WHERE Number = &Number IF &i==0 OR &DeliveryDate[&i] <> DeliveryDate THEN #Add(&i,1) SET &DeliveryDate[&i] = DeliveryDate // Hvis vi kører SQL, så indeholder RowNumber antallet af poster. // Ellers skal hver enkelt post tælles som 1. SET &Count[&i] = (#DBDIsSql(SalesLine) ? RowNumber : 1) ELSE #Add(&Count[&i], 1) ENDIF END
Eksemplet opbygger to arrays med hhv. leveringsdato og det tilsvarende antal ordrelinjer.
På Native sker det ved at alle linjer fremfindes i leveringsdato orden. Når leveringsdatoen skifter, skiftes index på arrays og antal sættes til 1. Er leveringsdatoen uændret tælles antallet blot en op.
På SQL returneres kun et antal poster i RowNumber. Så der returneres kun en post pr. leveringsdato (ELSE-forgræningen bliver aldrig aktiv). Det returnerede antal gemmes direkte i Count-Arrayet.
Der findes enkelte andre optimeringsmuligheder på SQL end ovenstående, men disse er efter min mening de vigtigste. En komplet gennemgang finder du i Microsofts dokument – disse bliver gennemgået i dokumentet “Microsoft Dynamics C5 SQL Best Practices”, som du finder på PartnerSource.
PAS PÅ: Hvis du programmerer meget i C5, så bør du være opmærksom på et enkelt alvorligt punkt, hvor SQL opfører sig helt anderledes en Native. Læs mere om det i dette indlæg: SQL vs Native – med et tvist af en indbygget SQL-bug?