Date as INT - having trouble with comparison :(

Question

Post reply

Date as INT - having trouble with comparison :(

Phineas Gage

Ten Centuries

Points: 1002
More actions
April 18, 2011 at 2:59 am

#104061

Hi all,
We have a legacy application where date is stored as an INT in the format yyyymmdd (don't ask!)
I need to write a script that includes filtering all records (in a very large table) where date >= 7 days ago. I've tried the following:
WHERE table-name-removed >= cast(datepart(YEAR,getdate()-7) AS varchar(4)) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),cast(datepart(mm,getdate()-7) AS varchar(2))),2) + RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),cast(datepart(dd,getdate()-7) AS varchar(2))),2);
This works, but the script takes just over 2 minutes to run. If I change this to:
WHERE table-name-removed >= date-7-days-ago-in-the-format-yyyymmdd
, then the script runs in 1 second.
I'm having real trouble finding a way to optimize this script so that the date doen't have to be converted for each row.
Any ideas?
Thanks very much,
Phineas

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

To make the query SARGABLE you need to compare to an integer.

Something like:

WHERE table-name-removed >= YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7)

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 2

Thanks for your reply, Ken.

Your code worked (and is certainly a lot neater than my own attempt). However, the script doesn't run any faster and still takes just over 2 mins to complete.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I'm guessing there's not an index on the[table-name-removed] column yet;

that should speed things up considerably i bet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 4

Lowell (4/18/2011)
I'm guessing there's not an index on the[table-name-removed] column yet;
that should speed things up considerably i bet.

There are 3 non-clustered indexes on that table, 2 of which use the date column.

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok, with a lot of rows in the source table,isn't the CURRENT_TIMESTAMP variable still being evaluated on a per-row basis?

can you try this instead?

declare @Last7Days int

SELECT @Last7Days YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7)

SELECT ...

WHERE table-name-removed >= @Last7Days

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 6

Hi Lowell,

Hmmm...gave your suggestion a try, but the query is still taking 2 mins to complete. To make sure I'm not going mad, I again tried:

WHERE table-name-removed >= 20110411 (date 7 days ago)

, and sure enough it completes in 1 second.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Update stats with fullscan if possbile.

If stats are out of data the plan will be wrong (especially for that case).

Could also be parameter sniffing.

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 8

Ninja's_RGR'us (4/18/2011)
Update stats with fullscan if possbile.
If stats are out of data the plan will be wrong (especially for that case).
Could also be parameter sniffing.

Thanks very much - updating the stats was one of the first things I tried, but it didn't make a difference to the execution time.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

How long does it take to run this:

WHERE table-name-removed >= '20110411'

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 10

ChrisM@home (4/18/2011)
How long does it take to run this:
WHERE table-name-removed >= '20110411'

1 second...see comments above

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

Phineas Gage (4/18/2011)
ChrisM@home (4/18/2011)
How long does it take to run this:
WHERE table-name-removed >= '20110411'
1 second...see comments above

Did you include the single quotes around the number?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 12

No...why's that? The query completed fine with and without them.

Just to clarify - I can get the query to complete 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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Phineas Gage (4/18/2011)
No...why's that? The query completed fine with and without them.

He wants you to test out something. Can you just humor him for 1 second instead of wasting 10 minutes arguing on the forum???

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 14

Try it with the quotes.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]