We write a query that include unpivot, partition by and order by.
Query is:
SELECT PersonId
,SalaryDate
,ID
,Type
,SalaryValue
,ROW_NUMBER() OVER (
PARTITION BY PersonId ORDER BY SalaryValue
) AS rn
FROM (
SELECT lp.PersonId
,lp.SalaryDate
,lp.Salary1
,lp.Salary2
,lp.Salary3
,lp.ID
FROM rdd.Salaries AS lp WITH (NOLOCK)
WHERE lp.SalaryDate > DATEADD(day, - 31, getdate())
) AS t
unpivot(SalaryValue FOR Type IN (
lp.Salary1
,lp.Salary2
,lp.Salary3
)) AS UnpivotTable
The query returns about 68.000.000 rows and execution time is 20 minutes.
Can I improve the query's performance or rewrite effectively?
What is the alternative of partition by?
CREATE TABLE [rdd].Salaries ( ID [bigint] NOT NULL ,Salary1 [decimal](18, 3) NULL ,Salary2 [decimal](18, 3) NULL ,Salary3 [decimal](18, 3) NULL ,[SalaryDate] [datetime] NULL ,[SalaryDateInt] [bigint] NULL ,[SalaryDateIntAll] [bigint] NULL ,[ErrorStatus] [int] NOT NULL ,[ErrorDetail] [varchar](500) NULL ,[PersonId] [bigint] NULL ,[RecordStatus] [char](1) NOT NULL ,CONSTRAINT [PK_Salaries] PRIMARY KEY CLUSTERED (ID ASC) )
CREATE NONCLUSTERED INDEX IX_rdd_Salaries ON rdd.Salaries (SalaryDate ASC,ErrorStatus ASC,RecordStatus ASC)INCLUDE (PersonId,Salary1,Salary2,Salary3)