Need verification on this matter

Question

Need verification on this matter

Teemo

Ten Centuries

Points: 1376
More actions
September 26, 2011 at 8:30 am

#248765

hey guys, need help on this one. I have a .net application, On sql side,Is it much faster to execute sp rather that inline statement? meaning from .net i will send to sql an inline "SELECT * FROM tbl" or using sp like EXEC sp_getdata?
thanks in advance

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

InfiniteError (9/26/2011)
hey guys, need help on this one. I have a .net application, On sql side,Is it much faster to execute sp rather that inline statement? meaning from .net i will send to sql an inline "SELECT * FROM tbl" or using sp like EXEC sp_getdata?
thanks in advance

there will be no effective difference;

select * from some table (without a WHERE statement) will require a table scan to get all the data;

the advantages become more apprarent when you use WHERE statements...SQL will cache a plan based on the WHERE statements, and reuse the plan when a query comes thru witht eh same criteria. (ie WHERE InvoiceId = [SomeValue]

SQL Server can cache for both the ad hoc query as well as the procedure , so there's no real performance gain either way.

The advantage is using procedures to prevent duplication of code, SQL injection, and for implementing some security into the picutre (no direct access to tables...onyl EXECUTing of the procs)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

InfiniteError (9/26/2011)
hey guys, need help on this one. I have a .net application, On sql side,Is it much faster to execute sp rather that inline statement? meaning from .net i will send to sql an inline "SELECT * FROM tbl" or using sp like EXEC sp_getdata?
thanks in advance

It Depends!!!

Generally speaking you could have a slight improvement from a stored procedure over dynamic sql. This performance will increase if it is executed frequently due to plan caching. There are a lot of assumptions here (execution plan for the sproc, indexing, etc...). It is possible to see dynamic sql execute faster than a stored proc because the plan was built against table with very little data and those tables have 1 million+ rows today. There are others way more versed in this topic on here that may weigh in.

However, there are more aspects than just performance to consider when trying to determine if your application should use stored procedures instead of dynamic sql. It is impossible to determine any dependencies to objects if your dml is dynamic sql in a .net application. Building and debugging large sql statement is more painful if you have to debug in .net, then copy and paste the dynamic (and unformatted) query string to SSMS. Although it is not always 100% followed we try to make all data requests from the server using stored procedures from .net .

--edit spelling

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Actually I've seen dynamic search queries takes as much as 485 ms AVG to compile. Then only 30 ms to run :-D.

I never had the chance to test against SPs but my guess is that I would have cut that to 50-80 ms total execution time.

Edit : Of couse in that case it would have been a nightmare to generate all the relevant sps. There could have been up to 40-50 for the same search form (hence my first decision to go dynamic sql there).

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Also, it is safer to allow user code access only views and sprocs. Sprocs are usually a bit faster because they are pre-compiled while passed SQL has to be parsed and compiled. Again, it does not make much difference because on today's processors compile usually takes a very small fraction of a second.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Sean Lange (9/26/2011)
Generally speaking you could have a slight improvement from a stored procedure over dynamic sql. This performance will increase if it is executed frequently due to plan caching.

Ad-hoc queries are cached just the same as procedures. If the ad-hoc query is correctly parameterised there should be little-no difference in the caching and reuse of the plans

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Revenant (9/26/2011)
Also, it is safer to allow user code access only views and sprocs. Sprocs are usually a bit faster because they are pre-compiled while passed SQL has to be parsed and compiled.

Both procedures and ad-hoc queries are compiled on their first execution (procedures are not pre-compiled at creation time). They are both cached in the plan cache and they can both be reused (with the ad-hoc that usually requires that the query is properly parameterised).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Teemo Ten Centuries Points: 1376 More actions · Answer 7

thanks guys for all the answers.

Sorry if my example is not that clear. My example assume that all are the same (same number of records, indexes, where clause). Honeslty I'm not in favor of doing dynamic sql statement in .net, like you said it's hard to debug.

Lowell SSC Guru Points: 323518 More actions · Answer 8

InfiniteError (9/26/2011)
thanks guys for all the answers.
Sorry if my example is not that clear. My example assume that all are the same (same number of records, indexes, where clause). Honeslty I'm not in favor of doing dynamic sql statement in .net, like you said it's hard to debug.

I guess that's what we are trying to say. there is no distinct advantage performance wise for ad hoc vs stored proc. there are lots of OTHER reasons to use procs instead, but as ninja pointed out, there are situations where dynamic adhoc queries are better perforance wise.

so there's no hard and fast rule like 'procs are 5% faster" or anything like that...nope.

A lot of the posts here pointed out some of the advantages of procs outside of perforamnce, which should weigh on your projects coding style.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

InfiniteError (9/26/2011)
thanks guys for all the answers.
Sorry if my example is not that clear. My example assume that all are the same (same number of records, indexes, where clause). Honeslty I'm not in favor of doing dynamic sql statement in .net, like you said it's hard to debug.

It's harder to debug, opens yourself to sql injection if you don't parameterise properly, results in poor plan reuse if you don't parameterise properly. Makes it hard to do any performance tuning (can't get at the code so easily)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 10

InfiniteError (9/26/2011)
thanks guys for all the answers.
Sorry if my example is not that clear. My example assume that all are the same (same number of records, indexes, where clause). Honeslty I'm not in favor of doing dynamic sql statement in .net, like you said it's hard to debug.

The only real performance difference in this will be if the query runs into bandwidth issues on the way to the database server.

After all, it takes a lot more packets for the network to pass in a 10,000-character SQL string than to pass in a 10-character procedure name plus a few parameter values.

The kind of bottlenecking it takes to make that matter is rare.

You can end up with a performance difference if you go overboard and have your Data Access Layer too abstracted, but that won't be a database performance issue, that'll be a DAL performance issue. SQL Server will still cache plans and data the same way for both access methods.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Teemo Ten Centuries Points: 1376 More actions · Answer 11

i learn a lot today, thanks for all that answer 🙂

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 12

The only circumstances that I'll use dynamic sql is in those that there's no other way around it.

Example:

SELECT * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Temp;

Because the OPENROWSET command will not accept variables only a constant file name string.

DECLARE @Command VARCHAR(MAX) =

'INSERT INTO #Temp

SELECT BulkColumn

FROM OPENROWSET(BULK ' + @FileName + ', SINGLE_CLOB) AS Temp;';

EXECUTE(@Command);

It's just an example. I know 'sp_executesql' with parameters is safer because of SQL injection.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA