August 21, 2008 at 4:08 pm
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
August 21, 2008 at 5:37 pm
Right off the bat the UNION looks suspicious because v_formulary_status_drug_label_assoc_detail_3 is in the not exists part too. By itself UNION is going to remove duplicates.
What indexes do you have? Is there sample data? How many records are involved?
See Forum Etiquette: How to post data/code on a forum to get the best help
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2008 at 6:31 pm
This is basically what I want
All rows from v_formulary_status_drug_label_assoc_detail_3 and all rows from v_formulary_status_drug_label_assoc_detail_2 which do not exist in v_formulary_status_drug_label_assoc_detail_3.
August 22, 2008 at 3:04 am
I suspect the NOT EXISTS sub-query uses a view which contains at least one JOIN. This is not a good idea as JOINs in sub-queries tend to use NESTED LOOPS which can be very slow on large tables. Get rid of the view in the NOT EXISTS sub-query and replace it with the table names. Do not use JOINs but use nested EXISTS sub-queries instead.
Also, I suspect your UNION can be replaced with an UNION ALL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply