March 29, 2007 at 4:04 am
Hi all,
I've a stored procedure, while executin with set statistics io on i'm getting...
Table '#ProducedHour___________________________________________0000000012B7'. Scan count 1, logical reads 424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#6DF65D3E'. Scan count 1, logical reads 21617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Attendance'. Scan count 1, logical reads 469, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Can u explain me what is worktable and #6DF65D3E which has more reads... is it possible to get rid of that or to reduce the reads..??
March 29, 2007 at 10:39 am
#6Df65D3E is probably a statistic added to one of the tables because an index is needed for the query you're running and that index isn't there. The worktables are usually generated for order by's & group by's as part of processing that data. It could also be a distinct. Again it's caused when an index that would be needed by the process is missing. You need to look at the actual plan to see where these objects are being created and it will show you the table & column that needs the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2007 at 10:46 am
Grant's explanation makes sense. It looks like you're using a temp table as well, so that will get created in tempdb along with any other intermediate tables that SQL Server needs.
Can you post the query that generated this?
March 30, 2007 at 12:58 am
Here is the query...
UPDATE #ProducedHour SET
ProducedHour = #ProducedHour.Units
FROM
attendance a
WHERE
#ProducedHour.courseMember_id = a.CourseMember_id AND a.startWeekCode = #ProducedHour.startWeekCode
AND datediff(d,StartDate,dateadd(d ,0 , dbo.getdatefromweekCode(#ProducedHour.startWeekCode)))=0
AND a.StatusCodemon IN (SELECT value FROM dbo.splittext('P,A',','))
March 30, 2007 at 6:51 am
From this, you should try adding an index to the temp table, [#ProducedHour]. The first one I'd try, and you will need to test anything you do, would be to add a clustered index on [courseMember_Id] & [startWeekCode]. Other options might work better. Lots of tests are the key.
One thing I'd look at for sure is whether or not you need the temp table at all. From this code fragment, it looks like you're modifying the data in the temp table. Usually this is done when people aren't sure how to do a single select statement so instead they load the data into temp tables and then modify them several times before selecting from them as the output from a procedure. As you're seeing, this can be very ineficient.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2007 at 1:58 am
#6DF65D3E is the type of object name given to a table variable. The most likely origin of that is the function dbo.splittext, which looks like a table-valued function.
It could also be the other function (getdatefromweekCode). Can't see from the update whether or not it has a table variable. will need the code of it for that.
You'll have to take a look at the code in the functions to see if the table variable can be optimised. Table variables can't have indexes (other than the pk) and can't have statistics, so they're often hard to optimise.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply