L
Loane Sharp
Hi there
I'm making many round-trips to SQL Server from Excel using a VBA module and
an ADO connection. Since I'm connecting to only one SQL Server database, I
keep the same connection open throughout the procedure, using the
"conn.Close( )" and "Set conn = Nothing" statements only at the very end.
Then, using this connection, I iterate through each record in the database
creating a new recordset each time, i.e. "For i = 1 to 117394 | Set rs =
conn.Execute( , ) | ... | Next i", etc. I know this must sound inefficient
and slow, which it is, but I find it's even slower to pull a single
recordset containing all records up-front and then loop through this using
"Do While Not rs.EOF | ... | rs.MoveNext | Loop", since the database is very
large (about 9GB), there are many complex joins between the tables (at least
40), and the requisite VBA code is a bit unwieldy to say the least.
I encounter the following problem: for the first few thousand records the
procedure is zippy, but thereafter the performance tapers off, becoming
progressively slower and slower; CPU usage rises to 100% and Excel ends up
absorbing all the available RAM and paging file memory. I presume that I am
not releasing objects from memory, though within the loop that creates a
recordset I also give the statements "rs.Close( )" and "Set rs = Nothing".
Can you shed any light on where the memory-intensive part of the operation
is, or perhaps I'm way off track.
Best regards
Loane
I'm making many round-trips to SQL Server from Excel using a VBA module and
an ADO connection. Since I'm connecting to only one SQL Server database, I
keep the same connection open throughout the procedure, using the
"conn.Close( )" and "Set conn = Nothing" statements only at the very end.
Then, using this connection, I iterate through each record in the database
creating a new recordset each time, i.e. "For i = 1 to 117394 | Set rs =
conn.Execute( , ) | ... | Next i", etc. I know this must sound inefficient
and slow, which it is, but I find it's even slower to pull a single
recordset containing all records up-front and then loop through this using
"Do While Not rs.EOF | ... | rs.MoveNext | Loop", since the database is very
large (about 9GB), there are many complex joins between the tables (at least
40), and the requisite VBA code is a bit unwieldy to say the least.
I encounter the following problem: for the first few thousand records the
procedure is zippy, but thereafter the performance tapers off, becoming
progressively slower and slower; CPU usage rises to 100% and Excel ends up
absorbing all the available RAM and paging file memory. I presume that I am
not releasing objects from memory, though within the loop that creates a
recordset I also give the statements "rs.Close( )" and "Set rs = Nothing".
Can you shed any light on where the memory-intensive part of the operation
is, or perhaps I'm way off track.
Best regards
Loane