November 15, 2011 at 5:49 am
Hi
I run the following statements as part of a stored procedure. The output is approx 300,000 rows of data. My problem is we are delivering the data via SSRS and the execution speed is slow. Can anyone think of any ways I could speed it up or make the query more efficient please?
Any help would be gratefully received.
Code is as follows:
USE [ABI_Reports]
GO
/****** Object: StoredProcedure [QIPP].[usp_QIPP_APC_ALL] Script Date: 11/15/2011 11:00:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***************************************************************************************************
Schema:QIPP (QIPP Indicators)
Name:[usp_QIPP_APC_ALL]
Author:Kayode Aliu
Creation Date:01/12/2010
Status:Test
Purpose:This proc extracts data for ALL APC QIPP report
Indicator desc:Admitted Paient Care indicators
SEM Type:na
Script Updates:
*******************************************************************************************************/
ALTER PROCEDURE [QIPP].[usp_QIPP_APC_ALL]
AS
BEGIN
set nocount on
truncate table QIPP.APC_ALL
insert into QIPP.APC_ALL
select
case
when month(EndDate_ConsultantEpisode) < 4 then cast(year(EndDate_ConsultantEpisode)-1 as varchar) + '/' + cast(year(EndDate_ConsultantEpisode) as varchar)
else cast(year(EndDate_ConsultantEpisode) as varchar) + '/' + cast(year(EndDate_ConsultantEpisode)+1 as varchar)
end as Financial_Year,
case
when month(EndDate_ConsultantEpisode) between 4 and 6 then 'Q1'
when month(EndDate_ConsultantEpisode) between 7 and 9 then 'Q2'
when month(EndDate_ConsultantEpisode) between 10 and 12 then 'Q3'
when month(EndDate_ConsultantEpisode) between 1 and 3 then 'Q4'
end as [Quarter],
case
when month(EndDate_ConsultantEpisode) =1 then 'Jan'
when month(EndDate_ConsultantEpisode) =2 then 'Feb'
when month(EndDate_ConsultantEpisode) =3 then 'Mar'
when month(EndDate_ConsultantEpisode) =4 then 'Apr'
when month(EndDate_ConsultantEpisode) =5 then 'May'
when month(EndDate_ConsultantEpisode) =6 then 'Jun'
when month(EndDate_ConsultantEpisode) =7 then 'Jul'
when month(EndDate_ConsultantEpisode) =8 then 'Aug'
when month(EndDate_ConsultantEpisode) =9 then 'Sep'
when month(EndDate_ConsultantEpisode) =10 then 'Oct'
when month(EndDate_ConsultantEpisode) =11 then 'Nov'
when month(EndDate_ConsultantEpisode) =12 then 'Dec'
end as [Month],
month(EndDate_ConsultantEpisode) as MonthNum,
case
when left(AIMTC_PCTCOMML,3) in ('5JF','5JG','5QJ') then '5QJ'
else left(AIMTC_PCTCOMML,3)
end as AIMTC_PCTCOMML,
case
when AIMTC_Type in (1,6) then 'Non elective'
when AIMTC_Type =2 then 'Elective IP'
when AIMTC_Type =3 then 'Elective DC'
end as AIMTC_Type,
case left(AIMTC_OrganisationCode_CodeOfProvider,3)
when 'RA7' then 'UHB'
when 'RVJ' then 'NBT'
when 'RD1' then 'RUH'
when 'RA3' then 'WAHT'
when 'NTC' then 'UKSH'
else 'Other'
end AIMTC_OrganisationCode_CodeOfProvider,
--isnull(SPECIAL.[SpecialityGroupDescription_All],'OTHER') as [MainSpecialtyName],
case
when left(AIMTC_OrganisationCode_CodeOfProvider,3)='RVJ' and [vw_APC_SEM_001].[TreatmentFunctionCode] in('130','160','430') then SPECIAL.SpecialityGroupDescription_NBT
else isnull(SPECIAL.[SpecialityGroupDescription_All],'OTHER')
end [MainSpecialtyName],
SUM(
CASE
WHEN
((left(AIMTC_OrganisationCode_CodeOfProvider,1)<>'5' and left(AIMTC_PCTCOMML,3)<>'5FL') or
(left(AIMTC_SiteCode_ofTreatment1,5) not in ('RN3C1','RN332','RN313','RN3C5','RN3C4','RN330','RN334','RN3C2','RN3C3','RN333') AND left(AIMTC_PCTCOMML,3)='5FL'))
and AIMTC_Type in ('2','3')
and [vw_APC_SEM_001].[TreatmentFunctionCode] not in('424','501')
and left([vw_APC_SEM_001].[TreatmentFunctionCode],1) <> 7 then 1
ELSE 0
end) AS INDICATORV3,
SUM(
CASE
WHEN
((left(AIMTC_OrganisationCode_CodeOfProvider,1)<>'5' and left(AIMTC_PCTCOMML,3)<>'5FL') or
(left(AIMTC_SiteCode_ofTreatment1,5) not in ('RN3C1','RN332','RN313','RN3C5','RN3C4','RN330','RN334','RN3C2','RN3C3','RN333') AND left(AIMTC_PCTCOMML,3)='5FL'))
and AIMTC_Type in ('1', '2','3','6')
and AdmissionMethod_HospitalProviderSpell in ('21','22','23','24','28')
and left([DiagnosisPrimary_ICD],3) in ('I20','I21','J45','J46','F31','J40','J41','J42','J43',
'J44','E10','E11','E12','E13','E14','F00','F01','F02','F03','F32','G40','S02', 'S12', 'S22',
'S42', 'S52', 'S72', 'S82', 'S92', 'T02', 'T08', 'T10','T12','I50','F20','G45', 'I61', 'I63', 'I64') then 1
ELSE 0
end) AS INDICATORV4,
SUM(
CASE
WHEN
((left(AIMTC_OrganisationCode_CodeOfProvider,1)<>'5' and left(AIMTC_PCTCOMML,3)<>'5FL') or
(left(AIMTC_SiteCode_ofTreatment1,5) not in ('RN3C1','RN332','RN313','RN3C5','RN3C4','RN330','RN334','RN3C2','RN3C3','RN333') AND left(AIMTC_PCTCOMML,3)='5FL'))
and AIMTC_Type in ('1','2','3','6')
and [vw_APC_SEM_001].[TreatmentFunctionCode] not in ('424','501')
and left ([vw_APC_SEM_001].[TreatmentFunctionCode],1)<>7 then 1
ELSE 0
end) AS INDICATORV5,
SUM(
CASE
WHEN
AIMTC_Type in ('1','2','3','6')
and [vw_APC_SEM_001].[TreatmentFunctionCode] not in('424','501')
and left([vw_APC_SEM_001].[TreatmentFunctionCode],1) <> 7
and ((left(AIMTC_OrganisationCode_CodeOfProvider,1)<>'5' and left(AIMTC_PCTCOMML,3)<>'5FL') or
(left(AIMTC_SiteCode_ofTreatment1,5) not in ('RN3C1','RN332','RN313','RN3C5','RN3C4','RN330','RN334','RN3C2','RN3C3','RN333') AND left(AIMTC_PCTCOMML,3)='5FL'))
THEN 1
ELSE 0
end) AS INDICATORV6
FROM
[ABI].[dbo].[vw_APC_SEM_001] with (nolock)
LEFT JOIN [QIPP].[HRG4Lookup] HRG4 with (nolock)--left b'cos lookup tbl may be incomplete
ON HRG4.[HRG]=[AIMTC_Current_FCE_HRG]
LEFT JOIN abi.[lard].[tbl_Referral_Speciality_Groupings] SPECIAL with (nolock)--same as above
ON SPECIAL.[TreatmentFunctionCode]=[MainSpecialtyCode]
LEFT JOIN abi.[lard].[tbl_Referral_Speciality_Groupings] TF with (nolock)--same as above
ON TF.[TreatmentFunctionCode]=[vw_APC_SEM_001].[TreatmentFunctionCode]
--LEFT JOIN [QIPP].[PCIS_GP_Practice] GP with (nolock)--same as above
--ON GP.[PRACTICE CODE]=[PracticeCodeofRegisteredGP] and GP.[ENDDATE] IS NULL
where
AIMTC_SEQ=1
and AIMTC_OrganisationCode_CodeOfProvider <> 'RVN00'
and EndDate_ConsultantEpisode >= '01 apr 2009'
and left(AIMTC_PCTCOMML,3) in ('5JF','5JG','5QJ','5M8','5A3','5FL')
and AIMTC_Type in ('1', '2','3','6')
group by
EndDate_ConsultantEpisode,
case
when left(AIMTC_PCTCOMML,3) in ('5JF','5JG','5QJ') then '5QJ'
else left(AIMTC_PCTCOMML,3)
end,
case
when AIMTC_Type in (1,6) then 'Non elective'
when AIMTC_Type =2 then 'Elective IP'
when AIMTC_Type =3 then 'Elective DC'
end,
SPECIAL.[SpecialityGroupDescription_All],
AIMTC_OrganisationCode_CodeOfProvider,
[vw_APC_SEM_001].[TreatmentFunctionCode],
SPECIAL.SpecialityGroupDescription_NBT,
AIMTC_SiteCode_ofTreatment1,
AIMTC_PCTCOMML
--Quarter
Update[QIPP].APC_ALL
set[Quarter] =[Quarter]+'TD'
where
[Quarter]=case
when (select
month(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
having
right(Financial_Year,4)=max(year(isnull(EndDate_ConsultantEpisode,0)))
and month(max(isnull(EndDate_ConsultantEpisode,0)))<4
) in (1,2) then 'Q4'
else ''
end
OR
[Quarter]=case
when (select
month(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
having
left(Financial_Year,4)=max(year(isnull(EndDate_ConsultantEpisode,0)))
and month(max(isnull(EndDate_ConsultantEpisode,0)))>3
) in (10,11) then 'Q3'
when (select
month(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
having
left(Financial_Year,4)=max(year(isnull(EndDate_ConsultantEpisode,0)))
and month(max(isnull(EndDate_ConsultantEpisode,0)))>3
) in (7,8) then 'Q2'
when (select
month(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
having
left(Financial_Year,4)=max(year(isnull(EndDate_ConsultantEpisode,0)))
and month(max(isnull(EndDate_ConsultantEpisode,0)))>3
) in (4,5) then 'Q1'
else ''
end
-- Financial Year
Update[QIPP].APC_ALL
set Financial_Year = [Financial_Year]+'YTD'
where
right(Financial_Year,4)=
(select
year(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
where
isnull(EndDate_ConsultantEpisode,0) >= '01 apr 2009'
having month(max(isnull(EndDate_ConsultantEpisode,0)))<3
)
OR
left(Financial_Year,4)=
(select
year(max(isnull(EndDate_ConsultantEpisode,0)))
from
[ABI].[dbo].[vw_APC_SEM_001]
where
isnull(EndDate_ConsultantEpisode,0) >= '01 apr 2009'
having month(max(isnull(EndDate_ConsultantEpisode,0)))>3
)
END
November 15, 2011 at 5:52 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 6:01 am
Will do, sorry about that.
November 16, 2011 at 6:20 am
Hi everyone
I’ve got this procedure that’s running slowly. It takes anything from 5 mins upwards to run i.e. 5 mins is good - 30 mins is bad.
I went through the procedure and it's the update statements that take the most time to run, however any suggestions to improve efficiency would be gratefully received.
The statement selects data from a view called vw_APC_SEM_001 which has no indexes and inserts data into a table called QIPP.APC_ALL. The underlying table for the view vw_APC_SEM_001 is called tbl_APC_SEM_ALL. Both have 3,672,464 rows and the query retrieves approx 250,000 rows in total.
I’ve attached the table definition for tbl_APC_SEM_ALL, a list of indexes on the latter table, the view definition for vw_APC_SEM_001 and the execution plans in a spreadsheet (APC_ALL.xls) and in a .sqlplan file. The lookup table has about 900 rows.
I have only just started work on this and some of my permissions are restricted and I may have to go through a chain of command to get any suggestions (such as new indexes) implemented, but I happy to receive all suggestions.
The create view statement is several hundred rows long and I have NOT included it, but it's basically a select of most of the columns from the underlying table. If anyone wants to see it, I shall post that too.
Please let me know if anything else is required.
Many thanks in advance.
November 16, 2011 at 7:42 am
The sql plan you posted is returning a query with 247,958 rows? is that right?
an SSRS report should not be presenting a quarter million rows of data for anyone to even attempt to review, in my opinion.
I'm thinking the issue might be simply returning too many rows for a browser to render...some people, with 8 gig of local ram, could render it in 5 minutes, where others with one gig of ram takes 30 minutes?
I'd question up front whether this should be a report at all.
I saw that it's estimated to be 877824 rows, according tot eh execution plan.
you added an update to the SQL plan as well, which is affecting 223,162 rows...not sure why you need to update a lot of rows after selecting them?
the plan calls for adding a missing index, you might want to look at that too.
Lowell
November 18, 2011 at 6:05 am
Thanks for the reply. Apologies for my own response time - I work part-time.
You are correct about the number of rows being returned.
Re the missing index - I'll take another look at this. I'll knowledge-up on the execution plans too as I've not been using SQL Server that long or as intensively as I now need to.
The query was written by a colleague and I'm told there will be further aggregation performed in SSRS. Therefore, the number rows actually displayed in the report is quite small.
I've been assured that the report was written this way due to time contraints including the updates which I assume are there to fill gaps in the data. However, it doesn't mean it can't be improved and I'd be grateful for any suggestions on how to get around the updates by including the update criteria in the 'select' and a different/more efficient methodology for selecting the financial year and quarter. Unfortunately, I have been away for quite a while and my SQL/SSRS is a little rusty, hence my submission to this forum.
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply