April 18, 2011 at 8:51 am
Just tried it with single-quotes and it completes in 1 sec.
I'm not being argumentative - I just want to understand why he suggested it for my own benefit, that's all.
Again, I can get the query to complete very quickly if I specify the date 7 days ago. However, what I'd like is for the script to query the date 7 days ago programmatically, so that I don't have to modify the script every time I want to run it.
April 18, 2011 at 8:55 am
Double check the datatype of that INT date column. I reckon it's a text type, not an INT.
@ninja - good spot, mate - you saw this coming 🙂
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 9:01 am
Plan B, using Lowell's code:
declare @Last7Days CHAR(8)
SELECT @Last7Days = CAST(YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7) AS CHAR(8))
SELECT ...
WHERE table-name-removed >= @Last7Days
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 9:07 am
ChrisM@home (4/18/2011)
Plan B, using Lowell's code:declare @Last7Days CHAR(8)
SELECT @Last7Days = CAST(YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7) AS CHAR(8))
SELECT ...
WHERE table-name-removed >= @Last7Days
ARGH - still takes 2 mins+ 😀
April 18, 2011 at 9:16 am
Phineas Gage (4/18/2011)
ChrisM@home (4/18/2011)
Plan B, using Lowell's code:declare @Last7Days CHAR(8)
SELECT @Last7Days = CAST(YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7) AS CHAR(8))
SELECT ...
WHERE table-name-removed >= @Last7Days
ARGH - still takes 2 mins+ 😀
Can you post the CREATE TABLE script for this table? And the scripts for the indices which contain this column?
And the whole query?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 9:17 am
Post both query plans (fast and slow).
Can you also test at which point you start getting the bad plan (3,4,5,6 days??).
If the query is not going to be run very often or is extremely simple, you could consider WITH RECOMPILE at the proc level and then use local variables in the query. That should get you there.
April 18, 2011 at 3:14 pm
Phineas Gage (4/18/2011)
There are 3 non-clustered indexes on that table, 2 of which use the date column.
First ,you most likely don't need 2 indexes on the same column.
Second, if a column is used for range selection (like in your example) there must be clustered index on that column.
Make PK nonclustered and one of date indexes clustered and your problem will go away.
And then you may drop the second index on date column as it's not gonna be used by optimiser anyway.
_____________
Code for TallyGenerator
April 18, 2011 at 3:46 pm
Phineas Gage (4/18/2011)
There are 3 non-clustered indexes on that table, 2 of which use the date column.
By "use the date column"... is the date column the first column in these indexes, or is it just in there with other columns?
If it's just in there, try adding an index where that column is the first column in the index.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply