I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Any thoughts? Dennis

Hi dennis,

I clear my recordset variable after each query. I

make sure that you set _all_ objects to nothing when you finished your code!
Esp. the connection string.

If you have many queries with one connection string then make sure that you
open the connection only once, run your queries and close the connection.
This is a lot faster the open-query-close-open-query-close...

After a while and lots of queries your Excel will be slow and using a lot of
memory anyway. However, you can work much longer without troubles when you
do what I mentioned.

regards

arno

don't know if this is your situation, but you might check this out:

http://support.microsoft.com/default...98&Product=xlw
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

--
Regards,
Tom Ogilvy


"Dennis" wrote in message
...
I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Any thoughts? Dennis

"Dennis" wrote ...

I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Are you querying an open workbook?

Microsoft Knowledge Base Article - 319998
http://support.microsoft.com/default...;en-us;Q319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO

If this applies, save the worksheet to a temporary workbook, close it
and query the closed workbook. Here's some example code:

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"MySheet"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

' <<do something with recordset

rs.Close
Con.Close

End Sub

Jamie.

--

"arno" wrote ...

make sure that you set _all_ objects to nothing when you finished your code!
Esp. the connection string.

Any particular reason for this advice? My experience is that if you
let the objects go out of scope, ADO cleans up impeccably.

Jamie.

--

Thank you for your help. I will give your comments a try.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Jamie,

make sure that you set _all_ objects to nothing when you finished your
code!
Esp. the connection string.

Any particular reason for this advice?

run queries eg. in a loop and never cleanup. each query will take longer and
longer and the memory usage of excel (task manager) will grow and grow.

impeccably.
sorry, I do not know this word. anyway: do not clean up = wait.

arno

"arno" wrote ...

run queries eg. in a loop and never cleanup. each query will take longer and
longer and the memory usage of excel (task manager) will grow and grow.

That's not my experience. Please post your code that reproduces this
e.g. using northwind.

Many thanks,
Jamie.

--

Hi Jamie,

run queries eg. in a loop and never cleanup. each query will take longer
and
longer and the memory usage of excel (task manager) will grow and grow.

That's not my experience. Please post your code that reproduces this
e.g. using northwind.

My experience is different. I am running ADO requests vs. an Access mdb that
contains only links (ODBC, whatever is neccessary) to tables like Foxpro,
text, xl-files and other mdb. In queries I can link tables form different
sources (a kind of mini-datawarehouse), so Northwind does not help. And I
run queries vs. not saved excel-files that contain the data from other
queries (named ranges, not sheets). I use all kinds of sql (select, insert,
update, union, full outer joins etc) to all kinds of tables. When I do _not_
cleanup the objects (esp. connection string and recordset) the size of
excel.exe will grow and queries will be slower. When I cleanup - everything
is fine.

The second point is that I run multiple queries on my mini-datawarehouse
within one macro. I found it a lot faster to open the connection, run all
queries and close the connection instead of open-run-close-open-run-close...

Still, excel.exe is growing and queries take longer, however, this happens
very very slowly. Note, I am runing thousands of queries a day, that's my
job. The reason for this could be "normal" excel bahaviour that everyone
knows when opening lots of files, some small files, some with megabytes of
data, runing code...

There's nothing special in my code, I have functions for the connection
string and for the query. These functions are called from the
"report"-makros that do the rest.

This is why I am giving the advise to cleanup and run many queries by
opening only one connection as this works perfectly in my rather complex
environment. These tips should work anywhere. when you follow the KB-"Howto"
samples for ADO performance issues should not arise but maybe some people do
not care too much about cleaning up variables/objects. In many cases not
cleaning up does not have any consequences but with ADO queries it does.

regards

arno

"arno" wrote ...

In queries I can link tables form different
sources (a kind of mini-datawarehouse), so
Northwind does not help

Are you saying you cannot (easily) recreate the situation using only
northwind? If failing to clean up was the sole cause of the problem
then you should be able to recreate it using a single data source.

Just to be clear, are you saying that

Sub Test()
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.Open <snip
<snip
' Just let the open connection go out of scope
End Sub

causes the Excel.exe to grow and slow down queries, whereas:

Sub Test()
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.Open <snip
<snip
Con.ActiveConnection = Nothing
Set Con = Nothing
End Sub

does not?

I run queries vs. not saved excel-files

A not saved Excel file can only mean an open worksheet and the title

BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO

means you are almost certainly going to be experiencing problems due
to the memory leak bug, even if you do explicitly clean up your ADO
objects. That's why I asked for an example that just uses northwind
i.e. it eliminates other elements which may be the cause of the
problem. In lieu of a test, the situation you describe makes me wonder
whether you have misdiagnosed the problem i.e. it could be due to the
Excel memory leak bug, not allowing enough time for connections to
close, Jet failing to clear lock files, etc.

Based on what you originally said:

run queries eg. in a loop and never cleanup. each query
will take longer and longer and the memory usage of
excel (task manager) will grow and grow.

here's my quick test. In a loop, open a connection to northwind, open
an recordset, let the (open) connection and (open) recordset objects
go out of scope, wait a generous three seconds (to eliminate the
possibility of timing issues):

Option Explicit

Private Declare Sub Sleep _
Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Sub Test()

Dim lngCounter As Long

For lngCounter = 0 To 20
GetData
Sleep 3000
Next

End Sub

Function GetData() As Long

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strSql As String

Const strPATH As String = "" & _
"C:\Program Files\" & _
"Microsoft Visual Studio\VB98\" & _
"NWIND.mdb"

Set oConn = New ADODB.Connection

With oConn
.CursorLocation = adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPATH
.Open
End With

strSql = "SELECT lastname FROM employees"

Set oRs = oConn.Execute(strSql)
GetData = oRs.RecordCount

End Function

While this code is running, I look at Excel.exe in task Manager and
see it fluctuating slightly i.e. no significant or constant increase.

I agree that best advice is to explicitly close connections and
recordsets, and I always do so myself. What I'm seeking is evidence
that not doing so actually causes problems.

Thanks,
Jamie.

--

Hi Jamie,

Are you saying you cannot (easily) recreate the situation using only
northwind?
Yes. Making a query to Northwind with only one field for only 20 times
produces 0.001% of what I am doing. If you really want to test make the
following:

What I'm seeking is evidence
that not doing so actually causes problems.

- create a new mdb, link all tables of Northwind (link some dbf-tables,
too).
- run queries on the new mdb only
- make sure you get thousands of records in your recordset and many (what
about 250?) fields. Use a complicated sql-statement, eg. join 2 tables, use
conditions (and group by...)
- do not pause the macro to avoid what you call timing problems (you don't
want to wait 1000x3sec, do you?)
- loop through the query maybe 1000 times.

If failing to clean up was the sole cause of the problem
then you should be able to recreate it using a single data source.

Just to be clear, are you saying that

Sub Test()
' Just let the open connection go out of scope
End Sub

causes the Excel.exe to grow and slow down queries, whereas:

Sub Test()
Con.ActiveConnection = Nothing
Set Con = Nothing
End Sub

does not?
Correct. In a quick test using a foxpro table I started with excel.exe with
18MB. At the end I had with cleaning 25MB, without 118MB of RAM (30 queries,
a very simple one, 525 records as result).


I run queries vs. not saved excel-files

A not saved Excel file can only mean an open worksheet and the title
I fill my workbook with data from the mdb-file. Then I run queries on my
workbook.

BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO
I know this one. It uses Worksheets as databases, this is one of the reasons
why I use _named_ranges_ as databases. I do not have a problem with excel
files.

In the end, I do not know why my excel was so terribly big and slow
(sql-statements, odbc drivers, lots of data in the worksheets, joining
different sources (mdb and foxpro)...) but cleaning up and using one
connection for many queries solved everything.

arno

"arno" wrote ...

Hi arno,

- create a new mdb, link all tables of Northwind (link some dbf-tables,
too).
- run queries on the new mdb only
- make sure you get thousands of records in your recordset and many (what
about 250?) fields. Use a complicated sql-statement, eg. join 2 tables, use
conditions (and group by...)
- do not pause the macro to avoid what you call timing problems (you don't
want to wait 1000x3sec, do you?)
- loop through the query maybe 1000 times.

I think those steps to reproduce are unreasonable. Worse, there are
too many elements involved to be able to isolate 'not cleaning up the
ADO objects' as being the cause of the problem. Applying any kind of
'scientific method' requires an experiment to be repeatable, rather
than being limited to something that can only be reproduced in your
laboratory.

In a quick test using a foxpro table I started with excel.exe with
18MB. At the end I had with cleaning 25MB, without 118MB of RAM (30 queries,
a very simple one, 525 records as result).

Now this sounds like a reasonable test. Please give details of the
Foxpro table, provider used and your code. That said, it sounds very
similar to the test I did with northwind but with different results.
This suggests to me the problem lies with Foxpro, the Foxpro provider
or perhaps your code (you are letting those recordsets go out of
scope, right?), rather than ADO. But I'll keep an open mind and await
the details.

do not pause the macro to avoid what you call timing problems (you don't
want to wait 1000x3sec, do you?)

I don't mind a test taking an hour or more to run if it eliminates the
possibility of external influences.

BUG: Memory Leak Occurs When You Query an
Open Excel Worksheet Using ADO

I know this one. It uses Worksheets as databases,
this is one of the reasons why I
use _named_ranges_ as databases.

Your assumption about ranges is incorrect. Defined Names/named ranges
are not immune to the memory leak bug. The article talks about
worksheets but think about it: a range requires a worksheet to exist.
Querying a range involves querying a worksheet.

If you are not convinced, try the 'Steps to Reproduce the Behavior' in
the article, using a named range. I've done so many times and I tried
again just now and can confirm a /named range causes the memory leak.

As I said before, cleaning up the ADO objects is no solution to the
memory leak. There is no recovery I know of without restarting Excel.

Until you have eliminated open workbooks from your scenario your
anecdotal evidence does not carry much weight. If you use the article
for ideas for a resolution, I'll warn you off its Method 1. In my
tests, using the SELECT INTO syntax of the Jet OLE DB Provider to
export the Excel data to a new worksheet still causes a memory leak if
the table in the FROM clause resides in an open workbook.

Jamie.

--

Hi Jamie,

I think those steps to reproduce are unreasonable. Worse, there are
too many elements involved to be able to isolate 'not cleaning up the
ADO objects' as being the cause of the problem.

Right, ADO might not be the reason.

Applying any kind of
'scientific method' requires an experiment to be repeatable, rather
than being limited to something that can only be reproduced in your
laboratory.

Banging your head against the wall is easy to reproduce but cannot replace a
crashtest for vehicles ;)

Your assumption about ranges is incorrect.

There were some other points to use names instead of sheets, but I do not
recall what (annoying problems) they were. Names are not mentioned in the
article but they could have a problem, too.

Until you have eliminated open workbooks from your scenario

no, no :)

I tested one of my files with cleaning and without and I got the same
results (9 queries run 6 times, some to linked foxpro, some to linked mdb,
some to the open xls):
MB/sec after each run, equal results for cleaning and w/o:
49/12
55/15
61/14
67/15
73/16
79/18

so, cleaning up did not have _any_ effect on MB and seconds! however, the
memory leak bug is obvious.

I am a little surprised now, however, 2 years ago I redesigned my template
for ADO/SQL queries which lead to a jump in performance (speed). I'm really
wondering what I did at that time...... but it stills works great ;)

regards

arno

"arno" wrote ...
Names are not mentioned in the
article but they could have a problem, too.

They most definitely are a problem.

I tested one of my files with cleaning
and without and I got the same
results (9 queries run 6 times, some
to linked foxpro, some to linked mdb,
some to the open xls)

You have included an open workbook and got a memory leak, so your test
proves nothing we don't already know. From your earlier post, it seems
you may be experiencing a double leak i.e. one from the open xls and
one from the Foxpro source.

Try isolating each data source i.e. one test to query only the
unlinked mdb tables, another to test to query only the foxpro data
(don't bother testing the open xls at all because this is a known
issue).

Have you ever played the board game Clue/Cluedo? If you call Oberst
von Gatow to the Musikzimmer with the Heizungsrohr, you could get
various results but what if you already hold two of those cards...?

Banging your head against the wall is
easy to reproduce but cannot replace a
crashtest for vehicles ;)

I suggest you only need to conduct the test twice: once without the
wall and once with (in that order). That should give you a good idea
whether it is the wall causing the pain. Warning: like the ADO bug,
this could result in memory loss and much leakage <g.

Jamie.

--

Hi Jamie,

all my questions are answered with my testing, both test only showed the
memory leak and nothing else as cleaning up was the only variable but the
results were the same (= no effect of the variable).

From your earlier post, it seems
you may be experiencing a double leak i.e. one from the open xls and
one from the Foxpro source.

There's no difference when testing the mdb-linked foxpro table with and w/o
cleaning up. you know, I'm testing the vehicle not the head;) I did not pay
enough attention on my first quick test, now, both tests showed 43 sec and
23 MB of RAM with a result table of 540rows x 34cols, 30 queries, Ram
started with 16.8MB.

regards

arno