January 17, 2012 at 5:05 am
Gianluca Sartori (1/13/2012)
GilaMonster (1/13/2012)
The optimiser's not allowed to take that log to optimise queries (about 20 sec is the longest I've heard of and that was view upon view upon view upon viewI've had 20 minutes once. Yes, twenty minutes.
It was a huge query on a EAV database (37 LEFT JOINs IIRC). Something else must have been wrong with the instance anyway, because that absurdly long compile time was not the only weird symptom. I cycled the instance and the query started behaving surprisingly fast for such a dumb DB model (it "only" took 1 minute to execute).
Once refactored the view to a PIVOT, the execution time dropped to 30 ms (or was it 300 ms?? I don't remember) and I was never able to make the 20 minutes compile time happen again, even using the original ugly code.
However, this edge case apart, I must have a 700 Kb .sql file lying around somewhere that consistently took over 1 minute to parse.
Would be interesting to know if there's a parse timeout that can be set or a maximum timeout that the optimizer can't go over.
Yikes. Something was messed up. We had an 86 table join that only took 3.5 minutes to compile.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 17, 2012 at 5:06 am
GilaMonster (1/13/2012)
azdzn (1/13/2012)
- if the plan is stable and using 'USE PLAN' has the same effect as stopping the optimiser from compiling on each runit still looks like a good solution to me 🙂
No, use plan does not have the same effect as stopping the compiler from compiling on each run. In fact, it may even increase compile time. The plan that comes out will be consistent every time, but since plan inconsistency is not the problem here and compile time is, this is unlikely to have the desired effect.
In the tests I've done, it pretty consistently increases compile time. I guess it might depend on the query, but I'm with you. I wouldn't suggest this as a method for reducing compile time.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply