Re: Huge memory comsumption of ADODB Connection object

I would be willing to bet that, with some thought, your complicated rules
could be accomplished without a cursor. However, I'm in no position to try
to win that bet.

As far as the server-based cursor affecting the Connection object, unless
results are being returned to the client via the cursor, there should be no
impact on the client from using a server-based cursor in a stored procedure.
Additionally, a Connection object is not a "data container", it's more of a
conduit between the database and an ADO data-receiver. So I'm unaware of any
reason for your problem.

Perhaps you could show us how you call that stored procedure ...

Kevin LZJ wrote:
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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

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: 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: 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)
  • Sybase module 0.37pre2 released
    ... The Sybase module provides a Python interface to the Sybase relational ... It supports all of the Python Database API, ... * Cursor state initialisation fix from Skip Montanaro. ... * Output parameters now work for FreeTDS 0.62.4. ...
    (comp.lang.python)