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 :
The query does appear in the "recent expensive queries" (only when run from another client)
PS: SQL Server does not recommend any additional indexes.