Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

adding line break changes execution plan Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 5:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:30 AM
Points: 193, Visits: 505
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

Post #1464573
Posted Tuesday, June 18, 2013 5:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:11 AM
Points: 530, Visits: 442
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.
Post #1464580
Posted Tuesday, June 18, 2013 7:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:30 AM
Points: 193, Visits: 505
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
Post #1464640
Posted Tuesday, June 18, 2013 7:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 1,932, Visits: 19,937
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
Post #1464647
Posted Tuesday, June 18, 2013 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:30 AM
Points: 193, Visits: 505
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
Post #1464683
Posted Tuesday, June 18, 2013 5:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
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)
Post #1464912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse