1

I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.

Here is the query :

SELECT T0.pk 
    FROM   products T0 
    WHERE  (( ( T0.pk IN (SELECT T1.target 
                               FROM   catrel T1 
                               WHERE  ( T1.source = 87960962008461 ) 
                                      AND ( T1.type = 87961011815221 )) 
                AND T0.pk NOT IN (SELECT T2.target 
                                       FROM   varexc T2 
                                       WHERE  ( T2.source = 87960962008461 ) 
                                              AND ( 
                                      T2.type = 87961020990261 )) ) 
               OR ( T0.pk IN (SELECT T3.target 
                                   FROM   varinc T3 
                                   WHERE  ( T3.source = 87960962008461 ) 
                                          AND ( T3.type = 87961021066258 
                                              )) ) )  ) 
           AND ( T0.type IN ( 87961022300198, 87916102426706 ) ) 
    ORDER  BY T0.p_code 

I have no idea where to start debugging that. When run directly in the SQL databse, the response in instant :

live query stat

The query does appear in the "recent expensive queries" (only when run from another client)

PS: SQL Server does not recommend any additional indexes.

  • 6
    Did you happen to come across Slow in the Application, Fast in SSMS? – Erik Darling Jul 2 at 13:07
  • Yes but have only read the first couple chapters! Most (if not all) of the article talks about stored procedures which I thought does not concern me. I'll have a look again. – user184569 Jul 2 at 13:13
  • Are query plans different from each other? Look at the wait type column on sp_WhoIsActive when run query from application. – Yunus UYANIK Jul 16 at 12:21
1

The option PARAMETRIZATION was "forced" instead of "simple" Switching back to simple + disabling Parameter Sniffing solved the issue.

  • Turns out the Erland Sommarskog articles are pretty darn thorough! – Jacob H Jul 2 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.