About worktable

Question

About worktable

AKP

SSCommitted

Points: 1753
More actions
May 31, 2007 at 9:14 am

#116982

Hi Everyone,
I got a problem while running a query.
When I saw the IO statistics of a query, it shows huge scan count and logical reads in Table 'Worktable'.This query takes 20 seconds to execute, It only displays 12 records as result.
How can i work on that query ? how can i remove that worktable reads ?
This query takes data from a view and the view consists of joins and a ' Case...When...Then' condition. Can this case condition generate such worktable or any others ?
Help me ASAP

Thanks

AKP

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

To help we will need more information. If you could post the code for your query and the DDL for the table(s)/view(s) used and perhaps some sample data would go a long way to helping you.

AKP SSCommitted Points: 1753 More actions · Answer 2

view v_rpt:

SELECT dbo.r_boffice.r_path, dbo.r_boffice.d_id, dbo.r_boffice.r_date, dbo.r_boffice.r_type_id,

dbo.l_r_boffice.r_name, dbo.r_boffice.r_id, dbo.l_r_boffice.r_title, dbo.l_r_boffice.display,

dbo.l_r_boffice.l_id, dbo.l_r_boffice.c_id, dbo.div.ex_id, CONVERT(varchar(32),

dbo.r_boffice.r_date, 1) AS rp_date_display, dbo.r_boffice.r_time,

dbo.v_div_All.Div_Dep AS div, dbo.areas.a_id, dbo.areas.reg_id,

dbo.r_boffice.d_id AS o_div_id, dbo.x_roles.ro_id,

CASE WHEN x_roles.ro_id IN (65536, 536870912, 4194304,

268435456, 2097152, 16384, 32768, 1073741824, 4096) THEN 100 WHEN

(r_boffice.r_type_id BETWEEN 1 and 99)

AND x_roles.r_type = 'abc' THEN 100 ELSE 0 END

AS a_level

FROM dbo.r_boffice INNER JOIN

dbo.l_r_boffice ON dbo.r_boffice.r_type_id = dbo.l_r_boffice.r_type_id INNER JOIN

dbo.div ON dbo.r_boffice.d_id = dbo.div.d_id INNER JOIN

dbo.areas ON dbo.div.a_id = dbo.areas.a_id INNER JOIN

dbo.v_div_All ON dbo.div.d_id = dbo.v_div_All.d_id INNER JOIN

dbo.x_roles ON dbo.l_r_boffice.l_id = dbo.x_roles.l_id

WHERE dbo.x_roles.display = '1'

V_div_all:

SELECT TOP 100 PERCENT dbo.div.d_id, dbo.div.dion, dbo.dep.depart, dbo.div.dion AS Div_Dep,

dbo.div.H_ID, dbo.div.day, dbo.div.a_id, dbo.div.co_id

FROM dbo.div LEFT OUTER JOIN

dbo.dep ON dbo.div.dep_id = dbo.dep.dep_id

ORDER BY dbo.div.dion

This query takes 20 seconds to execute:

select distinct r_type_id,r_title from v_rpt WITH(NOLOCK) WHERE r_id ='524288'

AND a_level=100 ORDER BY r_title

AKP SSCommitted Points: 1753 More actions · Answer 3

This is the result of SET STATISTICS IO ON

(12 row(s) affected)

Table 'areas'. Scan count 275, logical reads 560, physical reads 0, read-ahead reads 0.

Table 'Worktable'. Scan count 384307, logical reads 384840, physical reads 0, read-ahead reads 0.

Table 'div'. Scan count 2, logical reads 5075, physical reads 0, read-ahead reads 0.

Table 'r_boffice'. Scan count 29, logical reads 8234, physical reads 0, read-ahead reads 0.

Table 'x_roles'. Scan count 29, logical reads 29, physical reads 0, read-ahead reads 0.

Table 'l_r_boffice'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Why are you using the view v_div_all in v_rpt? The only field you are using from that view is v_div_All.Div_Dep and this is the same as div.dion from the table dbo.div (which is used in both views). You should be able to remove the v-div_all from your v_rpt and still get all the same information you are currently getting. This will eliminate an additional query against dbo.div.

Also, you should get out of the habit of building views using SELECT TOP 100 PERCENT..ORDER BY.. as this will not work as expected when you move to SQL Server 2005. You should get in the habit of doing your ORDER BY when you select from the view.

AKP SSCommitted Points: 1753 More actions · Answer 5

Actually, other guy wrote those views, but I am working as query optimizer. Thanks for your kind suggestions.

I remove that and try, it works faster with same output .

THanks

AKP