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 (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: General Forum Members
Points: 140800 Visits: 19665
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 (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230723 Visits: 25390
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 (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71445 Visits: 18796
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 (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)

Group: General Forum Members
Points: 127756 Visits: 8995
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 (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: General Forum Members
Points: 140800 Visits: 19665
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 (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91857 Visits: 10161
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 (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)

Group: General Forum Members
Points: 376650 Visits: 34643
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