Whether to use or not a Stored Procedure

Question

Whether to use or not a Stored Procedure

kaliyappan_k

Valued Member

Points: 59
More actions
January 2, 2004 at 2:25 am

#160445

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 2

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

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

Ferguson SSCertifiable Points: 5837 More actions · Answer 3

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.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

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

Ferguson SSCertifiable Points: 5837 More actions · Answer 5

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.

noeld SSC Guru Points: 96590 More actions · Answer 6

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

pwright7776 SSC Enthusiast Points: 103 More actions · Answer 7

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