SQL Connection Performance Problem

[Guest_imported]

I need to query of set of around 3000 records and perform around 15 to 30 queries on each record (done by calling subroutines). I'm facing a problem of performance when many users are accessing the file at the same time, due to opening/closing the connection many times.

How can I optimize my operation, keeping in mind that the functions/subroutines are in a seperate file that's being included in my processing file?

Is there a way to track the number of times a connection is opened?

 

[dianal]

Hello,
I assume that you use ASP.
(The question is posted in ASP forum.)
Use the application object

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/iisref/html/psdk/asp/vbob8zw4.asp

on application_onstart

to store a connection to database once and use it in your file as many times as you want.

D.

 

[link9]

If you're using Access as your db, then it's going to give you nothing but headaches trying to support multiple concurrent users -- My suggestion would be to upgrade to an enterprise dbms and then just create your connections on each page. It shouldn't give you any performance trouble if you have a good db behind it.

If you're already using something like SQL Server, then you have nothing to worry about -- it can handle it.

Problem with the application object is that the connection will have to either stay open all the time (???) or each page will have to try and open it when it's loaded. If you try to open it on each page, then you run into the same problems as creating them on each page --


Paul Prewett