using the same SP to requery few tiems

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using the same SP to requery few tiems

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-08 : 04:24:04

i am using this code :
set cm_getAcc = Server.CreateObject("ADODB.Command")
cm_getAcc.ActiveConnection = Application("conn1")
cm_getAcc.CommandType = &H0004
cm_getAcc.CommandText = "sp_GetAcc"
and what i wantto do is loop and on every loop to do :
cm_getAcc.Parameters("@ID") = "number i get from some array"
cm_getAcc.Execute

is there a problem using the same object the requery the same SP but with a diffrent value
each time?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-01-08 : 04:40:01

There is no problem but the cost of round trip is more here.

Roundtrips from the application and database can be slower and this can affect performance. Imagine it loops 1000 times, that means it is creating a 1000 object of command and 1000 hits to the database too.

If you ask me how to proceed in this case, then I will make comma separate string out of the number you get from the array. And then send pass the string to the stored procedure.

And get all the details at one go.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-08 : 12:09:52

quote:
Originally posted by sachinsamuel
If you ask me how to proceed in this case, then I will make comma separate string out of the number you get from the array. And then send pass the string to the stored procedure.

And get all the details at one go.

Oh, please don't. You are going to pass a comma-separated string of 1000 items to a sproc and parse it in the sproc? Average seven characters per item, plus one more for each comma, and you are advocating sending a CVS string more than 8000 characters long as a parameter?

Ideally, you should get the details you need when you populate your array with ID values, and not hit the database again until you need to insert/update/refresh the data.

You're not using NHibernate, I hope?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-08 : 13:53:09

Depends on the situation but consider writing the sp to cope with a csv string contining maybe max 100 entries and looping through calls to that and processing the result set in each pass.
This will mean that if you have 100 or less to return then it's a single call but if it's mor e the the call overhead will not be much compared to the processing effort.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-09 : 01:05:37

quote:
it is creating a 1000 object of command

what do u say that?
i am going to loop only on :

cm_getAcc.Parameters("@ID") = "number i get from some array"
cm_getAcc.Execute

will this create a new object everytime even if i didnt do :

set cm_getAcc = Server.CreateObject("ADODB.Command")
cm_getAcc.ActiveConnection = Application("conn1")

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources