November 12, 2018 at 9:03 pm
when running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?
Likes to play Chess
November 12, 2018 at 10:24 pm
VoldemarG - Monday, November 12, 2018 9:03 PMwhen running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?
It make a sight difference not some what you have, Can you post the code and plan.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 12, 2018 at 10:28 pm
VoldemarG - Monday, November 12, 2018 9:03 PMwhen running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?
Depending on the query, quite possible. Some people have seen queries run forever before they cancelled them.
November 13, 2018 at 7:35 am
muthukkumaran Kaliyamoorthy - Monday, November 12, 2018 10:24 PMVoldemarG - Monday, November 12, 2018 9:03 PMwhen running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?It make a sight difference not some what you have, Can you post the code and plan.
Well.. it is a query that within the cursor selects 50 000 times from the view that selects from the other view that selects from 5 other nested views...
We are OK with the query running 20 minutes. I was still thinking that I can improve it by cutting another 10 minutes but its not critical. May be the query plan is so huge that it really slows down the execution of this code so noticeably. I have attached the code but have no idea how to get rid of this cursor...
Likes to play Chess
November 13, 2018 at 7:41 am
VoldemarG - Tuesday, November 13, 2018 7:35 AMmuthukkumaran Kaliyamoorthy - Monday, November 12, 2018 10:24 PMVoldemarG - Monday, November 12, 2018 9:03 PMwhen running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?It make a sight difference not some what you have, Can you post the code and plan.
Well.. it is a query that within the cursor selects 50 000 times from the view that selects from the other view that selects from 5 other nested views...
We are OK with the query running 20 minutes. I was still thinking that I can improve it by cutting another 10 minutes but its not critical. May be the query plan is so huge that it really slows down the execution of this code so noticeably. I have attached the code but have no idea how to get rid of this cursor...
It's probably generating an execution plan for each of the 50,000 queries.
November 13, 2018 at 9:27 am
Jonathan AC Roberts - Tuesday, November 13, 2018 7:41 AMVoldemarG - Tuesday, November 13, 2018 7:35 AMmuthukkumaran Kaliyamoorthy - Monday, November 12, 2018 10:24 PMVoldemarG - Monday, November 12, 2018 9:03 PMwhen running a loing SQL Statement with Actual Plan On it takes 2 hours.
if run without Actual Exec Plan it takes 20 minutes.
Is it realistic that Execution Plan made SUCH a big difference?It make a sight difference not some what you have, Can you post the code and plan.
Well.. it is a query that within the cursor selects 50 000 times from the view that selects from the other view that selects from 5 other nested views...
We are OK with the query running 20 minutes. I was still thinking that I can improve it by cutting another 10 minutes but its not critical. May be the query plan is so huge that it really slows down the execution of this code so noticeably. I have attached the code but have no idea how to get rid of this cursor...It's probably generating an execution plan for each of the 50,000 queries.
Yes, this. It's rendering 50,000 execution plans and displaying them which is taking the time.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 13, 2018 at 11:30 am
is there any way to save the execution plan in the background (not on SSMS GUI) while the query is running, and then open the plan for review at a later time ?
Likes to play Chess
November 13, 2018 at 11:47 am
VoldemarG - Tuesday, November 13, 2018 11:30 AMis there any way to save the execution plan in the background (not on SSMS GUI) while the query is running, and then open the plan for review at a later time ?
Do you need to go through all 50,000 rows when performance testing?
November 13, 2018 at 12:01 pm
I looked through the code and it looks to me that you could eliminate the cursor if you reworked everything inside the cursor.
Also noticed that you made sure that in the event of a deadlock this set of queries will always win and that you are using read uncommitted transactions which means you could have issues with your data.
I would take a shot at reworking the code but I would need the DDL for the tables and functions used, sample data that at least provided about 10 rows of data for each school, as well as data for the calendar type tables, and of course there would need to be expected results based on the sample data. I would also need to do the work during the evenings as this wouldn't be a simple rewrite.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply