Re: Huge memory comsumption of ADODB Connection object

Kevin LZJ wrote:
controlHi all,

I encountered such a problem: ADODB Connection comsumes huge
memory before it is closed.
I use VB to port some data from EXCEL file into SYBASE database.
For some reason, I first convert the EXCEL file to DBASE format, then
use ADO Data Control to get data from DBASE file, finally, i use
ADODB to insert data into SYBASE database (using ADODB provider from
SYBASE corp). The problem is, I use a database stored procedure
to insert data in
which there is a cursor to check whether same data is existing. If the
content of cursor is more than 300 items, I found the memory used
increases greatly (in my case, there are about 600 data items in the
cursor, causing the memory used up to more than 2GB and getting error
for the lack of memory). After some research, I found 2 points :
1). If I delete the cursor from the Stored Procedure, my app uses
memory normally.
2). Only when the ADODB connection is closed, can the huge memory
used be freed.

So, my question is: why the ADODB connection object uses such huge
memory even the stored procedure is running in the server? And, how
can I get this problem worked out?

BTW, in my environment, the SYBASE Database Server also runs on my
laptop as well as the VB app does.

Basically - stop using the cursor in your procedure. It's not necessary.
You will need to post this in a Sybase group to get the correct syntax,
but in SQL Server I would do this:

IF EXISTS (SELECT * FROM table WHERE ...)
UPDATE table ...
ELSE
INSERT table (...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Huge memory comsumption of ADODB Connection object
    ... that's why I use cursor to get each row. ... comsume so much memory ... , or SYBASE. ... ADODB Connection comsumes huge ...
    (microsoft.public.data.ado)
  • Re: Multi user capability
    ... Are people going to get the same Excel file?, dump to the same Excel File? ... Processing loaded data in sql tables ... I implemented these three stages by using set of physical temporary sql tables (not the memory ones with # and ##) for data transformations etc. ... I used to delete the data in these temporary tables and reuse for the next client. ...
    (microsoft.public.sqlserver.dts)
  • Efficient parsing of large Excel documents in Ruby
    ... I am currently using the parseexcel gem to parse an Excel file so that I ... It would seem that I have two issues - memory usage and performance. ... I am transferring this data into a SQL Server database, ...
    (comp.lang.ruby)
  • Re: HELP - Not enough Memory, Out of system resource!
    ... For seeking a solution, does I need to upgrade any ... Excel 2000 on handling memory? ... If successfull, check the file format of the Excel file -in File SaveAs, ... and check the computer ram and HD space over [2GB free ...
    (microsoft.public.excel.misc)
  • Re: HELP - Not enough Memory, Out of system resource!
    ... For seeking a solution, does I need to upgrade any hardware for creating a large size of table? ... If successfull, check the file format of the Excel file -in File SaveAs, File type info- and if relevant, change to standard Excel file and save your file. ... enough memory and out of system resource is pop up, as I try to save my work ... and check the computer ram and HD space over [2GB free ...
    (microsoft.public.excel.misc)