Re: Memory problem - writing to database

From: Bonj (a_at_b.com)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 13:40:36 -0000

When I say open a connection every time and close it when you are done with
it, I meant you to do this *aswell as* Brian's idea of using an SP, not
instead of.
I don't know whether you have gone with keeping a connection open to execute
your SP on, or opening and closing them, but it seems like you have got it
sorted.
But I think it was the recordset that was causing you so much grief. I
always tend to shy away from dynamic updateable recordsets, especially when
they're got from a "select" statement rather than table-direct. The SP makes
sense though as it means SQL server doesn't have to keep parsing INSERT
statements, which offers much more of a performance gain in this situation
where you have a lot of frequent operations as opposed to one long one.

"Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message
news:6E76464C-E175-4F76-B4CF-8A75B0E0E562@microsoft.com...
> Thank you.
>>which is to open a new connection for every new piece of
>> data, and importantly, make sure you close it as soon as the data is
>> inserted.
> Does not opening and closing db connection slow down the program ? Does it
> take a lot of resource to keep opening and closing the db connection ?
>
> "Bonj" wrote:
>
>> > If I do not have the recordset open all the time, I will need to query
>> > the
>> > database again to add a new record. I do not want to do this, because
>> > it
>> > will
>> > take a long time to query the database (the db can have up to 10
>> > million
>> > records).
>>
>> Don't use a recordset to add new data. Instead, fire off an "INSERT"
>> statement.
>>
>> > I have to keep the connection open all the time also
>>
>> You don't.
>>
>> > , because data is coming
>> > in every milliseconds, and as the data comes in, I need to write all of
>> > them
>> > to the database.
>>
>> Try my suggestion - which is to open a new connection for every new piece
>> of
>> data, and importantly, make sure you close it as soon as the data is
>> inserted. The connection should be opened as late as possible and closed
>> as
>> soon as possible.
>>
>>
>>

Relevant Pages

  • Re: Refresh DB
    ... connection than closing and opening it? ... you can use Requery, which is the same as if you would call Close and ...
    (borland.public.delphi.database.ado)
  • Re: global connections
    ... you should be opening and closing it when you need it. ... you create a single pipe through which all of your data ... Using C#, would it be best to have a global> connection and datareaders etc, or is it better to put all the db stuff> into a seperate class and make calls to this whenever I need to> read/amend data? ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Run-time error 2147217841 Query Timeout expired
    ... Are you opening and closing your connection on each ... app, and expecting to stay alive whilst the app is sleeping between hourly ...
    (microsoft.public.vb.general.discussion)
  • Re: Memory problem - writing to database
    ... > take a lot of resource to keep opening and closing the db connection? ... I'm not even sure that ADO classic doesn't have a clever form of connection ... I also strongly suspect that by ... Don't worry about the fact that you're constantly opening new connections - ...
    (microsoft.public.vb.general.discussion)
  • Re: Why Would ResponseStream().Read return zero bytes when not at end of file?
    ... there is no network requirement that the number of bytes delivered by the server before closing the connection is identical to the number of bytes the server advertised in the HTTP response. ... The advertised length is just that: an advertisement. ...
    (microsoft.public.dotnet.languages.csharp)
Loading