Post reply

Select statement runs faster than equivalent SP.

  • June 9, 2005 at 12:43 pm

    #113927

    I have a query that returns ~ 80000 rows. It has a number of joins that join data in the order of 5000 rows to 30 rows.

    The query runs quickly (a second), but when as I turn it into a stored procedure and execute it it takes 40 seconds to run.

    The execution plans are different - but the SP's EP seems to be more efficient - using nest loop joins instead of hash matching. I think the query itself is a red herring but I really need to know why this might be happening.

    When the query becomes more complicated (more joins to large tables) the problem is multiplied (the query runs in under 10 seconds - the exec sp takes over 10 mins.

    Any ideas????

    Thanks

    Alex Weatherall

  • June 9, 2005 at 1:33 pm

    #564371

    Can you post the code of the sp??

    Also you might have to take a look at parameter sniffing, and today's article : Real World Query Plans

  • June 9, 2005 at 1:44 pm

    #564376

    If it is parameter sniffing then there is another very useful article on this by Ken Henderson at

    http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

    hth

    Dave

  • June 10, 2005 at 2:43 am

    #564510

    Hello,

    SQL server use serveral methods to optimize. I observed than the optimizer change when the table is large >1.000.000 records. SQL decide use the force (parallel scan)

    If you want same results in store procedure that query analyser I use the hits (you can find help in sql help FROM reference) lock and index and I compare the where. having, group clausule and try fix with the adecuate index. Can be very usefull have all fields of the index, minimun the firsts

    take out the select sentences of transaction.

    If you use transaction use nolock hit in the select sentences from the table that usually block (large tables, object with X with sp_lock,etc)

    use with care, It is very dificult, because you must know why exists the indexs if you can drop it, etc. 

    look if the query plan in sp is the same query analyser.

     

    Pedro

  • June 10, 2005 at 10:25 am

    #564681

    Hi there,

    Thanks this helped a bit. The dummy parameter solution from Ken did stop the problem in my sp. However this is also occuring in inline functions - which confused me as I thought all they did was expand into the surrounding query. e.g if I have function :

    CREATE FUNCTION foo (@bar int)
RETURNS TABLE
AS
RETURN (SELECT c.a, d.b FROM c INNER JOIN d on c.a = d.a WHERE d.bar = @bar)

    Then surely this :

    SELECT * FROM dbo.foo(1)

    is the equivalent to :

    SELECT * FROM (SELECT c.a, d.b FROM c INNER JOIN d on c.a = d.a WHERE d.bar = 1)

    so why do inline functions also suffer from parameter sniffing? By the way this function doesn't necessarily suffer from this problem. I was using it as an example of the inline expansion.

    Is there any other way to get round the problem other than the dummy parameter approach as this means changing the inline function to a multi line function - and you can't not supply the dummy parameter so it doesn't necessarily fix the parameter sniffing problem? How can I work out what part of my queries are likely to be causing sql server behave like this - are there any likely common causes? How about changes to index design - would that help? The 2 main base tables have in the order of 500,000 rows.

    Thanks,

    Alex

     

  • June 10, 2005 at 11:43 am

    #564707

    To name a few :

    use "force plan"

    use join hints.

    make 2 different functions (1 for each set of params that need different plan).

    The param trick you just learned.

    Run the function periodically with a good set of params so that the cached plan is always correct.

    You could try recompile if the recompile hit is smaller than the bad plan.

    Make sure the stats are up to date (expect that sql server will fail at keeping 'em up to date.)

    None of 'em is really pretty. The simplest one, I think, is the param trick. You know it'll always more with little effort.

  • June 15, 2005 at 6:48 am

    #565649

    If the Stored Proc was created sometimes back, it might be worth trying recompiling the Stored Proc. With that, it recreates the Execution Plan for the SP w.r.t current database Indexes, statistics and table size.

     

    regds/ramanuj


    🙂

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

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