Re: ADO and Cursors with MSSQL

From: Arnie Mauer (xxx_at_nowhere.net)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 15:30:25 -0500

"Schlup Herbert" <hschlup@external.ch.nospam> wrote in message
news:4060a5e6$1@newsgroups.borland.com...
>
> Hi All
>
> I have a programm that uses ADOConnection with MSSQL. I like to insert
about 100'000 records. No I saw, that when I use parameters with my
Insert-Statement, the memory used by MSSQL is not so heavy increasing as
when i use a specific Insert-SQL Statement for each row.
>
> I think this is because ADO creates a cursor for each insert. Now, in
several cases I have to use this 2nd possibility. So how can I free the
cursors so that the SQL-Server can free the memory?
>
> The problem is that the server is taking memory, and he never free's it.
>
> Herbert

One possibility. SQL Server will cache SQL statements. Therefore, if you
repeatedly issue the same parameterized statement, the cached statement,
which is already parsed, will be used. In your second case, each individual
statement is cached and never used again. It is always preferable to use
parameterized statements.

- Arnie

Relevant Pages

  • Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.
    ... site MSSQL database, and then to the corporate MSSQL server. ... surprised when my ADO implementation became a memory pig. ... Jet or MSSQL. ... I started freeing each TADOCommand or TADOQuery as ...
    (borland.public.delphi.database.ado)
  • Re: xmalloc string functions
    ... it makes perfect sense to crash ... back that there's insufficient memory. ... Are you writing performance specifications for Microsoft? ... Actually, we use MSSQL a fair bit around here, because a few apps ...
    (comp.lang.c)
  • Re: xmalloc string functions
    ... Ah, right, so if it can't allocate enough memory to process that large ... *database* app. ... Are you writing performance specifications for Microsoft? ... Actually, we use MSSQL a fair bit around here, because a few apps we need ...
    (comp.lang.c)
  • Re: MSSQL 2000 Standard Edition not utilizing Maximum Memory!!!
    ... Verify if your application is I/O bound or Memory Bound ?? ... If your cpu utilization is fixed at 100%, ... > Can anybody tell me why Memory is not utilized by MSSQL Service. ...
    (microsoft.public.sqlserver.server)