SP taking extremely long to run (sp_prepexec)

[NervousRex]

There is one stored procedure that is giving me tons of grief. When I run it in Query Analyzer, it takes 6-10 secs to run. But when called through an application, it is taking 5+ mins.

For whatever reason, it is not being run directly when called through coldfusion, but through sp_prepexec instead Here is the entry from my Profiler Trace file. The duration was 429096.

declare @P1 int
set @P1=1
declare @P2 bit
set @P2=1
declare @P3 bit
set @P3=1
declare @P4 bit
set @P4=1
declare @P5 bit
set @P5=1
declare @P6 bit
set @P6=1
declare @P7 bit
set @P7=1
declare @P8 bit
set @P8=1
declare @P9 bit
set @P9=0
declare @P10 bit
set @P10=1
declare @P11 bit
set @P11=1
declare @P12 bit
set @P12=1
declare @P13 bit
set @P13=1
declare @P14 bit
set @P14=1
declare @P15 bit
set @P15=1
exec sp_prepexec @P1 output, N'@P1 int,@P2 datetime,@P3 bit OUTPUT,@P4 bit OUTPUT,@P5 bit OUTPUT,@P6 bit OUTPUT,@P7 bit OUTPUT,@P8 bit OUTPUT,@P9 bit OUTPUT,@P10 bit OUTPUT,@P11 bit OUTPUT,@P12 bit OUTPUT,@P13 bit OUTPUT,@P14 bit OUTPUT,@P15 bit OUTPUT,@P16 bit OUTPUT', N'EXEC usp_Appointment_Validation @P1, @P2, @P3 OUTPUT, @P4 OUTPUT, @P5 OUTPUT, @P6 OUTPUT, @P7 OUTPUT, @P8 OUTPUT, @P9 OUTPUT, @P10 OUTPUT, @P11 OUTPUT, @P12 OUTPUT, @P13 OUTPUT, @P14 OUTPUT, @P15 OUTPUT, @P16 OUTPUT ', 972489, 'May 30 2007  1:00:00:000PM', @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, @P12 output, @P13 output, @P14 output, @P15 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15

I also call through a .NET application, and it runs as normal (called directly)...not as fast as in analyzer, but not nearly as bad as when coldfusion calls it.

I appreciate any help that can be offered

Thanks

 

[NervousRex]

A little more info for someone to try helping me

More detailed info in the traces shows that I have a Sort Warning of Multi Pass Sort Type

This is the performance killer, but I do not know what I need to look at to get rid of it. It seems to be occuring randomly, is this a memory issue?

Please Help!

 

[piti]

I don't know if this is the case, but days/weeks ago I have been pointed to an article (sorry don't have the link anymore) where a guy had similar problem caused by type of join used - it was a hash join against nested loops, where from his app nested loops were used instead of hash joins, so he added sp_recompile to his sp to force a new query plan for this sp, because in cache was stored one with nested loops

 

[NervousRex]

That sounds like it could be on the right track

It is an intermitten issue, it will run fast for a few hours/days, take a nose dive and be slow...till i look into it again, run it in query analyzer (which probaly recompiles it) and will run fast for a bit again. The sort warnings seem to come and go. So to me it sounds like it chooses the wrong execution plan once and a while and that causes the sort issues. Now the questions is, how do I avoid from it doing that?

As far as the hash joins and nested loops talk, I am a bit clueless about that stuff.

The stored procedure is way to large to post, and the querys that cause sort warnings probaly wouldn't make any sense to anyone, but if need be, I will post them if someone would like to help.

 

[piti]

you do it by using the sp_recompile stored procedure or you can explicitly specify which join type to use - if this is the problem - using the OPTION option ;-) in SELECT statement

hash joins vs nested loops - i think there are (were?) 3 types of joins in sql server chosen according to the amount of data hit by the query, ...

 

[NervousRex]

I'm looking at the OPTION option now

I am not understanding the syntax

Syntax
[ OPTION ( < query_hint > [ ,...n ) ] 

< query_hint > ::= 
    {    { HASH | ORDER } GROUP 
    | { CONCAT | HASH | MERGE } UNION 
    | { LOOP | MERGE | HASH } JOIN 
    | FAST number_rows 
    | FORCE ORDER 
    | MAXDOP number 
    | ROBUST PLAN 
    | KEEP PLAN 
    | KEEPFIXED PLAN
    | EXPAND VIEWS 
    }

Two of the options for it I am intrested in trying...first being the join type and the second being the keep plan

How do i make use of them?

 

[piti]

specify e.g. OPTION (LOOP JOIN, KEEP PLAN) at the end of your SELECT statement(s)
how about the result using sp_recompile procedure?