Date query optimization - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Date query optimization

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-30 : 11:22:42

I have a stored procedure that finds user login dates, for an optionally selected period and for specific users. I found the most efficient way (rather than use a catch-all query) was to do it like this:


DECLARE @tbl_users TABLE (userID int)
' population of table variables omitted as it only uses 1% of query resources anyway...

DECLARE @tmp TABLE (
   ID int,
   loginDate datetime,
   userID int,
   name nvarchar(100),
   surname nvarchar(100)
)

INSERT INTO @tmp
   (
   ID,
   loginDate,
   userID,
   name,
   surname
   )
SELECT
   l.ID,
   l.loginDate,
   u.ID AS [userID],
   u.name,
   u.surname
FROM
   [tbl_Logins] l INNER JOIN
   [tbl_Users] u ON l.userID = u.ID
WHERE
   (l.userID IN (SELECT userID FROM @tbl_users))

IF @startDate IS NOT NULL
  DELETE FROM @tmp WHERE (loginDate < @startDate)

IF @endDate IS NOT NULL
  DELETE FROM @tmp WHERE (loginDate > @endDate)

SELECT * FROM @tmp

Within the Execution plan I see there are two table scans, one for each DELETE operation. These account for 10% of the query resources. I assume here that SQL is scanning the ENTIRE table to find the correct date range. Within [tbl_Logins] however the loginDate field and ID field increase in tandem (e.g. new login stored with new ID values as expected).

Is there a way therefore, to stop SQL scanning the entire table, and only searching the period in between the @startDate and @endDate parameter values (as all dates would be in order anyway)? Or am I just making too big a deal about this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:38:44

is there ab existing index on loginDate?

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

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-30 : 11:51:03

quote:
Originally posted by visakh16

is there ab existing index on loginDate?

Hi

Yes two - [tbl_Logins].[ID] is a clustered index, and [tbl_Logins].[userID] is a non-clustered index.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 12:42:10

nope...i was asking on logindate field

the way query is written it should have an index on Logindate to take advantage of
One more thing, how many records it contain currently?

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

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-31 : 05:30:45

Hi

There currently is not an index on [tbl_Logins].LoginDate
The table contains about 12,000 records currently, and this is growing by about 500 records per month.

Do you think I should create an index on [tbl_Logins].LoginDate? Would you recommend an index just on that column?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 06:43:05

I may be missing something, but
Why do you delete from the @tmp table that you created and populated in the begining, Is it possible to just populate it with the rows that don't need to be deleted?

Duane.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-31 : 07:02:29

quote:
Originally posted by ditch

Why do you delete from the @tmp table that you created and populated in the begining, Is it possible to just populate it with the rows that don't need to be deleted?

Originally the first query included the dates in the WHERE clause...

quote:

INSERT INTO @tmp
.....
WHERE
(l.userID IN (SELECT userID FROM @tbl_users)) AND
(l.loginDate BETWEEN @startDate AND @endDate)

but after some testing it emerged that the query was much faster if the dates were deleted from the @tmp table afterwards. I didn't expect that, but it was done multiple times, so I structured the query to delete from @tmp instead. I wondered whether that might be because SQL did a full table scan on the dates after the userID's had been located from the clustered index, which again relates to the original question...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 07:12:01

Ahhh Yes - I see.

any reason why this is not handled by a join instead of a subselect?
"WHERE
(l.userID IN (SELECT userID FROM @tbl_users))"

Duane.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-31 : 07:39:53

quote:
Originally posted by ditch

any reason why this is not handled by a join instead of a subselect?
"WHERE
(l.userID IN (SELECT userID FROM @tbl_users))"

Good question. I don't suppose there is a reason. Do you know if INNER JOINs are faster than subselects? I suppose I could go back and test again...

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-01-31 : 07:50:02

quote:
Originally posted by ditch

any reason why this is not handled by a join instead of a subselect?
"WHERE
(l.userID IN (SELECT userID FROM @tbl_users))"

Okay I've done some testing. JOIN is marginally (approx 20%) faster for a large number of users (5000), but slower for fewer users. The difference in actual execution time though is minimal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 13:22:30

quote:
Originally posted by R

quote:
Originally posted by ditch

any reason why this is not handled by a join instead of a subselect?
"WHERE
(l.userID IN (SELECT userID FROM @tbl_users))"

Okay I've done some testing. JOIN is marginally (approx 20%) faster for a large number of users (5000), but slower for fewer users. The difference in actual execution time though is minimal.

you can also try EXISTS instead of IN as an alternative

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources