Avatar billede maffigadaffi Novice
08. maj 2014 - 08:53 Der 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
   
    Constr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=G:\TEST\;" & _
                "Extended Properties=Text;"
   
        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
                       
            RS.Close
            Set RS = Nothing
           
    EndTime = Now()
    Debug.Print Format(EndTime - StartTime, "hh:mm:ss")
   
    Exit Sub
   
ErrHandler:

    MsgBox "Errorhandler tog over", vbCritical
    RS.Close
    Set RS = Nothing

End Sub
08. maj 2014 - 08:57 #1
Hvad med at læse csv filen ind i en database (Access f.eks.) og lade Excel trække derfra i stedet?
Avatar billede maffigadaffi Novice
08. maj 2014 - 09:03 #2
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.
Avatar billede supertekst Ekspert
08. maj 2014 - 10:34 #3
Du skal være velkommen til at sende din testfil - så kunne det være interessant at forsøge. @-adresse under min profil.
Avatar billede maffigadaffi Novice
08. maj 2014 - 10:48 #4
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).
Avatar billede supertekst Ekspert
08. maj 2014 - 10:57 #5
Ok -
Avatar billede supertekst Ekspert
20. maj 2014 - 11:59 #6
Har du fundet en løsning?
Avatar billede maffigadaffi Novice
20. maj 2014 - 12:35 #7
Ingen løsning endnu pga. prioritering af andre opgaver.
Men det ender nok med en databaseløsning alá Access eller lign.
Avatar billede supertekst Ekspert
20. maj 2014 - 13:48 #8
Tak for info - har fundet en CSV-fil på 264.000 rækker - som jeg er fristet til at eksperimentere med.
Avatar billede maffigadaffi Novice
21. maj 2014 - 13:46 #9
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...
Avatar billede maffigadaffi Novice
21. maj 2014 - 13:48 #10
Lukket.
Avatar billede supertekst Ekspert
21. maj 2014 - 13:53 #11
Nu får vi se - men under alle omstændigheder skal du få en melding.
Avatar billede maffigadaffi Novice
21. maj 2014 - 13:56 #12
Jeg takker som sædvanlig.
Avatar billede supertekst Ekspert
21. maj 2014 - 15:02 #13
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.
Avatar billede maffigadaffi Novice
23. maj 2014 - 12:25 #14
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).
Avatar billede supertekst Ekspert
23. maj 2014 - 14:05 #15
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.
Avatar billede Ny bruger Nybegynder

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.

Loading billede Opret Preview
Kategori
Vi har et stort udvalg af Excel kurser. Find lige det kursus der passer dig lige her.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester