Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts

Monday, March 12, 2012

Multiple Database hits vs Bulk Data parameters

I was curious to know if it the amount of data sent to the sql server mattered.I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.Thanks

For SQL Server, the cost of parsing parameters can always be ingonred compared with the cost of compiling a store procedure and producing a optimized execution plan for it.

From network I/O aspect, each execution of a stored procedure is considered as a 'batch' (batch in SQL means all data will be processed and returned at one time, which can greatly improve network performance). So if you use 3 stored procedures, you need 3 rounds; while 1 stored procedure need only 1 round.

So generally I suggest you use 1 stored procedure with 12 parameters.