Performance tuning of this query?

Question

I have below query to support employee pagination sorted by employee name

SELECT rowNumAlias
    ,Employee.employeeId
    ,Employee.NAME
FROM (
    SELECT row_number() OVER (
            ORDER BY Employee.NAME ASC
            ) rowNumAlias
        ,employeeId
        ,NAME
    FROM Employee
    ) employeeData
INNER JOIN Employee ON Employee.employeeId = employeeData.employeeId
WHERE rowNumAlias BETWEEN ? AND ?

Where parameter rowNumAlias can be any integer number between 1 and 100

This query is taking around 7 seconds on my sql server database having 1 million records. Is there a way i can minimize query execution time ?

You need to [get an execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). This will tell you what the slowest part of the query is and will possibly even suggest indexes that can be used to speed up the query. — Justin, Sep 22 '14 at 10:11
Which version of SQL Server? If you are on 2012+ there are better ways to do pagination. What is the query execution plan telling you? Maybe you are missing an index (on Employee.name for instance)? — jpw, Sep 22 '14 at 10:13
assuming the pk/fk's are indexed, ordering on employee name is likely to be the performance hit, not much else to "optimize". so does it name have an index? (or use sql server 2012's better pagination syntax if available) — Paul Maxwell, Sep 22 '14 at 10:20

score 1 · Accepted Answer · answered Sep 22 '14 at 10:05

1

You can try like this:

SELECT * FROM (
SELECT (SELECT row_number() OVER (ORDER BY e2.NAME ASC) FROM Employee e2 WHERE Employee.employeeId = E2.employeeId) rowNumAlias,
    ,Employee.employeeId
    ,Employee.NAME
FROM Employee 
) e3 WHERE e3.rowNumAlias BETWEEN ? AND ?

answered Sep 22 '14 at 10:05

Iulian

169
3

are you sure you want to propose running row_number() as a correlated subquery in the select clause? not sure it will even work – Paul Maxwell Sep 22 '14 at 10:23
i do not know if there is other solution for this query. – Iulian Sep 22 '14 at 10:28
but how will your proposal improve the existing? if you are not sure I'd suggest not trying otherwise you will get "downvoted" – Paul Maxwell Sep 22 '14 at 10:33

score 1 · Answer 2 · answered Sep 22 '14 at 10:30

1

You can try to use CTE for this.

;WITH employeeData as 
(
    SELECT row_number() OVER (ORDER BY Employee.NAME ASC) rowNumAlias,
    employeeId,
    NAME
    FROM Employee
)
SELECT employeeData.rowNumAlias,
employeeData.employeeId,
employeeData.NAME
FROM employeeData
INNER JOIN Employee ON Employee.employeeId = employeeData.employeeId
WHERE rowNumAlias BETWEEN ? AND ?

answered Sep 22 '14 at 10:30

nvm-uli

626
1
7
14

Thats good suggestion but that also took the same execution time – user3198603 Sep 23 '14 at 10:29

score 1 · Answer 3 · answered Sep 22 '14 at 10:48

If you are on SQL Server 2012+ you can use the ORDER BY ... OFFSET ... FETCH ...syntax to do pagination:

SELECT EmployeeId, Name    
FROM Employee
ORDER BY Employee.Name ASC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

OFFSET specifies how many rows to skip and FETCH NEXT how many to get. See the documentation.

In earlier versions you should be able to get better performance by using a common table expression (with proper indexes this query looks like it executes in halt the time of your original according to my execution plan):

;With cte (rownum, employeeid, name)  as (
    SELECT 
       rownum = row_number() OVER (ORDER BY employee.name ASC),
        employeeid, 
       name
    FROM employee
)

SELECT rownum, employeeid, name
FROM cte
WHERE rownum BETWEEN ? AND ?;

@user3198603 That's odd. My execution plans shows my cte query has a cost (relative to the batch) of 33% compared to the accepted answer that has a cost (relative to the batch) of 67%. Did you try it with a nonclustered index for employee.name ascending? — jpw, Sep 26 '14 at 13:07

Performance tuning of this query?

3 Answers3