How to increase performance with DATE compare

Question

Post reply

How to increase performance with DATE compare

nguyennd

SSCommitted

Points: 1881
More actions
January 12, 2010 at 1:45 am

#215485

Hi all
This is my solution. I must select data from table with DATE2-DATE2 < 60 days.
I wrote this code but is very low performance. I can't add a column to precalculate when insert.
Please help me
DECLARE @data TABLE
(
DATE1 INT NOT NULL,
DATE2 INT NOT NULL,
INT NOT NULL
);
INSERT @data
VALUES (20100101,20100301,1),
(20100101,20100302,2),
(20100101,20100301,3),
(20100101,20100302,4),
(20100101,20100301,5),
(20100101,20100228,6),
(20100101,20100228,7),
(20100101,20100301,8),
(20100101,20100301,9),
(20100101,20100301,10);
SELECT * FROM @data
WHERE
DATEDIFF(day,CONVERT(CHAR(8),
DATE1 ,
103),
CONVERT(CHAR(8),
DATE2,
103)
) < 60;
http://unitech.vn

Viewing 11 posts - 1 through 10 (of 10 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The biggest problem is to have date values stored as integer.

You have to convert it via character into datetime format (e.g. the way you did). And that's slowing down performance.

The best way would be to store the data in datetime format combined with proper indexing.

Before trying to tune performace on a bad design you should get the design changed...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nguyennd SSCommitted Points: 1881 More actions · Answer 2

I can't change or add column in this DB because it isn't my DB. No more way to increase performance?

http://unitech.vn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's never going to perform well. The conversion on the columns will force index scans or table scans.

I strongly recommend that you push for changing the data types to DateTime. Once that's done, it's possible to redo the where clause so there's no functions on the columns and hence SQL can use indexes for this.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ligtorn Ten Centuries Points: 1107 More actions · Answer 4

If you can't modify the table, when there is not much you can do.

Are you allowed to create an indexed view on the table? This view could use the proper datatypes and have the proper indexes

nguyennd SSCommitted Points: 1881 More actions · Answer 5

Sorry, i can't create view, trigger, constrain or every thing except index.

http://unitech.vn

LutzM SSC Guru Points: 107049 More actions · Answer 6

Would you be allowed to copy the data into an additionally created table having correct table structure?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ligtorn Ten Centuries Points: 1107 More actions · Answer 7

If you use the temporary table as part of your solution, why doesn't that temp table have the right datatypes and indexes?

Are you copying data from another table into the temp table?

matzer SSCrazy Eights Points: 9727 More actions · Answer 8

You can speed up things a little by doing a 'preselect' on the raw integer values:

SELECT

*

FROM

DateTable

WHERE

DATE1 + 204 > DATE2

AND DATEDIFF(day, CONVERT(CHAR(8), DATE1, 103),

CONVERT(CHAR(8), DATE2, 103)) < 60;

I tested it on a 1 million row table with 20% of the rows satisfying the predicate. It gave speedups between 10 and 30%. Seems like short circuit evaluation is taking place in this case although that's undocumented.

Edit: changed constant 250 to 204, being 2 months of minimally 28 days + 4 (>= 60 days). It now runs almost twice as fast as the original query.

Peter

nguyennd SSCommitted Points: 1881 More actions · Answer 9

Peter Brinkhaus (1/12/2010)
You can speed up things a little by doing a 'preselect' on the raw integer values:
SELECT
*
FROM
DateTable
WHERE
DATE1 + 250 > DATE2
AND DATEDIFF(day, CONVERT(CHAR(8), DATE1, 103),
CONVERT(CHAR(8), DATE2, 103)) < 60;
I tested it on a 1 million row table with 20% of the rows satisfying the predicate. It gave speedups between 10 and 30%. Seems like short circuit evaluation is taking place in this case although that's undocumented.
Peter

Imazing solution but DATE1 = 20101231 and DATE2=20110101 are wrong, huhu.

@Ligtorn: this is my example database to post on here, Real database is same same.

http://unitech.vn

matzer SSCrazy Eights Points: 9727 More actions · Answer 10

Damn, this is definitely not my day! Bad testing. Sorry.

Peter