I develop a MTS component (DLL). It will retrieve data from several tables (at least 5 tables in each time). I'd like to know your opinion what is the best way to make a query to a database?
Printable View
I develop a MTS component (DLL). It will retrieve data from several tables (at least 5 tables in each time). I'd like to know your opinion what is the best way to make a query to a database?
Wow that is a very broad question.
Are you getting your data from SQL server ?
For both servers remember to :
-create late / destroy early (your objects that is)
(create objecst and connections late and destroy them soon as done with them, that goes for your DLL too when called from the clients)
-Utilize connection pooling by using module level variables ( or objects if you must, avoid using objects where possible i.e. Connection object)
-use forward only , read only cursors where all possible
- avoid using more then one object when not need.
- Make use of stored procs (big plus here), using Command object, if you are expecting a return.
-Try to follow this order : Connection obj,command obj, recordset as a last resort
-Use disconnected recordsets when appropriate (i.e. you need to pull a small recordset, but it might sit on Joe's desktop from morning till lunch)
-If you are expecting lots of traffic, put the MTS and SQL on separate machines.
In short the best way I guess depends on the situation and you 'the programmer'. Personally my self, I used lots of stored procs and a single command object with the same connection string every single time.
That is all I can think of right now. I hope this is what you were looking for.
What the heck happened to the spell checker ?