19. oktober 2012 - 09:53Der er
9 kommentarer og 1 løsning
Hjælp til at gøre query lidt (meget) hurtigere...
Hej,
Jeg har en SQL database hvor jeg IKKE har mulighed for at påvirke hvordan data kommer ind, men jeg har en kolonne (Revenue) som desværre ligger som nvarchar og skal summeres. Eksempler på data der ligger i den kolonne er:
-2.451.251,98 85.004,52 520,41 -1,58
Alle ligger som NVARCHAR.
I dag summerer jeg kolonnen på følgende måde:
select SUM(case when charindex('-', Revenue) <> 0 then cast(replace(replace(replace(Revenue, '-', ''), '.', ''), ',','.') as decimal(25, 2)) * -1 when Revenue = '' then CAST('0' AS decimal(25,2)) when Revenue IS NULL then CAST('0' AS decimal(25,2)) when PATINDEX('%[^A-Z]%', Revenue) = 0 then CAST('0' AS decimal(25,2)) else cast(Replace(replace(Revenue, '.', ''), ',', '.') as decimal(25, 2)) end) AS Total FROM dbo.tblXSAP_Revenue WHERE ((Year = '" & RangeStartYear & "' AND Period IN (" & RangeStartMonths & ")) OR (Year = '" & RangeEndYear & "' AND Period IN (" & RangeEndMonths & "))) AND Right(BT,5) = '" & TeamCode & "'"
Det fungerer, men da tabellen består af +2.000.000 records tager det rigtig lang tid at lave denne summering (ca. 35 sekunder). Mine evner rækker ikke til det, men det bør være muligt at lave en mere "effektiv" query.
Serveren er SQL 2008 og jeg bruger Visual Basic 2010.
Har du mulighed for at ændre på databaseschemaet? I så fald kunne du måske oprette et par ekstra kolonner, hvor data havde det rigtige format og så tilknytte en trigger, der formaterede data og gemte dem i de nye kolonner. Denne løsnings anvendlighed afhænger dog af, hvor ofte tabellen opdateres kontra hvor ofte denne forespørgsel skal foretages (bedst hvis der opdateres "sjældent" og læses ofte).
Jeg prøver at arbejde med at lave om i schemaet. Jeg får dog nye data ind hver dag, men da jeg selv konvertere dem bør jeg kunne ændre på dem.
Har testet "cast(replace(replace(Revenue,'.',''),',','.') as real)", men den giver ikke synlig hastighedsforbedring. Men det ser nemmere ud så jeg har lavet den ændring :)
SUM på 2 mill rækker er langsom ... end of discussion.
om 35 sek er for langtsom ... pas.
Men string vs int ... er der vist ingen tvivl om at int vil gøre en forskel.
Få lavet det skama om nu ... jeg græder hver gang jeg ser folk gemme tal/datoer som strings.
Derudover kan indexes også gøre en forskel. Men hvis du reelt vil lave en sum på det ... så er du nok bedre tjent med at køre et job om natten ... og så have det i den.
Jeg har ændret lidt i formatet, så nu ser min queary således ud:
select Year, Period, SUM(case when charindex('-', Revenue) <> 0 then cast(replace(replace(replace(Revenue, '-', ''), '.', ''), ',','.') as decimal(25, 2)) * -1 when Revenue = '' then CAST('0' AS decimal(25,2)) when Revenue IS NULL then CAST('0' AS decimal(25,2)) when PATINDEX('%[^A-Z]%', Revenue) = 0 then CAST('0' AS decimal(25,2)) else cast(Replace(replace(Revenue, '.', ''), ',', '.') as decimal(25, 2)) end) AS Total FROM dbo.tblXSAP_Revenue WHERE Right(BT,5) IN (" & TeamCode & ") GROUP BY Year, Period ORDER BY Year, Period
Nu fylder jeg en række datatables med informationen istedet og laver så en query på dem bagefter. Det ser ud som om at det gør søgningen hurtigere.
Men jeg er stadig meget interesseret i nogle ideer.
@Buzzzz: Du har nok ret...Shit in shit out! Jeg er nok nød til at bide i det sure æble og få lavet selve konverteringen om og i den forbindelse måske indexere...
Hvis 2 mill er alle rows ... så er det rimelig naturligt den vil lave en table scan. I hvert fald ville det ikke være meget der kan optimeres med mindre du kan smide alle rows der bliver benyttet ind i et index og på den måde få et covering index.
Korrekt, alle databaser som er lavet med hovedet under armen yder ringe selvom man kaster det vildeste hardware gear efter den.
@buzzzz, Det har taget lidt tid, men din "løsning" var den jeg valgte at gå videre med, og har nu en fantastisk performance. Smider du lige et svar... :)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
