August 1, 2011 at 11:17 am
I have a query that uses multiple functions & 8 subqueries. When I run it in SSMS without showing the actual execution plan it runs in about 3 seconds. When I run it with it showing the actual execution plan it runs in about 42 seconds. I have never seen asking for the actual execution plan to add more than a few seconds to a query.
Any ideas on what will cause this to occur?
Thanks in advance for the help.
August 1, 2011 at 11:23 am
I've seen this but never by that much. The most I've seen is a few more seconds, not 10 fold. It's actaully showing the graph plan that takes more time (maybe a bug with video driver / card???).
Can we see the plan to see how complexe it is?
August 1, 2011 at 11:30 am
Sure, see attached.
I have worked with execution plans that are much more complex & they have not increased the execution time very much at all.
August 1, 2011 at 11:33 am
Ya nothing overly complicated over there.
Have you checked your machine's eventlog for hardware issues?
August 1, 2011 at 11:46 am
Yes, it does cause a query to run longer. To return an actual execution plan, SQL has to track things during query execution that it doesn't normally track. That means that the query execution (not just the displaying of the plan) takes additional time.
I've seen a <1 second query go to 30+ seconds because of the actual plan being returned
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 11:48 am
GilaMonster (8/1/2011)
Yes, it does cause a query to run longer. To return an actual execution plan, SQL has to track things during query execution that it doesn't normally track. I've seen a <1 second query go to 30+ seconds because of the actual plan being returned
:w00t:
really complexe query?
realllllly busy server???
August 1, 2011 at 11:48 am
Do you see the same behavior when running profiler with Showplan XML with stats profile?
August 1, 2011 at 11:50 am
Ninja's_RGR'us (8/1/2011)
GilaMonster (8/1/2011)
Yes, it does cause a query to run longer. To return an actual execution plan, SQL has to track things during query execution that it doesn't normally track. I've seen a <1 second query go to 30+ seconds because of the actual plan being returned:w00t:
really complexe query?
realllllly busy server???
My desktop, a relatively complex query, but nothing earth-shattering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 11:51 am
Reallllllly old broken down laptop or the new monster you just bought?
... pickup up my jaw off the floor and heading home 😉
August 1, 2011 at 11:58 am
Ninja's_RGR'us (8/1/2011)
Reallllllly old broken down laptop or the new monster you just bought?
The machine that I just replaced.
I dunno if you'd call a Core-2 Quad with 2GB RAM 'broken down'
I suspect in the OP's case it's the functions. If those are scalar functions and they're getting run for each row of the query SQL has to track the additional information for each execution of each function. That could get nasty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 12:35 pm
I just checked no hardware errors.
August 1, 2011 at 12:46 pm
No, in SQL Profiler it does not seem to slow the query down.
August 1, 2011 at 12:59 pm
GilaMonster (8/1/2011)
I suspect in the OP's case it's the functions. If those are scalar functions and they're getting run for each row of the query SQL has to track the additional information for each execution of each function. That could get nasty.
I tried replacing the functions with left joins & it did make a big difference. With Show actual Plan it dropped to 8 seconds. Why would the functions impact the execution plan? The execution plan for the functions is not included in the execution plan for the query.
Oh yea & the query only returns approx. 3200 rows out of approx. 15000 rows in the Inventory table. We are not talking about a lot of data.
The server is not busy at all, it only runs max of 15% CPU, very few I/O's.
August 1, 2011 at 1:03 pm
John H Davis (8/1/2011)
The execution plan for the functions is not included in the execution plan for the query.
No its not, but SQL still has to track the run-time information through each one. The plans for the functions are still generated, they don't get returned for management studio, but they're still all generated (and you can see them with the profiler actual execution plan event, which is Statistics XML)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 1:17 pm
set statistics time will show compile time and execution time of the query, just to give you a look on consumption.
btw did you notice
<StmtSimple StatementCompId="3" StatementEstRows="2781.75" StatementId="37656" StatementOptmLevel="FULL"
StatementOptmEarlyAbortReason="TimeOut"
StatementSubTreeCost="4.05891" StatementText="select * from vWRDINVENT --SELECT * FROM Inventory as Inv --WHERE NOT EXISTS (SELECT 1 FROM xInventoryExt xInv WHERE Inv.InvtID = xInv.InvtID) " StatementType="SELECT" QueryHash="0x690BD3A1F05F88BC" QueryPlanHash="0x10E62248E2DCA22E">
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply