SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query - Performance between dates


Query - Performance between dates

Author
Message
jason 84805
jason 84805
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 41
Im running a query and it has numerous linked tables. I have entered a date parameter of one week (28/01/2018 to 03/02/2018) it took 12 minutes to complete. I then ran (28/01/2017 to 03/02/2018) it took 4 minutes.
Im in the UK and looking at the table the date field is DD/MM/YYYY. When I enter the dates in the query I have to enter the WHERE as (sqltbl.Datefield Between '2018/01/28' AND ''2018/02/03')
I cannot understand why running just one week can take 12 mins and one year can take 4 mins. This is also happening on a different query (but still using the same date field)
Any ideas please
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133558 Visits: 19392
Maybe because it's returning 52 times as many rows? Please post the query plan and the two actual execution plans.

John
jason 84805
jason 84805
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 41
Due to the nature of the database - I cant post the actual query. But essentially it's using a pass through query in MS Access directly to the server and written as
SET NOCOUNT ON SELECT field1, field2, field3, Date1 From sqlTBLone WHERE (field1 = 'something') and (Date1 Between '2018/01/28' AND ''2018/02/03')

There's loads more tables with joins, but it's simply written as in the above example

jason 84805
jason 84805
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 41
John Mitchell-245523 - Monday, February 5, 2018 8:29 AM
Maybe because it's returning 52 times as many rows? Please post the query plan and the two actual execution plans.

John


Due to the nature of the database - I cant post the actual query. But essentially it's using a pass through query in MS Access directly to the server and written as
SET NOCOUNT ON SELECT field1, field2, field3, Date1 From sqlTBLone WHERE (field1 = 'something') and (Date1 Between '2018/01/28' AND ''2018/02/03')

There's loads more tables with joins, but it's simply written as in the above example

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217653 Visits: 24940
jason 84805 - Monday, February 5, 2018 8:15 AM
Im running a query and it has numerous linked tables. I have entered a date parameter of one week (28/01/2018 to 03/02/2018) it took 12 minutes to complete. I then ran (28/01/2017 to 03/02/2018) it took 4 minutes.
Im in the UK and looking at the table the date field is DD/MM/YYYY. When I enter the dates in the query I have to enter the WHERE as (sqltbl.Datefield Between '2018/01/28' AND ''2018/02/03')
I cannot understand why running just one week can take 12 mins and one year can take 4 mins. This is also happening on a different query (but still using the same date field)
Any ideas please

I suggest that you compare the actual execution plans for the two queries and look for differences.
Are your statistics up to date?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
drew.allen
drew.allen
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65719 Visits: 17414
jason 84805 - Monday, February 5, 2018 8:51 AM
John Mitchell-245523 - Monday, February 5, 2018 8:29 AM
Maybe because it's returning 52 times as many rows? Please post the query plan and the two actual execution plans.

John


Due to the nature of the database - I cant post the actual query. But essentially it's using a pass through query in MS Access directly to the server and written as
SET NOCOUNT ON SELECT field1, field2, field3, Date1 From sqlTBLone WHERE (field1 = 'something') and (Date1 Between '2018/01/28' AND ''2018/02/03')

There's loads more tables with joins, but it's simply written as in the above example

Without the actual execution plans, it's pure guesswork. If you cannot post the actual execution plan, there's not a whole lot that we can do.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122366 Visits: 8990
I submit it is not guesswork (assuming no server load/blocking issues). It is what I call the "widely-varying-input" problem. Pay attention to that I am about to say, because it is a MAGIC BULLET for that class of queries (which is essentially ever report query ever written with start/end dates or a date comparison, and also situations where one "identifier" can get a few rows and another get 40% of all data, et al):

IF YOU HAVE WIDELY-VARYING INPUTS TO A QUERY (see above indicators) YOU ABSOLUTELY MUST ENSURE SAID QUERY HAS OPTION (RECOMPILE) ON IT!!!

You can't POSSIBLY want the same query plan on a query that has @StartDate and @EndDate when the first execution has yesterday and today and the next execution (using that stored query plan) has 19000101 and 99991231. The opposite order of first/second calls kills you to, just with a different plan.

The above thing alone has made me look like I am a magician at MANY MANY clients.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133558 Visits: 19392
TheSQLGuru - Monday, February 5, 2018 9:19 AM
I submit it is not guesswork (assuming no server load/blocking issues). It is what I call the "widely-varying-input" problem. Pay attention to that I am about to say, because it is a MAGIC BULLET for that class of queries (which is essentially ever report query ever written with start/end dates or a date comparison, and also situations where one "identifier" can get a few rows and another get 40% of all data, et al):

IF YOU HAVE WIDELY-VARYING INPUTS TO A QUERY (see above indicators) YOU ABSOLUTELY MUST ENSURE SAID QUERY HAS OPTION (RECOMPILE) ON IT!!!

You can't POSSIBLY want the same query plan on a query that has @StartDate and @EndDate when the first execution has yesterday and today and the next execution (using that stored query plan) has 19000101 and 99991231. The opposite order of first/second calls kills you to, just with a different plan.

The above thing alone has made me look like I am a magician at MANY MANY clients.

Since he's running it from an Access front end, it sounds like an ad hoc query and therefore he shouldn't be seeing plan reuse at all, should he (except maybe with the same arguments for the dates)?

Edit - of course the only way to know for certain is to look at the plans.

John

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85745 Visits: 9639
What is the underlying table clustered on? If it's clus on identity, you should seriously consider clustering instead by date first, esp. if you (almost) always look up by date, rather than ident. You can add the ident to the end of the clus key to guarantee a unique key.

That will reduce the time as much as possible across all queries when coming specifying a date.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)SSC Guru (357K reputation)

Group: General Forum Members
Points: 357887 Visits: 34408
Get a copy of SentryOne Plan Explorer. It's free. Anonymize the plans (it'll clean the query & everything) and then post those. Otherwise, everything everyone is saying is just random stabs in the dark. You can't get good assistance without providing us with information. With no information, it could be anything.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
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