title image


Smiley Mal nachgemessen...
Unter A97 komm ich auf folgende Messwerte:



RunSQL: 2,503906 s

Execute: 1,339844 s

Execute (eigener Jet WS): 1,292969 s

Execute (ODBC WS): 92,17188 s

Execute (ADO): 1,851563 s



warum der ODBC-Workspace derart lahm ist, müsste ich nochmal untersuchen (obwohl es sich kaum lohnt, im Zweifelsfall nimmt man ja dann eher ADO...)



Testfunktion:



Public Function TestRunSQLExecute()

Dim I As Long, J As Long, Z As Single, DB As Object, WS As DAO.Workspace

Const SQL1 = "Create Table zzz (Test text(50), lTest Long)"

Const SQL2 = "INSERT INTO zzz (lTest) Values(999)"

Const SQL3 = "Insert into zzz (Test) Select Nachname from MeineTabelle"

Const SQL4 = "Drop Table zzz"

DoCmd.SetWarnings False

Z = Timer

For J = 1 To 10

DoCmd.RunSQL SQL1

For I = 1 To 10

DoCmd.RunSQL SQL2

Next I

DoCmd.RunSQL SQL3

DoCmd.RunSQL SQL4

Next J

Debug.Print "RunSQL: " & Timer - Z & " s"

DoCmd.SetWarnings True



Set DB = CurrentDb

Z = Timer

For J = 1 To 10

DB.Execute SQL1

For I = 1 To 10

DB.Execute SQL2

Next I

DB.Execute SQL3

DB.Execute SQL4

Next J

Debug.Print "Execute: " & Timer - Z & " s"



Set WS = DBEngine.CreateWorkspace("Ws", "Admin", "", dbUseJet)

Set DB = WS.OpenDatabase(CurrentDb.Name)

Z = Timer

For J = 1 To 10

DB.Execute SQL1

For I = 1 To 10

DB.Execute SQL2

Next I

DB.Execute SQL3

DB.Execute SQL4

Next J

Debug.Print "Execute (Jet WS): " & Timer - Z & " s"

Set DB = Nothing

Set WS = Nothing



Set WS = DBEngine.CreateWorkspace("Ws", "Admin", "", dbUseODBC)

Set DB = WS.OpenConnection("x", , , "ODBC;DSN=Sample")

Z = Timer

For J = 1 To 10

DB.Execute SQL1

For I = 1 To 10

DB.Execute SQL2

Next I

DB.Execute SQL3

DB.Execute SQL4

Next J

Debug.Print "Execute (ODBC WS): " & Timer - Z & " s"

Set DB = Nothing

Set WS = Nothing



Set DB = New ADODB.Connection

DB.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & CurrentDb.Name & ";"

Z = Timer

For J = 1 To 10

DB.Execute SQL1

For I = 1 To 10

DB.Execute SQL2

Next I

DB.Execute SQL3

DB.Execute SQL4

Next J

Debug.Print "Execute (ADO): " & Timer - Z & " s"

Set DB = Nothing

End Function











Gruß aus dem Norden
Reinhard


Bitte immer die Access-Version angeben!
DB-Wiki


Wie man Fragen richtig stellt

YaccessAccess-FAQUnd ansonsten: Wikipedia




geschrieben von

Login

E-Mail:
  

Passwort:
  

Beitrag anfügen

Symbol:
 
 
 
 
 
 
 
 
 
 
 
 
 

Überschrift: