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.


"Loane Sharp" wrote in message
...
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