what do you prefer ?

  • Thread starter Thread starter Jarod
  • Start date Start date
J

Jarod

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
 
Jarod said:
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
Click to expand...

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.
 
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.
 
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.
 
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
 
Back
Top