Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    1

    Unanswered: speeding up store procedures using EXEC?

    Hello, can anyone offer any advice on this problem related to store procedures.

    The following 2 chunks of SQL illustrate the problem

    --1
    declare @lsFilt varchar(16)
    select @lsFilt = 'fil%ter'
    select * from sysobjects where name like @lsFilt

    --2
    declare @lsQuery varchar(128)
    select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
    exec (@lsQuery)

    When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.


    Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of

    create proc SP2Style
    @psFilter varchar(16)
    AS
    declare @lsQuery varchar(128)
    select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
    exec (@lsQuery)
    GO

    instead of

    create proc SP1Style
    @psFilter varchar(16)
    AS
    select * from sysobjects where name like @psFilter
    GO

    Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?

    thanks

    Paul
    Last edited by PTSQL; 05-26-04 at 13:46.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would have thought a clustered index seek would be faster.....

    I'll test it out
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I get both INDEX Seeks for both of yours...just that the predicate with the local variable does need to use a bookmark lookup...

    But my guess is because of the select *, not the predicate...

    But why the dynamic is only shoing index seek?

    I think it's lying to us because it doesn't know what it has...

    You sure it's faster?

    I found this bizarro though

    Code:
    select * from sysobjects where name like '%fil%'
    
    declare @lsFilt varchar(16)
    select @lsFilt = '%fil%'
    select * from sysobjects where name like @lsFilt
    Both should show a Index scan, no?

    The second comes out just like yours...index seek and bookmark?

    How? I thought that was impossible...

    How can it use an index with a leading wildcard? It can't

    That's why I think it's fibbing to us...

    Anyone?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •