January 12, 2010 at 1:45 am
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;
January 12, 2010 at 2:30 am
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...
January 12, 2010 at 2:35 am
I can't change or add column in this DB because it isn't my DB. No more way to increase performance?
January 12, 2010 at 2:46 am
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
January 12, 2010 at 3:40 am
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
January 12, 2010 at 4:06 am
Sorry, i can't create view, trigger, constrain or every thing except index.
January 12, 2010 at 4:11 am
Would you be allowed to copy the data into an additionally created table having correct table structure?
January 12, 2010 at 4:19 am
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?
January 12, 2010 at 4:44 am
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
January 12, 2010 at 5:59 am
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.
January 12, 2010 at 6:08 am
Damn, this is definitely not my day! Bad testing. Sorry.
Peter
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply