Select statement runs faster than equivalent SP.

Question

Post reply

Select statement runs faster than equivalent SP.

alexweatherall

Mr or Mrs. 500

Points: 515
More actions
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

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 2

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

PEDRO ALEGRE CANSECO SSC Journeyman Points: 81 More actions · Answer 3

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

alexweatherall Mr or Mrs. 500 Points: 515 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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.

Ramanuj SSC Veteran Points: 278 More actions · Answer 6

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

🙂