How Can I remove Table Scan?

I have the following query and just cannot seem to remove the table scan on
Table2. I have posted the indices from all tables in the join. I think Ive
covered everything according to rule. but is it overkill?
select count(a.Vh_SummVeh_id)
from Table1 a (nolock),
Table2 b (nolock),
Table3 c (nolock),
Table4 d (nolock)
where a.VH_Group_ID = b.Vh_group_id
and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
and c.vh_value_id = b.vh_value_id
and c.Vh_band_low_id = a.Vh_band_low_id
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
sp_help Table1
nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
Vh_Rating_Class_ID, Vh_band_low_id
sp_help Table2
nonclustered, unique located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Vh_reg_area_id
nonclustered located on PRIMARY Vh_VhAll_group_id
nonclustered located on PRIMARY Vh_Motor_group_id
nonclustered located on PRIMARY Vh_mfg_yr_id
nonclustered located on PRIMARY Vh_value_cv_id
nonclustered located on PRIMARY Vh_category_id
nonclustered located on PRIMARY Vh_mfg_age_id
nonclustered located on PRIMARY Vh_group_id
nonclustered located on PRIMARY Vh_rating_class_id
nonclustered located on PRIMARY Vh_model_id
nonclustered located on PRIMARY Vh_make_id
nonclustered located on PRIMARY Vh_value_id
nonclustered located on PRIMARY Vh_reg_year_id
sp_help Table3
nonclustered, unique located on PRIMARY Vh_value_id
clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper,
Vh_value_id
nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_id
sp_help Table4
nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
Tr_seq_id
nonclustered located on PRIMARY Product_id
nonclustered located on PRIMARY Inception_date_id
nonclustered located on PRIMARY Client_ver_id
nonclustered located on PRIMARY Tr_sub_type_id
nonclustered located on PRIMARY Rated_driver_id
nonclustered located on PRIMARY f_Ren_Flag
nonclustered located on PRIMARY Cp_cover_class_ind_id
nonclustered located on PRIMARY cp_attribute_id
nonclustered located on PRIMARY Cp_gen_ind_id
nonclustered located on PRIMARY Policy_id
nonclustered located on PRIMARY dim_keys_id
nonclustered located on PRIMARY Po_tr_pay_method_id
nonclustered located on PRIMARY Vh_SummVeh_id
nonclustered located on PRIMARY Po_tr_status_id
nonclustered located on PRIMARY Po_tr_bus_type_id
nonclustered located on PRIMARY Po_rd_postcode_id
nonclustered located on PRIMARY Cover_id
nonclustered located on PRIMARY Cur_trn_dt
nonclustered located on PRIMARY Vehicle_id
nonclustered located on PRIMARY Cover_idWhat exactly is this test supposed to achieve:
and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
?
This one also makes no sense to me:
and isnull(d.Vh_SummVeh_id,0) <> 0
Try replacing these two with valid tests first.
E.g.:
and (b.vehicle_id = d.vehicle_id or b.vehicle_id is null) -- just guessing
here
and (d.Vh_SummVeh_id is null)
ML|||try
Updating statistics
--
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> I have the following query and just cannot seem to remove the table scan o
n
> Table2. I have posted the indices from all tables in the join. I think Ive
> covered everything according to rule. but is it overkill?
> select count(a.Vh_SummVeh_id)
> from Table1 a (nolock),
> Table2 b (nolock),
> Table3 c (nolock),
> Table4 d (nolock)
> where a.VH_Group_ID = b.Vh_group_id
> and a.Vh_Rating_Class_ID = b.Vh_Rating_Class_ID
> and c.vh_value_id = b.vh_value_id
> and c.Vh_band_low_id = a.Vh_band_low_id
> and COALESCE(b.vehicle_id, b.vehicle_id) = d.vehicle_id
> and isnull(d.Vh_SummVeh_id,0) <> 0 -- IS NULL
> sp_help Table1
> nonclustered located on PRIMARY VH_SummVeh_id, VH_Group_ID,
> Vh_Rating_Class_ID, Vh_band_low_id
> sp_help Table2
> nonclustered, unique located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Vh_reg_area_id
> nonclustered located on PRIMARY Vh_VhAll_group_id
> nonclustered located on PRIMARY Vh_Motor_group_id
> nonclustered located on PRIMARY Vh_mfg_yr_id
> nonclustered located on PRIMARY Vh_value_cv_id
> nonclustered located on PRIMARY Vh_category_id
> nonclustered located on PRIMARY Vh_mfg_age_id
> nonclustered located on PRIMARY Vh_group_id
> nonclustered located on PRIMARY Vh_rating_class_id
> nonclustered located on PRIMARY Vh_model_id
> nonclustered located on PRIMARY Vh_make_id
> nonclustered located on PRIMARY Vh_value_id
> nonclustered located on PRIMARY Vh_reg_year_id
> sp_help Table3
> nonclustered, unique located on PRIMARY Vh_value_id
> clustered, unique located on PRIMARY Vh_value_lower, Vh_value_upper
,
> Vh_value_id
> nonclustered located on PRIMARY Vh_value_band_id, Vh_band_low_
id
> sp_help Table4
> nonclustered located on PRIMARY Po_risk_detail_id, Effective_date_id,
> Tr_seq_id
> nonclustered located on PRIMARY Product_id
> nonclustered located on PRIMARY Inception_date_id
> nonclustered located on PRIMARY Client_ver_id
> nonclustered located on PRIMARY Tr_sub_type_id
> nonclustered located on PRIMARY Rated_driver_id
> nonclustered located on PRIMARY f_Ren_Flag
> nonclustered located on PRIMARY Cp_cover_class_ind_id
> nonclustered located on PRIMARY cp_attribute_id
> nonclustered located on PRIMARY Cp_gen_ind_id
> nonclustered located on PRIMARY Policy_id
> nonclustered located on PRIMARY dim_keys_id
> nonclustered located on PRIMARY Po_tr_pay_method_id
> nonclustered located on PRIMARY Vh_SummVeh_id
> nonclustered located on PRIMARY Po_tr_status_id
> nonclustered located on PRIMARY Po_tr_bus_type_id
> nonclustered located on PRIMARY Po_rd_postcode_id
> nonclustered located on PRIMARY Cover_id
> nonclustered located on PRIMARY Cur_trn_dt
> nonclustered located on PRIMARY Vehicle_id
> nonclustered located on PRIMARY Cover_id
>|||ps: i would prefer not to use index hint
also: http://techrepublic.com.com/5100-22_11-1050429.html
which explains the problem with counting nulls|||Have you tried any of my suggestions? How did they affect the execution plan
?
ML|||Thanks but statistics are updated,
the coalesce reduces the amount of disk IO by 5%.
The isNull gets away from a SQL "feature" [IS NOT NULL] that ensures the
number of IS NOT NULLs counted is correct(see thread 'Are Null counts
reliable' from yesterday).
So any other ideas on why when the selected column to update from is indexed
as well as the columns in the join, a table scan still exists? And how can I
overcome this?|||Hey ML, I have.
"and (d.Vh_SummVeh_id is null)" I cannot used because it does not give me
the correct number of ISNULL records.
The other uses a table scan also.|||there are 4million records in Table2, is it possible the optimizer thinks th
e
tablescan is the best way to run the query?
"ML" wrote:

> Have you tried any of my suggestions? How did they affect the execution pl
an?
>
> ML|||I have evaluated the performance on using a hint which drops the table scan
and creates a 95% bookmark. performance did not increase.
I think the optimizer may be right to do its table scan.
Thanks all round|||There's also no clustered index on Table4. Try making the one on the
vehicle_id column in Table4 a clustered index.
MLsql