Whether to use or not a Stored Procedure
-
I have to construct a search query based on several no. of criterias which are dynamic such as to filter by the name of 10 countries, 10 religions, 10 occupations (for eg.). I have constructed the query at the front end and processed. Is there any performance gain, if we create a stored procedure using dynamic Query inside a procedure.
-
Well, building the statement at the client and sending it to the server might make you more vulnerable to injection.
Building it at the server and execute it within a stored procedure with sp_executesql might increase the chance that some existing execution plan is reusaged and thus performance will not be hurt too much.
Do you really need this all-in-one approach?
These one might also be interesting to you
http://www.algonet.se/~sommar/arrays-in-sql.html
http://www.algonet.se/~sommar/dynamic_sql.html
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'd use a central procedure that takes the parameters and then calls one of 10 or so subprocedures that performs the search. Seems like a pain, but you set it up and changes are quick and you can resuse the subprocedures for other searches that may be canned.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
Depending on what exactly would generate the dynamic sql, I have become a firm believer in doing just that for most such queries.
Everything is "it depends", but assuming your tables are large and queries run long in comparison to compile time, stripping out the unnecessary garbage is very easy in a scripting language (even T-Sql) and then letting the compiler optimize a concise statement instead of one with tons of null checks and or's is almost always better.
There are good reasons for stored procedures vs. dynamic sql, but I think it has become one of those mantras that tend to be applied by reflex rather than by analysis.
-
I never grant anything to the base tables.
No user can execute any query against the base tables.
Only via SP or sometimes throu views.
The maintenance is much easier.
When the developers change the code without changing the number and the type of the parameters they don't have to reinstall the new client everywhere.
Bye
Gabor
Bye
Gabor -
On rereading the question I think I answered something you did not ask -- whether or not to use Dynamic SQL.
You are asking whether you are better off issuing the dynamic sql from the client or from the server (inside a SP) in terms of performance.
I am not certain, but I would expect them to be the same. If the proc is cached it is cached in either case, the SP's compiled plan only relates to code within it, not the dynamic sql it generates.
-
Let me add just this:
If RANGES are involved in the searches it might be GOOD to allow recompilation for the stored procedure! (this behavior is observed when the ranges vary in # of rows a lot)
My 2 cents
* Noel -
Just to add my two cents. I did this with a developer they had a search they dynamically built inside there application then sent to the server and we re-did it by sending it to a stored proc that had dynamic sql. we saw a huge improvement with the dynamic sql and as long as we used sp_executesql we were re-using our plan. I would say if possible try both and see what works best. Good luck
Pat Wright
Pat Wright
Dba
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply