| 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.surnameFROM [tbl_Logins] l INNER JOIN [tbl_Users] u ON l.userID = u.IDWHERE (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 MVPhttp://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?
HiYes 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 fieldthe way query is written it should have an index on Logindate to take advantage ofOne more thing, how many records it contain currently?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2012-01-31 : 05:30:45
|
| HiThere currently is not an index on [tbl_Logins].LoginDateThe 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, butWhy 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Date query optimization