Query Tuning Help

[Moebius01]

Ok, the non-sql expert in me just inherited a new "problem query" that was causing major load issues and taking quite a while to run. Basically it's a logon usage by interval report, showing the number of employees that logged on in each interval (currently 1 day). When run for small ranges (say up to 90 days or so) it's not too bad. But when pulled for a year's time, it slows to a crawl.

The first thing I noticed was it used cursors, so I took those out and made the following re-write. Currently, the logon and logoff times are not indexed in this table, which is another step I'm looking at, but in the meantime, can anyone suggest any ways that might tweak this up a bit? Removing the cursor took a year long run from 2:04 to about 1:25, but I'd really like to get it lower than that if at all possible.

DECLARE @date SMALLDATETIME
DECLARE @enddate SMALLDATETIME
DECLARE @sampleperiod INT
DECLARE @siteid VARCHAR(50)
DECLARE @EmpFilter NVARCHAR(4000)
DECLARE @ShowWeekends BIT

SET @date = '1/1/2006'
SET @enddate = '12/31/2006'
SET @sampleperiod = 24
SET @siteid = 'xyz'
SET @EmpFilter = 'SELECT EmpID FROM Employee'
SET @ShowWeekends = 0

SET NOCOUNT ON

CREATE TABLE #tmpEmps (EmpID INT NOT NULL)
INSERT INTO #tmpEmps EXEC (@EmpFilter)

CREATE TABLE #temp (countdate SMALLDATETIME NULL, usercount INT NULL)

WHILE @date < @enddate
BEGIN
INSERT INTO #temp (countdate) VALUES (@date)
SET @date = DATEADD(hh,@sampleperiod,@date)
END

IF @ShowWeekends = 1
BEGIN 
	SELECT countdate AS bucket, DATENAME(dw,countdate) AS dayname,
		(SELECT COUNT(DISTINCT lh.empid) FROM LogonHistory lh
		INNER JOIN #tmpEmps te ON te.empid = lh.empid
		WHERE LogonTime BETWEEN countdate AND DATEADD(hh,@sampleperiod,countdate) 
        	OR LogoffTime BETWEEN countdate AND DATEADD(hh,@sampleperiod,countdate) AND siteid = @siteid) AS usercount
	FROM #temp d
END
ELSE
BEGIN 
	SELECT countdate AS bucket, DATENAME(dw,countdate) AS dayname,
		(SELECT COUNT(DISTINCT lh.empid) FROM LogonHistory lh
		INNER JOIN #tmpEmps te ON te.empid = lh.empid
		WHERE LogonTime BETWEEN countdate AND DATEADD(hh,@sampleperiod,countdate) 
        	OR LogoffTime BETWEEN countdate AND DATEADD(hh,@sampleperiod,countdate) AND siteid = @siteid) AS usercount
	FROM #temp
	WHERE DATENAME(dw,countdate) NOT IN ('Saturday',
	'Sunday')	
END
DROP TABLE #temp
DROP TABLE #tmpEmps

 

[SQLSister]

WHERE DATENAME(dw,countdate) NOT IN ('Saturday',
'Sunday')
try using the clause witht the days to include - in tends to be a little bit faster than not in.

Also try table variables instead of temp tables. SOmetimes these are faster and sometimes #temp tables are faster, you should check to see which is best inthis case.

The distinct is hurting you as well. Is there a way to rewrite using a group by? Not sure if this would be faster but it's worth a try.

Adding the index should help as well.

A date lookup table with the day of the week for each date might help. Then you could join to that instead of making the query look up the day of every date in the table. An annual job to populate the next entire year could be scheduled to add the next year's dates sometime in Dec.



Questions about posting. See faq183-874

 

[Lamprey13]

Can you tell us anything about the table sizes and indexes?

If you look at the execution plan is there a particular hot spot?

You could change the IF..ELSE to just an IF and DELETE from #Temp to remove Weekends if needed and then you'd only have one query (which may or may not help the execution plan).

Also, hard to say without knowing anything about the data, but you might try removing the correlated sub-query and just writing it as a join.

 

[Moebius01]

The table in question has no indexing at present, and for example, a given site could have anywhere from a few hundred to a few hundred thousand users logging in and out of the system. Each session creates a record with login and logout times. As you can imagine, the table can get pretty huge.

I'm going to be starting discussions (I'm the new guy here, so....) on indexing the table, but figured I'd see what I can tweak out of the sproc first.

Also, is it possible to view an execution plan with temp tables? When I try I get an error about invalid object name #TmpEmps.

 

[Lamprey13]

I can in 2005. I can't remember if there is any issue with 2000, but I do not remember having one.

 

[Moebius01]

Ok, some more testing results show me that the biggest hangup is in the OR statement. If I run the query on either the LogonTime or LogoffTime individually, it returns in 8 seconds. Using the OR statement as above, it takes 1:20 to run.

Unfortunately, even though it's rare, in the case where a user crosses the interval, they need to appear on each.