Re: Huge memory comsumption of ADODB Connection object

Hi, Bob,

Thanks for your kind reply.
However, in my case, it's impossible to check whether same data exists
only by using "if exists (select ...)", I need to apply some complicated
rules for checking data, that's why I use cursor to get each row. My question
is: the procedure runs in Sybase Server, why it makes ADODB Connection object
comsume so much memory (in my understanding, ADODB acts as a client ).
Moreover, I first must determine this problem come from ADODB
(Microsoft), or SYBASE.


"Bob Barrows [MVP]" wrote:

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
    ... unless the cursor is returning data to the client* I don't see ... I suppose this could cause some extra memory ... Is there a similar behavior in Sybase? ... Server was originally based on that rdbms, so there very well may be. ...
    (microsoft.public.data.ado)
  • Re: Huge memory comsumption of ADODB Connection object
    ... It's a equipment management system, in which there are some equipment in ... (not using cursor to do this). ... ADODB Connection comsumes huge ... memory before it is closed. ...
    (microsoft.public.data.ado)
  • Re: Huge memory comsumption of ADODB Connection object
    ... As far as the server-based cursor affecting the Connection object, ... determine this problem come from ADODB, or SYBASE. ... ADODB Connection comsumes huge ... memory before it is closed. ...
    (microsoft.public.data.ado)
  • Re: Old Mac Mini - what system to upgrade to?
    ... If there is a cursor, ... Maybe 3.5" disks required less spin-up current when the enclosure was designed, or maybe the enclosure engineers found that limiting spin-up current would extend the life of a disk. ... I have found that with Safari running, I may have only a sliver of free memory. ... CNodeRegister:RegisterLocalNetInfoHierarchy::Call to netinfo_domainname for local domain name lasted 10 seconds. ...
    (comp.sys.mac.system)
  • Re: how not to run out of memory in cursor.execute
    ... and I find that the cursor.execute method uses a lot of memory that ... help, since execute doesn't give its memory back, after reading enough ... memory back from execute in cx_Oracle and MySQLdb? ... cursor iteration idiom: ...
    (comp.lang.python)