tobyyR
07-12-2012, 09:13 AM
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!
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
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!
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