SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


adding line break changes execution plan


adding line break changes execution plan

Author
Message
Edward-445599
Edward-445599
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 532
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
Bhaskar.Shetty
Bhaskar.Shetty
SSC Eights!
SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)

Group: General Forum Members
Points: 854 Visits: 509
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
Edward-445599
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 532
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
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5411 Visits: 35316
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
Edward-445599
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 532
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
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2428 Visits: 6494
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search