September 14, 2009 at 10:35 am
I have a query that I am running which seems to run forever. The code is listed below:
SELECT a.nhs_number
,a.postcode_of_usual_residence + a.gender + CONVERT(varchar(10), a.date_of_birth, 103) AS unique_identifier
,prov_a.provider_name AS readmit_provider_name
,prov_b.provider_name
,a.provider_id
,a.pas_hospital_number AS readmit_pas_hospital_number
,b.pas_hospital_number
,a.admission_date AS readmitadmissiondate
,a.discharge_date AS readmitdischargedate
,b.admission_date
,b.discharge_date
,a.treatment_function_specialty AS Readmit_treatment_function_specialty
,b.treatment_function_specialty
,b.hospital_spell_number AS spell_number
,adm_type_a.admission_type
,a.val_event_id AS Readmit_val_event_id
,b.val_event_id
,a.age_on_admission
,a.total_cost
,'5C300' AS purchaser_id
FROM fact_apc a
INNER JOIN
fact_apc 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.fin_year = b.fin_year AND
a.provider_id = b.provider_id AND
a.treatment_function_specialty = b.treatment_function_specialty AND
a.admission_type = b.admission_type LEFT 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_apc_admission_type AS adm_type_a ON a.admission_type = adm_type_a.admission_type
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_apc_admission_type AS adm_type_b ON b.admission_type = adm_type_b.admission_type
WHERE a.fin_year = '0910' AND
(a.episode_id <> b.episode_id) AND
(a.admission_date > b.discharge_date)AND
(a.admission_date <= dateadd(day,14, b.discharge_date)) AND
(a.treatment_function_specialty NOT IN ('560','501','7%')) AND
(a.age_on_admission > 16) AND
(adm_type_a.admission_type = 'NELEM' OR adm_type_a.admission_type = 'NELO') AND
(ISNULL(a.total_cost,0)> 0 AND ISNULL(b.total_cost,0)> 0 )
I have tried to take a look at the execution plan but cannot really understand how to use it. I have attached a copy of that also. There is an inner join in the script where a table (fact_apc) joins on itself. I was thinking that that might be the reason why it is running slow but i have done a similar quey in the past with this table and have not encountered this execution time before.
Any help would be much appreciated.
September 14, 2009 at 12:24 pm
This is an estimated execution plan, not the actual execution plan, so there may be other issues that I can't see with this. The thing that jumps out at me is 9 table scans. That means if you have indexes on these tables, they're not getting used.
A big part of that is likely the functions you're using on the JOIN and WHERE criteria like this one:
CONVERT(varchar(10), a.date_of_birth, 103)
But then you have to take into account that you're combining columns in order to join the tables
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)
Between these two things, you're very unlikely to see index use and scanning an entire table is expensive. Scanning 9 out of 11 tables is VERY expensive.
You need to be able to do these comparisons without adding the columns together. If the functions you have defined above are true, then
a.postcode_of_usual_residence = b.postcode_of_usual_residence
AND
a.gender = b.gender
AND
a. date_of_birth = b.date_of_birth
Should be true as well and then you're more likely to see index use.
But, that begs the question, what do your structures look like? Where do you have the clustered index on these tables and are there any nonclustered indexes?
"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
September 14, 2009 at 1:25 pm
Thanks for that info. I will have post the actual execution plan tomorrow as I am not currently in the office.
Regards
sauce1979
September 17, 2009 at 4:31 am
Hi I have had a close look at the query and have founf that out of the 9 table scans many of them are not needed so i have not included the sql and consequently the query completes after 1min with the necessary output. Many thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply