My table has12 m records, the query runs over 4 hours if I use partition and window function for 3 fields. This is in SQL server 2008 R2. I also need to calculate the variance % of same period last year. Hence I used CTE in combination with row_number()window function to do this. This took nearly 17 hours to run. My colleagues suggested to use indexes but this is a standalone table, I am not using any joins or any unique foreign keys.
Any help would be greatly appreciated?
Thank you
Siva
This could give you at hint, if your query would benefit from an index
show us table structure and the where clause used
Thank your for getting back to me.
USE ccg_centrallondon
Go
SELECT
[AgeGroup]
,[EthnicCategoryCode]
,[Gender]
,[AttendedOrDidNotAttend]
,[CoreHRG]+'-'+[HRG_Name] as HRGName
,[GPPracticeCode]
,[GPPracticeCode]+'-'+[GPPracticeName] as PracticeName
,[ProvCode]+'-'+[ProvName] as Provider
,[CCG_Code]+'-'+[CCG_Name] as CCG
,[OutcomeOfAttendance]
,[FiscalMonth]
,[FinancialYear]
,[zFinancialYear]
,[PrimaryProcedureCode]+'-'+[PrimaryProcedureDescription] as PrimaryProcedure
,[PriorityType]
,[ReferralSource]
,[TreatmentFunctionCode]+'-'+[TFCDescription] as Specialty
,[zPODlevel4]
--,COUNT(CASE WHEN [AttendedOrDidNotAttend]='ATTENDED' and YEAR(AppointmentDate)=DATEADD(YEAR, -1, AppointmentDate) Then 1 Else 0 End)as PreviousYear
--,DENSE_RANK() OVER (Partition by [TreatmentFunctionCode]+'-'+[TFCDescription] ORDER BY COUNT([RowID]) desc) as SpecialtyRank
--,DENSE_RANK() OVER (Partition by [ProvCode]+'-'+[ProvName] ORDER BY COUNT([RowID]) desc) as ProviderRank
--,DENSE_RANK() OVER (Partition by [PrimaryProcedureCode]+'-'+[PrimaryProcedureDescription] ORDER BY COUNT([RowID]) desc) as ProcedureRank
--,ROW_NUMBER() OVER (Partition by [GPPracticeCode] ORDER BY COUNT([RowID]) desc) as PracticeRank
,ROW_NUMBER() over( order by [zFinancialYear]) as row_num
,SUM([First]) as [First Attendances]
,SUM([Follow Up]) as [Follow Up Attendances]
,SUM([OP Procedures]) as [OP Procedures]
,COUNT([RowID]) as Activity
,COUNT( DISTINCT [zNHSNumberPseudo]) as Patients
,SUM([PbRFinalTariff]) as Costs
INTO [ccg_centrallondon].[dbo].[OP_DD]
FROM [ccg_centrallondon].[dbo].[OP]
GROUP BY [AttendedOrDidNotAttend]
,[CoreHRG]+'-'+[HRG_Name]
,[GPPracticeCode]
,[GPPracticeCode]+'-'+[GPPracticeName]
,[ProvCode]+'-'+[ProvName]
,[CCG_Code]+'-'+[CCG_Name]
,[OutcomeOfAttendance]
,[FiscalMonth]
,[FinancialYear]
,[zFinancialYear]
,[PrimaryProcedureCode]+'-'+[PrimaryProcedureDescription]
,[PriorityType]
,[ReferralSource]
,[TreatmentFunctionCode]+'-'+[TFCDescription]
,[zPODlevel4]
,[AgeGroup]
,[EthnicCategoryCode]
,[Gender]
what is the answer you would like to get from this one giant table?
Thank you Yosiasz. i would like to get the Top 10 Specialties, Providers, Practices, Primary Procedures on First Attendances, Follow Up Attendances, OP Procedures, Activity,Patients and Costs along with their % comparison of previous year performance or Same Period Year To Date Comparison.
GROUP BY [AttendedOrDidNotAttend]
,[CoreHRG]+'-'+[HRG_Name]
,[GPPracticeCode]
,[GPPracticeCode]+'-'+[GPPracticeName]
,[ProvCode]+'-'+[ProvName]
,[CCG_Code]+'-'+[CCG_Name]
,[OutcomeOfAttendance]
,[FiscalMonth]
,[FinancialYear]
,[zFinancialYear]
,[PrimaryProcedureCode]+'-'+[PrimaryProcedureDescription]
,[PriorityType]
,[ReferralSource]
,[TreatmentFunctionCode]+'-'+[TFCDescription]
,[zPODlevel4]
,[AgeGroup]
,[EthnicCategoryCode]
,[Gender]The other group by fields are your filters.
Thank you
Siva
filter go in the WHERE clause, not sure I understand.
What are the columns you would like to see at the end of the query. Right now you query is grouped with so much stuff that you might not need
I take this data and filter it in excel using pivot tables. I import the output into excel powerpivot data model.
thank you
Siva
hi siva
i know this topic was 9 months ago
but this link might help
Hope it helps
Here are the quick tips to improve query performance in SQL Server Database: