Bericht 1 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 28-03-2008 |
Aan | : | Hugo Kornelis | MsgID | : | 3801.1 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Hoi Hugo,
Dank je weer voor je vorige antwoord! Daar kom ik dit weekend nog op terug. Ondertussen heb ik nog iets anders... ;-)
Het gaat om deze query:
INSERT INTO statushistory
SELECT ID, orderID, status, 1+ID - (select MIN(ID) FROM statushistory_tmp A
where a.orderID = B.orderID
group by orderID)
FROM statushistory_tmp Bstatushistory_tmp ziet er zo uit:
ID orderID status
1 123456 2008-03-27 12:13 A1
2 123456 2008-03-27 15:42 A2
3 123456 2008-03-28 09:12 A34 987654 2008-03-27 15:53 A1
5 987654 2008-03-27 10:15 A2
Hierbij is
ID de sleutel (primary key), type bigint en is in de voorgaande stap al automatisch opgenummerd.
orderID en
status zijn beide nvarchar(255).
Het doel van deze query om volgnummers toe te kennen. Dus binnen een orderID een volgnummer. Dit heb ik nodig, omdat het soms voorkomt dat binnen 1 minuut 2 keer een statuswijziging plaatsvindt. De seconden toevoegen is helaas niet mogelijk, dus heb ik het opgelost met een volgnummer.
statushistory ziet er na het uitvoeren van de query dus zo uit:
ID orderID status volgnummer
1 123456 2008-03-27 12:13 A1 1
2 123456 2008-03-27 15:42 A2 2
3 123456 2008-03-28 09:12 A3 34 987654 2008-03-27 15:53 A1 1
5 987654 2008-03-27 10:15 A2 2
Ook in deze tabel is
ID de sleutel (primary key), type bigint en
orderID en
status zijn opnieuw nvarchar(255). De
volgnummer kolom is van type int.
En... het werkt! Maar de machine waarop dit draait heeft er toch héél wat meer moeite mee dan mijn eigen computer. Bij 10.000 rijen ben ik op mijn pc nog binnen 1 seconde klaar, maar op de server waar het op moet draaien duurt het dan al 11 seconden. Vandaag heb ik een test gedaan met 58.000 rijen. Dat duurde ruim 6 minuten! Nog even op mijn computer geprobeerd: 2 seconden.
Heb jij enig idee waarom die andere machine zo ongeloofelijk veel moeite heeft met deze query? En is er wellicht een manier om dit te versnellen (met een slimmere query?)
De machine waarop dit draait, is trouwens een Dual-Core AMD Opteron 2210 1.8 Ghz met 4 GB intern geheugen (draaiend op Windows Server 2003 R2 SP2). De /3GB switch is geactiveerd in boot.ini om SQL Server voldoende intern geheugen toe te kennen. Mijn eigen computer is een Intel Core2 6300 1.87Ghz met 2 GB intern geheugen (draaiend op Windows Vista SP1).
De versie van SQL Server 2005 is dezelfde (9.0.3054).
Groeten,
Ronald
Bericht 2 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 31-03-2008 |
Aan | : | Allen | MsgID | : | 3801.2 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Inmiddels kwam ik het volgende tegen:
FIX: A query performance issue occurs when you run a query against a column of the bigint data type in SQL Server 2005http://support.microsoft.com/kb/948248 Ik weet niet 100% zeker of dit van toepassing is, maar voor alle zekerheid heb ik het datatype veranderd in 'int'. Aan maximaal 2 miljard nummers heb ik per dag toch wel genoeg. <g>
Of het probleem hiermee is opgelost, weet ik later vandaag (als het aantal rijen weer groot is geworden).
Bericht 3 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 01-04-2008 |
Aan | : | Ronald Beuker | MsgID | : | 3801.3 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Helaas.... de query met op dit moment 74,000 rijen in de brontabel loopt bij mij nu in 3 seconden. Op dat andere systeem duurt het 55 minuten!! :-/
Bericht 4 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Michel Uphoff (Sysop) | Datum | : | 01-04-2008 |
Aan | : | Ronald Beuker | MsgID | : | 3801.4 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Hoi Ronald,
Ik neem aan dat je niet alleen maar wat tegen jezelf aan het neuzelen was, en dat je hier wat mee wilt.
>> Op dat andere systeem duurt het 55 minuten <<T.o.v. 3 seconden... dat is een bijkans onmogelijk verschil. Wat zijn de hoofdspecs van het snelle en van het trage systeem? OS verschil? Werkstations of servers?
Hoe staat het met evt. virusscanners? Ik weet dat er scanners zijn die met name queries piepend en knarsend tot stilstand kunnen brengen.
Doe even het volgende (naast die hoofdspec's) start op beide machines taakbeheer (Ctrl-Shift-Esc), tabblad processen, en kijk naar abnormale cpu load door processen, kijk ook naar (vrij) geheugen. Kijk ook naar de netwerkgrafiek; er wordt toch niets iets heel diks telkens over een touwtje gesleept?
Schijfruimte voldoende, defragmentatie misschien een echt probleem (kan vooral op database servers wel eens echt heel ernstig worden).
Michel Uphoff (NLcomputer)
Homepage
Bericht 5 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 02-04-2008 |
Aan | : | Ronald Beuker | MsgID | : | 3801.5 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Hoi Ronald,
Sorry voor het vertraagde antwoord - ik had het even druk met andere zaken.
Heb jij enig idee waarom die andere machine zo ongeloofelijk veel moeite heeft met deze query?In feite vraag je om voor elke rij in "StatusHistory_tmp" een subquery uit te voeren waarin de laagste ID van een overeenkomende OrderID in diezelfde "StatusHistory_tmp" wordt gezocht. Bij 100 rijen moet dus 100 keer in die 100 rijen gezocht worden, bij 1000 rijen 1000 keer in die 1000 rijen, enzovoort. De benodigde verwerkingstijd zal exponentieel stijgen met de hoeveelheid rijen.
Maar je eigenlijke vraag is vermoedelijk waarom er zo'n groot verschil tussen de twee machines is. Ik denk dat dit te maken heeft met een verschil in de aanwezige indexen. Ik denk dat je op je eigen computer een index hebt gedefinieerd op de OrderID kolom in StatusHistory_tmp, en dat een dergelijke index op de server ontbreekt. Door deze index kan SQL Server voor elke rij (via die index) direct de laagste ID waarde bij een orderID vinden, zodat de performance nu lineair stijgt met het aantal rijen in plaats van exponentieel.
Een andere mogelijke verklaring, als de indexen op beide machines wel gelijk zijn, kan de data distributie zijn. Met een index op alleen OrderID moet SQL Ser ver nog wel alle rijen van een order lezen om de laagste ID te zoeken. Als jouw machine 6000 orders met gemiddeld 10 rijen heeft, en de echte server 60 orders met gemiddeld 1000 rijen, dan moet de echte server veel meer werk doen.
En is er wellicht een manier om dit te versnellen (met een slimmere query?)Jazeker. Dan grijp ik opnieuw naar de nieuwe ROW_NUMBER() functie, dit keer met behalve een ORDER BY ook een PARTITION BY clause om de nummers per waarde van OrderID opnieuw te laten beginnen:
INSERT INTO statushistory -- Altijd een kolomlijst opgeven !!!!!
SELECT ID, orderID, status,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ID)
FROM statushistory_tmp;En aangezien dit er wel erg uitziet als het vervolg op een andere discussie is mijn volgende suggestie om de hele tijdelijke tabel er tussenuit te knippen en direct de nummering per order toe te kennen:
INSERT INTO statushistory(ID, OrderID, Status, Volgnummer)
SELECT ROW_NUMBER() OVER (ORDER BY WeetIkNietMeer),
OrderID, Status,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ID)
FROM WeetIkOokNietMeer;Als je de ID kolom alleen nodig had om het volgnummer te kunnen bepalen, dan kan je die uiteraard ook meteen laten vervallen.
Het voordeel van het overslaan van tussenstappen is dat er veel minder naar schijf geschreven en weer van schijf gelezen hoeft te worden. Bovendien geef je de query optimizer veel meer kans om "shortcuts" te vinden. Dit is vaak een van de moeilijkste dingen voor een "3GL" ontwikkelaar die overstapt naar SQL schrijven: leren om (in één enkele query) het resultaat te beschrijven in plaats van in een serie kleine stapjes een manier om tot het resultaat te komen.
Groetjes,
Hugo Kornelis, SQL Server MVP
--
Kijk ook eens op mijn blog:
http://sqlblog.com/blogs/hugo_kornelis
Bericht 6 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 02-04-2008 |
Aan | : | Michel Uphoff (Sysop) | MsgID | : | 3801.6 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Hoi Michel,
Ik babbel altijd zo gezellig tegen mezelf. <g> Doet me denken aan een sketch van Herman van Veen:
Komt een man bij de dokter. Man geeft aan niet zo lekker in z'n vel te zitten.
Dokter: 'Houdt u wel van u zelf?'
Man: 'Ja...
....maar het is niet wederzijds'Dit slaat niet op mij trouwens. <g> Maar terug naar deze server. Een virusscanner draait er niet op. Of dat (on)verstandig is, laat ik maar in het midden (niet mijn probleem, en sowieso lijkt het mij qua performance alleen maar gunstig <g>).
Ik ben nu de C-schijf aan het defragmenteren. Die is 12 GB groot, 4 GB vrij, 30% fragmentatie. De data staan hier trouwens
niet op.
De D-schijf (met de SQL Server databestanden) is 220 GB groot, met 210 GB vrij. Fragmentatie kan ik zien als de C-schijf zometeen klaar is met defragmenteren. ;-)
Netwerkverkeer is er nauwelijks. Deze server is zelfs buiten het domein gehouden.
Het performance probleem op de SQL Server heb ik kunnen oplossen (dat zal ik zo in een bericht aan Hugo uiteenzetten). Maar met die server ben ik nog niet klaar. Het ding moet razendsnel zijn, maar je zit regelmatig te wachten. CPU-gebruik is dan bijna niets, vrije geheugenruimte is er nog zát, maar toch gebeurt er regelmatig even niets. Dus 'iets' blokkeert die server, heb jij nog ideeën wat zoiets kan veroorzaken? Ik denk inmiddels aan iets met de hardware, of een iets teveel getweakte BIOS-setting. Morgen ga ik zeker in het BIOS kijken; misschien de 'zet alles terug naar standaard' optie maar gebruiken?
Groeten,
Ronald
Bericht 7 van 24NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 02-04-2008 |
Aan | : | Hugo Kornelis | MsgID | : | 3801.7 |
Onderwerp | : | Versnellen SQL query mogelijk? | Forum | : | ws-nlcomputer |
Hoi Hugo,
>>De benodigde verwerkingstijd zal exponentieel stijgen met de hoeveelheid rijen.<<
Ok! Nou ja, niet ok dus, maar zo'n gevoel had ik er zelf ook al bij (dat het exponentieel toeneemt).
>>Ik denk dat dit te maken heeft met een verschil in de aanwezige indexen. (...) Een andere mogelijke verklaring, als de indexen op beide machines wel gelijk zijn, kan de data distributie zijn.<<
De brondata waren exact dezelfde (op beide machines is de 1e stap van het DTS-package is het downloaden van hetzelfde XML-bestand). Qua indexen was er geen verschil. Sterker nog: die waren er niet eens. <g> De tabellen op mijn systeem had ik met rechtermuisknop --> Script table --> CREATE to omgezet in een query, en die op de andere machine uit laten voeren). Ik heb nog even bij de 'Indexes and keys' gekeken, maar daar stond op beide systemen alleen de primary key (op het 'ID' veld).
Door de subquery om te zetten naar een aantal tussenstappen, waardoor ik met een LEFT JOIN per orderID het 'startID' nummer erbij kon zetten, had ik gistermiddag zelf al een enorme snelheidsvergroting bereikt. Echter, jouw methode werkt nog véle male sneller! Die 50 minuten werden opeens... 1 seconde. Zelfs snéller op de server dan op mijn computer. <g>
Toen liep ik wel vast bij een andere query die een driedubbelgeneste SELECT CASE bevat. Drie draaide op mijn systeem in 3 seconden, en op de andere machine 13 minuten. Toen vond ik de Database Engine Tuning Advisor en die adviseerde om indexen te gaan gebruiken. En weg was het probleem!
Hieronder trouwens die prachtige query (althans, ik vond 'm prachtig toen ik eindelijk alle haakjes goed had staan <g>):
INSERT INTO STATUSHIST_TMP2
select ID, A.orderID AS orderID, volgnummer,
(SELECT CASE status
WHEN 'A1' THEN (SELECT CASE (SELECT COUNT(*) FROM STATUSHISTORY C WHERE C.orderID = A.orderID)
WHEN 0 THEN (SELECT CASE(SELECT MAX(volgnummer) FROM STATUSHIST_TMP1 D WHERE D.orderID = A.orderID)
WHEN 1 THEN LEFT(UpdTime,10) + 'T' + SUBSTRING(UpdTime,12,6) + '00'
ELSE '20' + RIGHT(orderDate,2) + '-' + SUBSTRING(orderDate,4,2) + '-' + LEFT(orderDate,2) + 'T00:00:00'
END)
END)
ELSE
LEFT(status,10) + 'T' + SUBSTRING(status,12,5) + ':00'
END) AS StatusDateTime,
(SELECT CASE status
WHEN 'A1' THEN 'A1'
ELSE
STUFF(status,1,17,'')
END) AS Status
FROM STATUSHIST_TMP1 A,XML B
WHERE A.orderID = B.orderID
ORDER BY IDIk heb al bedacht dat die ORDER BY eruit kan, en dat achter de INSERT INTO de kolomlijst moet komen. ;-)
Achtergrond van wat hier gebeurt: soms bevat de status-kolom (door een invoerfout) alleen de code 'A1', en géén datum/tijd ervoor. Als dat zo is, dan kijk ik of die order al in de totale historietabel voorkomt. Zo nee, dan kijk ik of ik precies 1 nieuwe regel informatie heb over die nieuwe order (in theorie zou het kunnen dat die order nieuw is binnengekomen, maar ook al een statuswijziging heeft gehad). Op basis daarvan bepaal ik welke kolom ik gebruik om e.e.a. recht te trekken.
Dit had ik nodig omdat anders de hele procedure staakte: als er geen geldige datum/tijd was in 'nvarchar-formaat', dan ging de conversie naar datetime-formaat fout.
Wat wel vreemd blijft, is dat ik op mijn systeem zónder deze indexen al een goede performance haal, terwijl het op die andere machine dan dramatisch traag is. Sowieso heb ik nog steeds het gevoel dat er 'iets' niet lekker op die server zit (zie mijn bericht aan Michel).
>>
INSERT INTO statushistory -- Altijd een kolomlijst opgeven !!!!! <<
Is dat vanwege performance redenen, of is er (ook) een andere reden? Ik dacht dat het voldoende was om ervoor te zorgen dat in de SELECT-list de correcte namen stonden (voor zover nodig gebruik makend van 'AS').
>>Dit is vaak een van de moeilijkste dingen voor een "3GL" ontwikkelaar die overstapt naar SQL schrijven: leren om (in één enkele query) het resultaat te beschrijven in plaats van in een serie kleine stapjes een manier om tot het resultaat te komen.<<Point taken. <g> Wat ik mij nog zat af te vragen: als ik nu ipv al die tussenstappen en tabellen alles eens in 1 lange transactie zet, gebruik makend van tijdelijke tabellen? Want tijdelijke tabellen worden toch alleen in het geheugen bewaard? (Met als gevolg: minder schijf I/O?) Het voordeel van die tussenstappen vind ik wel dat het veel makkelijker is om terug te herleiden wat er nu precies gebeurt. Maar het kan een kwestie zijn van anders gaan denken zijn. <g>
Groeten,
Ronald