September 8, 2009 at 8:01 am
I currently have a stored procedure that uses a view to do inserts in to another table. The sql for this view is shown below
ALTER VIEW [dbo].[cr_overlaping_op_att]
AS
SELECT CASE WHEN a.nhs_number = '' THEN b.nhs_number ELSE a.nhs_number END AS nhs_no, a.attendance_id, prov_a.provider_id,
prov_a.provider_name, a.date_of_attendance, spec_a.specialty_desc, a.consultant, cons_a.consultant_surname, ref_src_a.source_of_referral_desc,
a.total_actual_cost, a.pbr_flag, b.attendance_id AS overlap_att_id, prov_b.provider_name AS overlap_provider_name,
b.date_of_attendance AS overlap_attendance_date, spec_b.specialty_desc AS overlap_specialty, b.consultant AS overlap_consultant,
cons_b.consultant_surname AS overlap_consultant_surname, ref_src_b.source_of_referral_desc AS overlap_source_of_referral,
b.total_actual_cost AS overlap_cost, b.pbr_flag AS overlap_pbr_flag,
CASE WHEN a.first_attendance = 1 THEN 'New' WHEN a.first_attendance = 2 THEN 'Follow up' ELSE a.first_attendance END AS attendance_type,
CASE WHEN a.consultant = b.consultant AND a.provider_id <> b.provider_id AND a.first_attendance = 1 THEN 1 WHEN a.consultant = b.consultant AND
a.provider_id = b.provider_id AND a.first_attendance = 1 THEN 3 WHEN a.consultant <> b.consultant AND a.provider_id = b.provider_id AND
a.first_attendance = 1 THEN 4 WHEN a.consultant <> b.consultant AND a.provider_id <> b.provider_id AND
a.first_attendance = 1 THEN 2 WHEN a.consultant = b.consultant AND a.provider_id <> b.provider_id AND
a.first_attendance = 2 THEN 6 WHEN a.consultant = b.consultant AND a.provider_id = b.provider_id AND
a.first_attendance = 2 THEN 5 WHEN a.consultant <> b.consultant AND a.provider_id = b.provider_id AND
a.first_attendance = 2 THEN 7 WHEN a.consultant <> b.consultant AND a.provider_id <> b.provider_id AND
a.first_attendance = 2 THEN 8 END AS error_code, CASE WHEN a.consultant = b.consultant AND a.provider_id = b.provider_id AND
a.first_attendance = 1 AND a.source_of_referral = b.source_of_referral AND
ref_src_a.ref_type_2 = 'GP' THEN '2 Query with provider' ELSE '3 To watch' END AS severity_code, dgd.month_year_html AS month_year,
'5C300' AS purchaser_id, a.val_event_id, b.val_event_id AS overlap_val_event_id, a.fin_year
FROM dbo.fact_op AS a LEFT OUTER JOIN
dbo.dim_generic_provider AS prov_a ON a.provider_id = prov_a.provider_id LEFT OUTER JOIN
dbo.dim_generic_specialty AS spec_a ON a.treatment_function_specialty = spec_a.specialty_code LEFT OUTER JOIN
dbo.dim_generic_consultant AS cons_a ON a.consultant = cons_a.consultant_code LEFT OUTER JOIN
dbo.dim_op_ref_source AS ref_src_a ON a.source_of_referral = ref_src_a.source_of_referral_code LEFT OUTER JOIN
dbo.dim_generic_date AS dgd ON a.date_of_attendance = dgd.date_code INNER JOIN
dbo.fact_op AS b ON a.postcode_of_usual_residence + a.gender + CONVERT(varchar(10), a.date_of_birth, 103)
= b.postcode_of_usual_residence + b.gender + CONVERT(varchar(10), b.date_of_birth, 103) AND a.date_of_attendance = b.date_of_attendance AND
a.fin_year = b.fin_year AND a.dna_indicator IN ('5', '6') AND b.dna_indicator IN ('5', '6') AND a.attendance_id > b.attendance_id AND
a.first_attendance = b.first_attendance AND a.treatment_function_specialty = b.treatment_function_specialty AND
a.provider_id = b.provider_id LEFT OUTER JOIN
dbo.dim_generic_provider AS prov_b ON b.provider_id = prov_b.provider_id LEFT OUTER JOIN
dbo.dim_generic_specialty AS spec_b ON b.treatment_function_specialty = spec_b.specialty_code LEFT OUTER JOIN
dbo.dim_generic_consultant AS cons_b ON b.consultant = cons_b.consultant_code LEFT OUTER JOIN
dbo.dim_op_ref_source AS ref_src_b ON b.source_of_referral = ref_src_b.source_of_referral_code
WHERE (a.date_of_attendance >= '01-Apr-2007') AND
(CASE WHEN a.nhs_number = '' THEN 1 WHEN b.nhs_number = '' THEN 1 WHEN a.nhs_number = b.nhs_number THEN 1 ELSE 0 END = 1) AND
(a.total_actual_cost > 0) AND (b.total_actual_cost > 0)
I select the fin_year field to specify the dataset that i need to insert into another otable. There are 3 values for fin_year: 0708,0809 and 0910.
If I do a select statement and put a where clause specifing
where fin_year = '0809' the query works. When I say where fin_year = '0708'
it works. When I say where where fin_year = '0910' the query just continuosly runs. However if say where fin_year NOT IN ( '0809','0708') the query produces the right output i.e reords where fin_year = 0910. Whis is the query behaving like this? I was under the impression that using NOT IN was not very efficient for performance? Obviously I get the desired input but I would just like to knwo any reasons for future reference.
thanks
September 8, 2009 at 8:15 am
You should compare the execution plans for the troublesome queries.
(you can save them and post them is this forum thread if you want to)
Your issue must have something to do with statistics of distribution causing it to choose another execution plan !
btw:
- Why are you concatenating these columns in one of the on-clauses ?
a.postcode_of_usual_residence + a.gender
+ CONVERT(varchar(10), a.date_of_birth, 103) = b.postcode_of_usual_residence
+ b.gender + CONVERT(varchar(10), b.date_of_birth, 103)
Why not just use the columns individually and maybe have some profit of indexes for these ?
- Keep in mind you are using left joins, so all non-outer-left-columns are NULLable !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply