Query Performance: Batch or Procs

Question

Post reply

Query Performance: Batch or Procs

bigj77s

Grasshopper

Points: 24
More actions
January 15, 2010 at 6:54 pm

#144352

Hello Experts!
In your experience, as a general rule which has better performance:
1 x Batch Query
exec "INSERT INTO tbl (col) VALUES (1); INSERT INTO tbl (col) VALUES (2);INSERT INTO tbl (col) VALUES (3);"
Or
n x Stored Procs
exec s_AddVal 1
exec s_AddVal 2
exec s_AddVal 3
Thanks for your expertise!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

"IT DEPENDS". 😉 Test it with multiple different procs yourself and see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 2

Also... we have a name for inserting just one row at a time using either method... RBAR which is another four letter word for "slow".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 740241 More actions · Answer 3

I agree with Jeff, you need to test. It could go either way.

Make sure you test different situations and a variety of possible parameters.

bigj77s Grasshopper Points: 24 More actions · Answer 4

Thanks guys!!

When I run SQL profiler on the large enterprise web-apps I work on, I've always been astonished at the plethora of procs we developers typically call to fill a web page with data (over web services). And then again to process the data upon a form submit.

I thought a more efficient approach might be to package up all queries for that web page in a single batch, and execute it once. Rather than calling 20-30 procs for each little piece of data here and there as the code runs down the list of everything the page needs.

Same for the submit. Call a batch of all the sql needed to process that page, rather than just going down the code, save this: execute, wait for web services to come back, save that: execute, wait for web services, etc...

I first started thinking about this when reading some suggestions on strategies for cloud computing since I won't be using Service Oriented Architecture on a new project I'm starting.

Thought I'd see if anyone else out there has taken a similar approach or had thoughts from their experience.

Thanks again!

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 5

bigj77s (1/15/2010)
I thought a more efficient approach might be to package up all queries for that web page in a single batch, and execute it once. Rather than calling 20-30 procs for each little piece of data here and there as the code runs down the list of everything the page needs.
Same for the submit. Call a batch of all the sql needed to process that page, rather than just going down the code, save this: execute, wait for web services to come back, save that: execute, wait for web services, etc...

Now, if done correctly, THAT would most likely be true especially if you could rid of things like multiple INSERT/VALUE statements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)