08. maj 2014 - 08:53Der er
14 kommentarer og 1 løsning
Optimering af SQL træk i VBA
Jeg har en .csv-fil med +100.000 ordrelinjer.Filen kompilerer data og får altså flere og flere linjer. Vha. SQL importeres udvalgte data fra filen. Alt virker, men problemet er performance. Hvis .csv-filen indeholder ca. 20.000 linjer er svartiden ca. 2 sekunder. Hvis antallet af linjer er ca. 90.000 er svartiden ca. 8 sekunder of hvis antallet af linjer er 215.000 (ca. 12 mdrs data) er svartiden helt oppe på ca. 65 sekunder.
Filen er sorteret efter dato således at de yngste linjer ligger nederst.
Kan jeg på nogen måde begrænse svartiden ved kun at lade SQL'en kigge i dele af filen eks. fra en bestemt dato, eks. sidste måneds data eller lignende?
Kode:
Sub SQL_TEST()
Dim StartTime As Date, EndTime As Date Dim RS As New ADODB.Recordset Dim Constr As String Dim SQL As String
StartTime = Now()
On Error GoTo ErrHandler Worksheets("Test_SQL").Activate On Error GoTo 0
If Range("A2").CurrentRegion.Rows.Count > 1 Then ActiveSheet.Range("A2:FF" & Range("A2").CurrentRegion.Rows.Count).Clear End If
SQL = "SELECT * FROM TESTdata12mdr.csv WHERE Oprettet BETWEEN DateValue(Now) - 30 And DateValue(Now)+1 and Land = ""DK"" and LEN(PMgr) >3;" 'ca. 65 sek
Set RS = New ADODB.Recordset RS.Open SQL, Constr, adOpenForwardOnly, adLockReadOnly
If Not RS.EOF Then ActiveSheet.Range("A2").CopyFromRecordset RS Else MsgBox "Ingen data indenfor afgrænsningen", vbCritical End If
Det ligger i overvejelserne, men lidt langt nede af listen. (Vi har ikke den store tradition for at bruge Access). Min 1. prio er at bibeholde .csv formatet og "bare" optimere svartiden.
Tusind tak for tilbuddet, men der er tale om i høj grad fortrolige data, så jeg har ikke umiddelbart mulighed for at sende data ud. Jeg håbede at man på en eller anden (nem?) måde kunne begrænse dataindlæsning eks. noget med at sætte BOF til linje 150.000 eller noget i den retning? (Som du nok kan fornemme, er jeg ikke specielt SQL kyndig, så jeg skøjter på tynd is).
Håber du er villig til at dele hvis du falder over en genialitet der kan fremme performance. Jeg lukker foreløbigt spørgsmålet, da der ikke har været den store interesse omkring emnet...
Indlæse CSV-fil som teksfil med 1 linje ad gangen. 264.465 rækker / 13 kolonner Sorteret efter det felt, der testes på. 11.850 rækker udtrukket 14 sek.
Kan sandsynligvis forbedres ved at udføre en søgning til den første række med laveste værdi (sorterede) i kriteriet.
14 sekunder. Det var da en klar forbedring... Anyways: jeg har fået opsat en testdatabase med henblik på at afdække hvilke performanceforbedringer der kan forventes ved ikke at kigge ned i .csv-fil. Så må jeg se hvad det ender med (når jeg får tid til at lege med det).
En anden mulighed er at åbne filen manuelt Via makro sætter fitler - indlæse filterværdier i en Userform, hvori der kan vælges pr. relevant kolonne: eksakte værdier, fra og til samt > < o.s.v.
Synes godt om
Ny brugerNybegynder
Din løsning...
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.