SQLTeam.com | Weblogs | Forums

How to improve query performance on a single table with 12M records


#1

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


#2

This could give you at hint, if your query would benefit from an index


#3

show us table structure and the where clause used


#4

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]


#5

what is the answer you would like to get from this one giant table?


#6

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.


#7
  1. but your group by tells me a different story? Do you really need all of these columns?
  2. Define top 10
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]

#8

The other group by fields are your filters.

Thank you

Siva


#9

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


#10

I take this data and filter it in excel using pivot tables. I import the output into excel powerpivot data model.

thank you

Siva