Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-02-16 : 16:27:38
|
| I'd like to have some advice from any of you on how to write a data access routine to achieve maximum performance.Let's say, I have a sub called UpdateOrder() from a client application. It will call out 5 different stored procedures.It has an ado connection, ado command, and recordset/dataset.If it uses following for the 1st sp, ... cn.Open conDevConnetionString cmd.ActiveConnection = cn cmd.CommandType = adCmdStoredProc cmd.CommandText = "OP_getJob" cmd.Parameters.Append cmd.CreateParameter("@Jno", adVarChar, adParamInput, 15, strInput) rs = cmd.Execute then, for the rest of the sps, should I reuse the same cmd and cn, or close them, and recreate another set of them? My first thought was to reuse cmd and cn, but some other member from the team thought starting another set for each call is more readable. What do you think, particularly from the prospective of performance? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-16 : 17:53:07
|
performance wise it's beter to leave it open and close it at the end of all 5 sproc executions.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-02-16 : 17:57:39
|
| I guess my question is more of a client side programming question, and has little to do with dba or database programming. But for all the practical purpose, it should not be a uncommon one.All the samples I found are either on ado or sp, and are mapping them one on one.Well, I will keep looking.Thanks! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-16 : 18:30:35
|
| well client side or not opening and closing and then opening and closing and.... is kind of inefficient, no?if you're going to call all 5 sprocs one after another then keep the conn open.the command you have to recreate each time.Go with the flow & have fun! Else fight the flow |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-02-17 : 04:15:35
|
| And for yaks sake: use RS.GetRows! ->rs = cmd.ExecuteIF NOT rs.eof THEN myArray = rs.GetRowsrs.closeSet rs = nothingIt will increase performance *drastically* even with small recordsets. myArray is now a two-dimensional array...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Best practice: ADO + StoredProc