Stored Procedure vs direct execute SQL

Discussion in 'ASP .Net' started by =?Utf-8?B?SklNLkgu?=, May 27, 2005.

  1. 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.
    =?Utf-8?B?SklNLkgu?=, May 27, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?SklNLkgu?=

    Marina Guest

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

    "JIM.H." <> wrote in message
    news:...
    > 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.
    >
    Marina, May 27, 2005
    #2
    1. Advertising

  3. 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.

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



    "JIM.H." <> wrote in message
    news:...
    > 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.
    >
    Robbe Morris [C# MVP], May 27, 2005
    #3
  4. =?Utf-8?B?SklNLkgu?=

    clintonG Guest

    Read these for starters...

    http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23011&DisplayTab=Article
    http://www.codeproject.com/database/hkstoredproc.asp


    <%= Clinton Gallagher
    METROmilwaukee (sm) "A Regional Information Service"
    NET csgallagher AT metromilwaukee.com
    URL http://metromilwaukee.com/
    URL http://clintongallagher.metromilwaukee.com/




    "JIM.H." <> wrote in message
    news:...
    > 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.
    >
    clintonG, May 27, 2005
    #4
  5. =?Utf-8?B?SklNLkgu?=

    Karl Seguin Guest

    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!)
    "JIM.H." <> wrote in message
    news:...
    > 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.
    >
    Karl Seguin, May 27, 2005
    #5
  6. =?Utf-8?B?SklNLkgu?=

    PB Guest

    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.



    "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
    wrote in message news:...
    >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!)
    > "JIM.H." <> wrote in message
    > news:...
    >> 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.
    >>

    >
    >
    PB, May 27, 2005
    #6
  7. =?Utf-8?B?SklNLkgu?=

    Karl Seguin Guest

    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!)
    "PB" <> wrote in message
    news:...
    >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.
    >
    >
    >
    > "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
    > wrote in message news:...
    >>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!)
    >> "JIM.H." <> wrote in message
    >> news:...
    >>> 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.
    >>>

    >>
    >>

    >
    >
    Karl Seguin, May 27, 2005
    #7
  8. I think our understandings are different

    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



    "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
    wrote in message news:...
    >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!)
    > "JIM.H." <> wrote in message
    > news:...
    >> 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.
    >>

    >
    >
    Robbe Morris [C# MVP], May 27, 2005
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FireListen
    Replies:
    1
    Views:
    494
    James J. Foster
    Jun 27, 2003
  2. Jeff Thur
    Replies:
    2
    Views:
    1,029
    Guest
    Feb 7, 2005
  3. nicholas
    Replies:
    7
    Views:
    600
    John.Net
    Aug 3, 2005
  4. Replies:
    1
    Views:
    450
    Bjorn Abelli
    Oct 17, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,297
    Mike P
    Jun 19, 2006
Loading...

Share This Page