Lots of small queries in a small database - performance problems

[tobyyR]

Hi everybody!

tl;dr-version: I'm trying to make lots of small queries to a small database, but the infinite loop takes 10+ seconds to execute every other time in the loop. Help, code below.


Long version: I need to make lots of small queries to a very small database (under 100 rows) and write values to cells. I have set up a local database and used the code below to test the idea. The code just runs the same query in an infinite loop, writes down the CategoryId and selects a new cell beneath the current cell. But I'm having performance issues already.

The weird thing is that the query runs VERY SLOW, it takes around 10 seconds for the myCmd.Execute -line to run, but only on every other time the line is run! Every other time the loop runs instantly eg. the first time in the loop takes 10s, second 0s, third 10s again, fourth 0s and so on. And the code runs always the same query!

Furthermore the query always returns 2 rows. If I change the SQL-query so that it returns only 1 row, the code executes instantly, even if it would query for different things. The code is blazing fast always, if the query would return only 1 row.

So the question is, is there something wrong with the code? Or am I using the Connection, Command and Recordset objects the wrong way? Because getting 2 rows from a local database shouldn't take 10+ seconds, especially when getting 1 row happens instantly.

I'm quite baffled, to be honest, why the code acts this way.

Thanks for your time in advance!

[VBA]Sub Macro1()

Dim myRecordset As Recordset
Dim myConn As Connection
Dim myCmd As Command

sqlCommand = "SELECT CategoryId, CategoryName FROM H_BudgetEntries WHERE CategoryId=1 Or CategoryId=2"

Set myConn = New Connection

With myConn

.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=TESTDB;Data Source=.;Trusted_connection=yes;"
.Open

End With

Set myCmd = New Command
myCmd.CommandText = sqlCommand

Set myCmd.ActiveConnection = myConn

Range("A5").Select

Do While True ' Infinite loop

Set myRecordset = myCmd.Execute ' This line of code takes almost 10 seconds to complete, but only on every other time it is run
Selection.Value = myRecordset.Fields(1)
Selection.Offset(1, 0).Select 'BREAKPOINT HERE

Loop


End Sub [/VBA]

[Kenneth Hobs]

Most likely, your problem is in Select. If you must use select, at least use my speedup routines or parts of it. The better method is to not use Select but my routines contains other settings that help with speed.

http://vbaexpress.com/kb/getarticle.php?kb_id=1035

Why execute more than once? With infinite loops, eventually something not nice will happen.
[vba]
Sub Macro1()
Dim myRecordset As Recordset
Dim myConn As Connection
Dim myCmd As Command
Dim r As Range

sqlCommand = "SELECT CategoryId, CategoryName FROM H_BudgetEntries WHERE CategoryId=1 Or CategoryId=2"

Set myConn = New Connection

With myConn
.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=TESTDB;Data Source=.;Trusted_connection=yes;"
.Open
End With

Set myCmd = New Command
myCmd.CommandText = sqlCommand
Set myCmd.ActiveConnection = myConn

Set r = Range("A5")
Set myRecordset = myCmd.Execute ' This line of code takes almost 10 seconds to complete, but only on every other time it is run
Do While True ' Infinite loop
With r
.Value2 = myRecordset.Fields(1)
Set r = .Offset(1, 0).Select 'BREAKPOINT HERE
End With
Loop
End Sub[/vba]

[tobyyR]

Thanks for the quick answer! The point of the code was to test, if one could run lots of small queries fast, that's why the infinite loop. There was a breakpoint in the loop so I could stop the execution when I wanted

I didn't test the speedup routines because the problem was solved on an other forum. I didn't close myRecordset before trying to assign a new one to the same variable and it somehow caused a slowdown. I added the modified code below, thanks for your time!

Link to the other thread: bit.ly/N4KTS1

[VBA]Sub Macro1()

Dim myRecordset As Recordset
Dim myConn As Connection
Dim myCmd As Command

sqlCommand = "SELECT CategoryId, CategoryName FROM H_BudgetEntries WHERE CategoryId=1 Or CategoryId=2"

Set myConn = New Connection

With myConn

.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=TESTDB;Data Source=.;Trusted_connection=yes;"
.Open

End With

Set myCmd = New Command
myCmd.CommandText = sqlCommand

Set myCmd.ActiveConnection = myConn

Range("A5").Select

Do While True ' Infinite loop

Set myRecordset = myCmd.Execute ' This line of code takes almost 10 seconds to complete, but only on every other time it is run
Selection.Value = myRecordset.Fields(1)
Selection.Offset(1, 0).Select 'BREAKPOINT HERE

myRecordset.Close
Set myRecordset = Nothing

Loop


End Sub [/VBA]