Re: Memory problem - writing to database

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

Date: Fri, 24 Dec 2004 13:35:03 -0000

> 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 ?

Well... I'm suspecting that it won't slow it down as much as your current
method will. Try it! One of the important principles in programming, is that
the method that *seems* like it would be the fastest, best, or whatever,
isn't always the fastest/best etc. at all - you just have to experiment with
other ideas, and hey - I've given you another idea. You can take a backup of
your current code and then chop and change it about as much as necessary -
you don't lose anything by trying it out, even if the change is complete
bladderdash! (though I suspect it's not...)

I'm not even sure that ADO classic doesn't have a clever form of connection
pooling anyway, similar to ADO.NET. Even if it doesn't, it's even more
probable that SQL server itself does. I also strongly suspect that by
closing a connection, you are telling the RDBMS that it can perform
necessary housekeeping code and can 'flush' things - your current idea of
keeping a recordset (and hence also a connection) open all the time you're
starving it of the opportunity to do these things, but you're still
inserting loads of data.

Don't worry about the fact that you're constantly opening new connections -
instead, *try it*. You might be impressed with the results. If you still
notice the same slowdown, *then* post back and tell me it's a crap idea.

And I don't know whether you are, but if you are using task manager to
measure the memory usage of the program, please find something more accurate
if you want to report credible results of serious testing. You could use
process explorer from sysinternals, or alternatively an internal method,
such as having a column in the database table logging the time the record
actually made it to the database, and a column where you insert the time
that the record was actually received (which you remember as soon as the
record is received) - although if you use this method don't let the server
calculate one of the times and the client calculate the other - have the
client calculate both. The difference between the times then gives you a
rough indication of the overall latency.

Use an accurate performance testing method (more accurate than looking at
the memory usage in task manager) on your current method, and the same test
on the method I suggest, implemented exactly as I have described, and post
back the results.

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: Problem with ER : cdr sync replicate
    ... I suspect that the 47 error is leading eventually to the other failures because logic is that when we are trying to destroy the part structure, that we attempt to connect to the server that the part is associated with and then perform a clean shutdown. ... I suspect that in UC4 that there is some issue with improper cleanup when the initial connection never occured. ... (nameIn should be the group name...) ...
    (comp.databases.informix)
  • Re: Trigger-happy Met Armed Cops still havent learned to stop behaving like armed thugs
    ... some connection with the raid was wrong. ... appeared in the middle of an attempt to arrest some one who may be armed and may already have shot two people could neither disrupt the operation or be at risk from the operation was wrong ... I am suggesting that only the suspect, ...
    (uk.legal)