adding line break changes execution plan

Question

adding line break changes execution plan

Edward-445599

Hall of Fame

Points: 3147
More actions
June 18, 2013 at 5:18 am

#295456

Hi ,
I am confused I had seen a rather long query in SQL profiler its all on one line and I ran it to capture the query plan.
For readability I added some line breaks and executed it again.. and the query plan had totally changed to a far more efficient plan.
I thought I was mad so I went back and tried it again and again.. and every time the query plan was different when line breaks where introduced.
I have no idea why this is happening? Any ideas?
Many thanks

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

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 1

Whenever you fire same query second time or more, it will be faster anyways as it will pick the result from cache and gives faster results. This may be the reason for this.

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 2

Bhaskar.Shetty (6/18/2013)
Whenever you fire same query second time or more, it will be faster anyways as it will pick the result from cache and gives faster results. This may be the reason for this.

Hi I am aware of that , sorry I didn't make myself clear, interchanging between the two queries , one with no line breaks and one with line breaks causes a different execution plan to fire

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Edward-445599 (6/18/2013)
Bhaskar.Shetty (6/18/2013)
Whenever you fire same query second time or more, it will be faster anyways as it will pick the result from cache and gives faster results. This may be the reason for this.
Hi I am aware of that , sorry I didn't make myself clear, interchanging between the two queries , one with no line breaks and one with line breaks causes a different execution plan to fire

care to share the queries and exec plans?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 4

J Livingston SQL (6/18/2013)
Edward-445599 (6/18/2013)
Bhaskar.Shetty (6/18/2013)
Whenever you fire same query second time or more, it will be faster anyways as it will pick the result from cache and gives faster results. This may be the reason for this.
Hi I am aware of that , sorry I didn't make myself clear, interchanging between the two queries , one with no line breaks and one with line breaks causes a different execution plan to fire
care to share the queries and exec plans?

The query's are large and would take a while to rename everything for privacy. But that's beside the point I am more interested what would be the reason for something like this to happen?

Does anyone know of anything which who cause this to happen

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

Remember, the query optimizer looks for a verbatimly equal query in the plan cache and will use that if it finds one. Your replacing CRs and LFs changes the query, hence it will look for a different plan, which if it doesn't exist will cause it to create a new one. And that leaves parameter sniffing out of the equation...

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]