Stored Procedure vs direct execute SQL

G

Guest

Hello,
Is there any difference to between SLQ string in the code and call execute
query and call a stored procedure and execute the query that way concerning
speed, effectiveness, reliability, …?
Thanks,
Jim.
 
M

Marina

Stored procedures are compiled, so you should see improvement there. How
much improvement and how noticeable really all depends on a number of
factors.
 
R

Robbe Morris [C# MVP]

SQL Server caching of queries has reduced the overall performance
gain of procedures versus dynamic sql strings. Procedures
in most cases will still be faster.

They are also "far more" secure than dynamic sql strings.
 
K

Karl Seguin

I have to strongly disagree with the two other respondants. The real answer
is that it totally depends. To say that stored procedures are "far more
secure" is extremely misleading. By simply using parameterized values in
your dynamic SQL you've achieved high security. Conversly, by using execute
in your stored procedure it's not at all secure.

As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe queries
are often uncacheable. Additionally, client-side (ASP.Net) caching can
belittle this minor advantage. What's worse, the most efficient search
queries are typically written in one form or another of dynamic sql
(otherwise SQL Server can't take advantage of indexes).

Personally I'm a fan of stored procedures (big time), but blanket statements
are dangerous. Saying that stored procedures are secure dangerously risks
taking the responsability away from the developer. Additionally, different
solutions are better suited for different scenarios. As such, only someone
who doesn't subscribe to blanket statements can make the right decision.

Let's look at a simplified example:

create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types


select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1


In the above example we need to allow 0 to be passed in to mean any listing.
This will likely result in SQL Server being unable to use an index on the
ListingType column, thus resulting in less that optimal performance. To get
ideal performance, we'd need to if the code:

if @ListingType = 0 BEGIN

select listingId, listingName from Property
WHERE Status = 1

END ELSE BEGIN

select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1

END

Now you are getting somewhere...except that it'll get impossible to maintain
with the addition of just a few more search parameters (and query plan
caching will increasingly become less efficient).

We could use execute:

declare @sql varchar(1024)
set @sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET @sql = @sql + ' AND ListingType = ' + @ListingTypeId
END
exec(@sql)

Looky that, a stored procedure and a HUGE potential for SQL
injection...egads! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).

This simple, and common, example not only illustrates that stored procedures
aren't necessarily faster and just vulnerable, but that they can also be
harder to maintain.

In short, the correct answer is "it depends" and hopefully as you learn more
you'll learn to use the right solution at the right time :)

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
P

PB

I couldn't agree with you more about blanket statements... implying that
proper research and understanding is in order. But I do have a question
regarding your statement:

<< the most efficient search queries are typically written in one form or
another of dynamic sql (otherwise SQL Server can't take advantage of
indexes).>>

I don't understand this. You seem to be saying that SQL Server can use
indexes *only* when fed dynamic SQL. I don't think that's true. Can you
explain further? I guess it's the "one form or another" part that I don't
get. How many forms of dynamic SQL are there?

Thanks.
 
K

Karl Seguin

Teehee...I made my own blanket statement ;) but atleast I had the word
"typically" in there.

But I can explain what I was saying in more detail. I was specifically
refering to cases where some search parameters are optional, which in my
experience is almost always a requirement. Without using dynamic sql (I
typically use sp_executesql instead of building it in the DAL but that's
just implementation), you are forced to either write a lot of IF/ELSE (which
is totally unacceptable for maintenance), or you write statements like AND
(@SomeParameter IS NULL OR SomeColumn LIKE @SomeParameter)

Using the above approach is sweet because it lets you avoid huge if/elses
AND also avoids dynamic sql. However, SQL server couldn't take advantage of
any indexes on SomeColumn in the above case. Don't get me wrong, it isn't
necessarily a bad approach, simply one which might have an unknown
side-effects (indexes are largely ignored). With dynamic SQL you're query
would either contain the SomeColumn LIKE @SomeParameter or not (determined
at runtime)...it wouldn't check if @SomeParameter IS NULL and wouldn't need
to OR (which I believe is why an index can't be used).

I can't recommend this article enough if your specific question is about
searches:
http://www.sommarskog.se/dyn-search.html it's totally unbiased and
contains no blanket statements...it'll purely educate you and give you
information necessary to making the right choice.

Hope that cleared some of the muck I said..
Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
R

Robbe Morris [C# MVP]

He said "direct execute sql".

I took this to be executing an entirely dynamically
create sql string. Use of parameters would not
help in this scenario. You are correct in your
assessment when parameterized command objects
are used.

I also said that SQL Server's caching "reduced"
the overall performance gain as a general statement which
is true.

And, I did say the procedures were still faster
in most cases.


--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top