471,330 Members | 1,074 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,330 software developers and data experts.

what do you prefer ?

Hey
I don't know what's best. I can invoke 20 times stored procedure with
diffrent id's to do something. Or put this id's as one long varchar string
and in stored procedure use some userDefined function to cut it into single
peaces. What's better way ? It seems like the second one ;)
Jarod

Jan 18 '06 #1
4 1045
jmi

Jarod wrote:
Hey
I don't know what's best. I can invoke 20 times stored procedure with
diffrent id's to do something. Or put this id's as one long varchar string
and in stored procedure use some userDefined function to cut it into single
peaces. What's better way ? It seems like the second one ;)
Jarod


If the stored proc you're invoking is on a server that's part of a
network you may want to go with the second option. Having too many
network-round-trips (especially on slow network) can make it seem that
your app is slow.

Jan 18 '06 #2
This is a bad idea, IMO. It makes the code more complex, and there are
ways around multiple network hops.

For example, the root for data adapters, DbDataAdapter, allows for batch
processing to occur. With this, you can have your SP invoked 20 times, but
have only one call made to the server.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"jmi" <mi**@askish.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Jarod wrote:
Hey
I don't know what's best. I can invoke 20 times stored procedure with
diffrent id's to do something. Or put this id's as one long varchar
string
and in stored procedure use some userDefined function to cut it into
single
peaces. What's better way ? It seems like the second one ;)
Jarod


If the stored proc you're invoking is on a server that's part of a
network you may want to go with the second option. Having too many
network-round-trips (especially on slow network) can make it seem that
your app is slow.

Jan 18 '06 #3
jmi
Jarod --
Does the proc return a result set? Not knowing what method you were
using to make the call...in general you want to reduce your round trips
when possible. But if your proc is using these IDs to fetch data then
I would agree with Nicholas...use DbDataAdapter or one of it's
descendents. Your proc can return the 20 resultsets from one call.

Jan 18 '06 #4
But presumably if your doing the same thing each time (give or take an id)
you could return 20 rows in a single result set.

Command batching is fine, but it is not necessarily supported on every
database server / version.

Personally, for this type of job I tend to go down the "single SP call
approach", where my SP accepts a varchar(8000) for the ids in a CSV format,
which I then pick apart using a table-udf (coded once, reused in many
places) - i.e. my udf splits the string and returns a single column,
multi-row result of values. I can then use this as the base for whatever
joins etc I need. This approach (after much peformance profiling) appears to
be better performing at the database - i.e. it can be (depending on
circumstances) much quicker to look in a table for 20 values (via an inner
join) than it can be to look for 1 value 20 times... plus any triggers etc
only get called once... plus less chance of getting a lock escalation issue
halfway through your method since you can take out an UPDLOCK on all the
data from the outset, rather than updating the first 15 only to find that
the 16th is locked, and then having to rollback the other 15...
Most of the above are SQL issues, not C# issues, but that's my opinion.

Marc
Jan 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

30 posts views Thread by Reinhold Birkenfeld | last post: by
9 posts views Thread by Jay | last post: by
21 posts views Thread by Tee | last post: by
30 posts views Thread by Bill Reid | last post: by
44 posts views Thread by Steven D'Aprano | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.