Date as Integer and Date Math/Where Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date as Integer and Date Math/Where Clause

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 02:14:52

Guys, I've posted something similar to this before, but I don't think I quite understood it that well. Understanding more of the problem, I think this may make more sense this time.

The application I'm working with unfortunately stores all dates as 8 digit integer (yyyymmdd or 20110801 etc)

So most of my queries are riddled with a lot of conversions to char then to datetime. I think I've gotten a good process for converting the number field to a date, but I've also been concerned about date math in the where clause. As I understand it, if you are not performing functions on a column in the where clause, only on a variable etc, then it shouldn't force a table scan. But when I run the following query, there is still a table scan on the select statement at the end. Is this avoidable? Much appreciated!

[CODE]
declare @checkdate date
set @checkdate='20110927'

declare @dates table (DateInt int)
insert into @dates (DateInt)
values
('20110801')
,('20110810')
,('20110811')
,('20110830')
,('20110901')

select *,
@checkdate as DateChecked,
cast(convert(char(8), dateadd(mm, datediff(mm, 0, @checkdate)-1, 0),112) as int) as FromDate,
cast(convert(char(8), dateadd(mm, DATEDIFF(mm, 0, @checkdate), 0)-1,112) as int) as ToDate

from @dates

where
dateint between cast(convert(char(8), dateadd(mm, datediff(mm, 0, @checkdate)-1, 0),112) as int) and
cast(convert(char(8), dateadd(mm, DATEDIFF(mm, 0, @checkdate), 0)-1,112) as int) --Using between because only have the datepart

(5 row(s) affected)

(1 row(s) affected)
DateInt DateChecked FromDate ToDate
----------- ----------- ----------- -----------
20110801 2011-09-27 20110801 20110831
20110810 2011-09-27 20110801 20110831
20110811 2011-09-27 20110801 20110831
20110830 2011-09-27 20110801 20110831

(4 row(s) affected)

(1 row(s) affected)
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 06:01:09

what are indexes you're currently having on the table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 09:37:03

Oh, good question. First, it's not my database and I am not supposed to change the table structure in the database. But the table referenced looks something like this:

[CODE]
CREATE TABLE Reservations
(
R_Id int not null
,DateInt int not null --8 digit year in yyyymmdd format
,AgencyId int null
,CustomerId int not null
,ReservationType int not null
,--about 100 more columns
,CONSTRAINT pk_Reservation PRIMARY KEY (R_Id)
)
go

CREATE NONCLUSTERED INDEX idx_Date ON Reservations (DateInt)
go
CREATE NONCLUSTERED INDEX idx_Customer ON Reservations (customerid)
go
[/CODE]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:10:58

Difficult (for me) to say why you are getting a scan from your query. When I examine the query plan of the following example , I am seeing the nonclustered index (seek) being used;

declare @checkdate date
set @checkdate='20110927'

create table #dates(DateInt int)
insert into #dates (DateInt)
values
 ('20110801')
,('20110810')
,('20110811')
,('20110830')
,('20110901')

CREATE NONCLUSTERED INDEX idx_Date ON #dates (DateInt)

select *, 
@checkdate as DateChecked,  
cast(convert(char(8), dateadd(mm, datediff(mm, 0, @checkdate)-1, 0),112) as int) as FromDate, 
cast(convert(char(8), dateadd(mm, DATEDIFF(mm, 0, @checkdate), 0)-1,112) as int) as ToDate
from #dates

where 
dateint between cast(convert(char(8), dateadd(mm, datediff(mm, 0, @checkdate)-1, 0),112) as int) and
cast(convert(char(8), dateadd(mm, DATEDIFF(mm, 0, @checkdate), 0)-1,112) as int)  --Using between because only have the datepart

drop table #dates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 10:19:16

there are other factors also like amount of data in table, selectivity etc. so if optimiser feel like its more easier to scan the whole table rather than index table for non clustered it will cause a table scan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:20:09

Can you comment on any Index Fragmentation Issues or does the DBA have any Index Maintenance Plans in place?

What does DBCC SHOWCONTIG show for the Reservations Table?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:23:15

quote:
Originally posted by visakh16

there are other factors also like amount of data in table, selectivity etc. so if optimiser feel like its more easier to scan the whole table rather than index table for non clustered it will cause a table scan

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Good points. Particularly the low selectivity potential.

Thanks for adding that.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 10:32:51

@ehorn, I'm not sure what the index plans are now. I used to be involved with the maintenance until the database server was virtualized. I'll run DBCC SHOWCONTIG and get back to you on that. The table has about 2 million records, large for our database, but I realize that there are much larger tables in the database world. I have to check the actual table DDL later when I get in, but I do believe there are probably about 8-9 more nonclustered indexes on the table as well.

However, moving to a more general question, do you believe my where clause with the convert and date functions on the @checkdate rather than placing them on a column in the table avoid a forced table scan? Or is there a better way to go about it given the variables I presented? For example, I used to do the dateadd on the DateInt itself like this:

Where dateadd(mm, datediff(mm, 0, @checkdate)-1, 0) Between 1 and 3
etc (don't have SQL Server open, so might have this backwards)

I'm guessing my current construct is better than the one I just wrote out, but am also very interested to know if there is a better technique for doing date math in a where clause.

THanks!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:55:25

You are only performing functions on the local scalar value, not the table itself. This is essentially no different that comparing a column to a local variable. That being said, I do not believe that your filter (as it is here) is causing the scan.

I have seen this behavior before though and (all things being equal) visakh16 is correct. Sometimes the engine chooses what it sees as the most optimum execution plan. I have seen times when one can trick the optimizer to force a seek by adding another filter condition. Often one which is not relevant but yet causes the optimizer to use the seek. To this day, the optimizer is a bit black box (at least to me).

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 12:09:42

Thanks ehorn, that's good to know. For some reason, constructing the scalar variable with the dateadd/datediff functions never seems natural to me, I always think first to do the math on the column. Trying to break the habit and make sure I'm replacing it with a more sound approach.

I have some ideas on how to avoid the table scan in this table but it will probably take me a while to figure it out :)

Thanks ehorn and vis, I appreciate the advice!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 12:39:47

yvw,

Best wishes.

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 12:47:44

Move the calculation out of the query and see if that changes the query plan? (maybe that was what you guys were saying anyway??)


SELECT @intStartDate = cast(convert(char(8), dateadd(mm, datediff(mm, 0, @checkdate)-1, 0),112) as int),
       @intStopDate = cast(convert(char(8), dateadd(mm, DATEDIFF(mm, 0, @checkdate), 0)-1,112) as int)
select *, 
    @checkdate as DateChecked,  
    @intStartDate as FromDate, 
    @intStopDate as ToDate
from #dates
where dateint between @intStartDate and @intStopDate

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 13:01:15

I think it was that the fact that since I was calling "*" in the SELECT * portion, all the columns that have indexes were being called. There are 12 different indexes on the table. When I narrow the search down to the primary key only, it runs am index seek (nonclustered) Yet another reason to not use "*" :)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 13:02:16

@Kristen, was doing that as you were writing :) The performance didn't change, but it's definitely easier to read!

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 18:07:11

"The performance didn't change"

Good to know that the optimiser is smarter than the both of us!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources