very high cpu/ram usage: many Excel/SQL Server ADO return-trips

  • Thread starter Thread starter Loane Sharp
  • Start date Start date
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
 
Loane,

What is the task you're performing? It's not clear from your post
what the endpoint of all your loops is. There may be a pure SQL
approach (batch update?) which is faster.

You're not compiling results from your queries into a string variable
or something like that? Sounds like the performance pattern possible
with that kind of activity...

Tim.
 
Back
Top