I have a query that takes 2:30 minutes when executed in Management Studio and just about 4 seconds when executed through the web application. Google takes me to the opposite case, but there is no evidence that describes this.
This only happens for @parameter = ''. In case that that @parameter is set to a value, execution time is normal.
The query is rather lengthy and has this structure:
;with scope as (Select ... WHERE Column1 = @parameter or @parameter = '' )
select * from (
select * from scope left outer join table1
union all
select * from scope left outer join table2
union all
select * from scope left outer join table3
union all
select * from scope left outer join table4
union all
select * from scope Where condition 1
union all
select * from scope Where condition 2)x
left outer join Table5
left outer join (select Column, row_number from Table6)a on row_number = 1
left outer join (select Column, row_number from Table7)a on row_number = 1
Different cached query plans? Either execution command is NOT IDENTICAL (whitespace, capitalisation, ANY DIFFERENCE) or there is something else being used to "index" the SSMS query plan. I can't remember what "unique characteristics" define a query plan, but I think it includes properties of the currently connected user that irrelevant - maybe "permissions", or LANGUAGE, or things like that.
Sounds like the Web Query is using an old, stale, but efficient! query plan and the SSMS one is using a new query plan. Either way, it would be good to sort it out because presumably there is the potential for the Web to use the slower query plan, currently favoured by SSMS query plan, in the future. It may be that the first query (which creates a cached query plan) is dependent on whether @parameter is a blank string, or not - AND if NOT then whether the value in @parameter, on that very first query, is highly-selective for an Index on Column1 - OR NOT!
I expect there is something on www.sommarskog.se which discusses the possible scenarios (if so I am sure Erland Sommarskog will have covered in more depth than pretty much anywhere else on the interweb )
Thank you for the information and the link. Very interesting. As always when I stumble over bad performance that indicates an issue in the execution plan, I realize how little I know about it. I would love to take a break and study that in more detail, but the daily work won't let me. After some restructurings the query suddenly executed fine on both ends.
)