SQL Server Performance Forum – Threads Archive

union all view problem?

Hi, this is one that has been bugging us for ages. it seems to be slowing things down for us, not sure if there is a solution other than writing procedures around the problem. —————– Definitions: —————– view definition is: – base data view, 104 weeks data create view sl_base_data as select * from sl_1430 union all select * from sl_1429 union all .. .. .. select * from sl_1326 – data table view, 1 week of data sl_{time} (e.g. sl_1430) defined as: prod_id varchar geog_id varchar time_id int unit value – has one clustered index and a check constraint. clustered index ix_sl_1430 (prod_id, geog_id, time_id asc) check constraint ck_sl_1430 (time_id = {time} ) ———————————– end of definitions ———————————- so when I run the following the execution plan looks so: select * from sl_base_data where time_id = 1430 |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1430].[ix_sl_1430])) which is great. BUT when you substitute the time period with a variable it checks all the indexes like this: declare @time_id int set @time_id = 1430 select * from sl_base_data where time_id = @time_id |–Concatenation |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1370].[ix_sl_1370]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([sl_1370].[time_id])=[@weekid])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1371].[ix_sl_1371]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([sl_1371].[time_id])=[@weekid])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1372].[ix_sl_1372]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([sl_1372].[time_id])=[@weekid])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1373].[ix_sl_1373]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([sl_1373].[time_id])=[@weekid])) .. .. .. .. |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[SL_xxx].[dbo].[sl_1430].[ix_sl_1430]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([sl_1375].[time_id])=[@weekid])) my mind is boggled. Why does sql not know when using a variable when it did when using an actual value? thanks for your time. Dave

Optimizer doesn’t know time_id value to generate the query plan when you pass the time_id as parameter/variable…
So that sql optimizer goes through all table in that view…
MohammedU.
Moderator
SQL-Server-Performance.com

hi, is there anyway that I can tell the optimizer what time_id is, so it doesn’t have to go through all the tables in the view? thanks
Dave

Other way could be to determine the weektable based on timeid passed
and generate dynamic sql to get timeid records from specific table instead of view. I think this will be much quicker because view is of 108 tables and in this dynamic method you will scan just one table

yeah that’s what we have been doing, it’s just a pain in the backside to write that dynamic code all the time.

]]>