Click here to monitor SSC
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
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 522
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
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 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
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 522
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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 522
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.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 6490
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