how to improve the following query

Question

how to improve the following query

SSM-242970

Mr or Mrs. 500

Points: 547
More actions
August 21, 2008 at 3:57 pm

#211481

select
v.health_care_plan_id,
v.health_care_plan_name,
v.formulary_id,
v.drug_label_assoc_detail_id,
v.drug_label_id,
v.drug_detail_id,
v.drug_label_name,
v.dict_drug_form_desc,
v.dict_drug_strength_qnty_str,
v.dict_drug_strength_unit_str,
v.dict_drug_route_desc,
v.drug_label_type_code,
v.drug_label_assoc_detail_otc_flag,
v.drug_label_on_patent_brand_id,
v.formulary_drug_label_flag,
v.formulary_drug_label_assoc_detail_flag,
v.formulary_drug_label_coverage_exclusion_flag,
v.dict_formulary_display_code,
v.dict_formulary_display_desc,
v.dict_formulary_display_rank_nbr
from
v_formulary_status_drug_label_assoc_detail_3 v with (nolock)
Union
SELECT
h.health_care_plan_id,
h.health_care_plan_name,
v1.formulary_id,
v1.drug_label_assoc_detail_id,
v1.drug_label_id,
v1.drug_detail_id,
v1.drug_label_name,
v1.dict_drug_form_desc,
v1.dict_drug_strength_qnty_str,
v1.dict_drug_strength_unit_str,
v1.dict_drug_route_desc,
v1.drug_label_type_code,
v1.drug_label_assoc_detail_otc_flag,
v1.drug_label_on_patent_brand_id,
v1.formulary_drug_label_flag,
v1.formulary_drug_label_assoc_detail_flag,
v1.formulary_drug_label_coverage_exclusion_flag,
v1.dict_formulary_display_code,
v1.dict_formulary_display_desc,
v1.dict_formulary_display_rank_nbr
from
health_care_plan h with (nolock) inner join
v_formulary_status_drug_label_assoc_detail_2 v1 with (nolock) on h.formulary_id = v1.formulary_id
where not exists
(
select * from v_formulary_status_drug_label_assoc_detail_3 v3
where v3.health_care_plan_id=h.health_care_plan_id and
v3.health_care_plan_name=h.health_care_plan_name and
v3.drug_label_assoc_detail_id=v1.drug_label_assoc_detail_id and
v3.drug_label_id=v1.drug_label_id
)
thanks,
ssm

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

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

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 1

In the sub query following sub query , replace "select * " with " select top 1 1 " as it will not be useful with correlated subquery.

Thanks,

Amit Khanna

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

You might also try taking the NOT EXISTS sub-query and making it into a LEFT JOIN and looking for a NULL return. You'd need to test it, but in the past I've found a JOIN usually works better than a correlated query.

"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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

To simplify the request, with this data:

create table a (id int identity(1, 10) primary key clustered, dummy_col uniqueidentifier)

create table b (id int identity(1, 1) primary key clustered, dummy_col uniqueidentifier)

insert a (dummy_col) select newid()

go 10000

insert b (dummy_col) select newid()

go 100000

You want to do this:

select id, dummy_col

from a

union all

select id, dummy_col

from b

where not exists (select 1 from a where b.id = a.id)

Is this correct? If so, try it with a left join as follows:

select b.id, coalesce(a.dummy_col, b.dummy_col)

from b

left join a on b.id = a.id

Whether it performs better depends on your own environment. On my machine it performs slightly less logical I/O but uses slightly more CPU.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

SSM-242970 Mr or Mrs. 500 Points: 547 More actions · Answer 4

SSM-242970

Mr or Mrs. 500

Points: 547

August 22, 2008 at 12:29 pm

#862074

Thanks,I will try that out.

thanks,ssm