1

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

enter image description here

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?

9
  • What indexes do you currently have on the Salaries table? Nov 5, 2014 at 9:49
  • The index has SalaryDate and include PersonId, Salary1, Salary2, Salary3
    – sabandurna
    Nov 5, 2014 at 10:14
  • @Armstrong63 is it a composite index, or all indexes alone? Your writing would suggest the first option. Could you please provide entire table schema? Nov 5, 2014 at 10:25
  • Table schema is:<br/> 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) )
    – sabandurna
    Nov 5, 2014 at 10:54
  • This index is composite index. Index is:<br/>CREATE NONCLUSTERED INDEX IX_rdd_Salaries ON rdd.Salaries (SalaryDate ASC,ErrorStatus ASC,RecordStatus ASC)INCLUDE (PersonId,Salary1,Salary2,Salary3)
    – sabandurna
    Nov 5, 2014 at 10:55

1 Answer 1

2

You may find that the following index and query rewrite performs better, because it sorts per person rather than once over the whole set, and row estimates are more likely to be accurate:

-- Index
CREATE INDEX IX_Salaries_PersonId_SalaryDate_Inc_ID_Salary1_Salary2_Salary3
ON rdd.Salaries (PersonId, SalaryDate)
INCLUDE (ID, Salary1, Salary2, Salary3);

-- Query
WITH People AS
(
    SELECT DISTINCT
        S.PersonId
    FROM rdd.Salaries AS S
    WHERE 
        S.SalaryDate > DATEADD(DAY, -31, GETDATE())
)
SELECT 
    P.PersonId, 
    CA.SalaryDate, 
    CA.ID, 
    CA.SalaryValue, 
    CA.rn
FROM People AS P
CROSS APPLY
(
    SELECT
        S.SalaryDate, 
        S.ID, 
        V.SalaryValue, 
        rn = ROW_NUMBER() OVER (ORDER BY V.SalaryValue)
    FROM rdd.Salaries AS S
    CROSS APPLY
    (
        SELECT S.Salary1 WHERE S.Salary1 IS NOT NULL
        UNION ALL
        SELECT S.Salary2 WHERE S.Salary2 IS NOT NULL
        UNION ALL
        SELECT S.Salary3 WHERE S.Salary3 IS NOT NULL
    ) AS V (SalaryValue)
    WHERE 
        S.PersonId = P.PersonId
        AND S.SalaryDate > DATEADD(DAY, -31, GETDATE())
) AS CA
ORDER BY
    P.PersonId,
    CA.rn
OPTION (QUERYTRACEON 8649);

You can omit the OPTION clause if you find a parallel query is generated naturally, or if you find non-parallel performance is good enough. The desired plan shape is roughly as follows:

Plan Shape

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.