Performance question

I am using ADO and SQL server 2000 and I was wondering, is it better to open a global connection and then use that one when needed or to create and open a connection locally just when needed(this is what i am doing now). Thanks

Your preference and probably depends on how much data retrieval/editting will be going on.

If you have time do both methods and ask the network operators to monitor for you (plus your own timings).


Vince

For interactive systems where you are reading/writing data at regular intervals, then I would have one global connection left open throughout the apps 'life'. I think one connection can handle 255 recordsets. This will save time having to create/open/close/destroy connections at a local level.

Does this include/involve ASP (VBScript)? How does one create a global connection?
Thanks
Michael

i think what he means is decalring it in a module i.e

Public cn AS BLAH BLAH

B :)

I think its better to open a connection, read data and close connection immidiately. The OLEDB provider will cache the connection if you reconnect immidiately so there'e no overhead in authentication if you're using the same connection string. You can have your connectstring as global and then create/destroy your connections locally.
It would be better if you introduced some kind of public methods such as GetData, ExecuteSQL to execute all database operations.

Regarding ASP (VBSCRIPT) if you want a global connection object throughout your 'site' then declare it in the Global.asa file.
TIP:
If you screw up your code in Global.asa and run it, it will not fix itself until you either reboot or stop your Web services and restart them. Good Luck!

Global connections are a good idea. You can always close them and re open them if you don't want to keep a constant connection.

It may be good idea to declare global connection variables, but it's bad idea to make them 'live' through app. (I mean the app that be used by more than few user, if stand alone app it may OK)