Search code examples
sqlsql-serverdatabasesql-optimization

Performance tuning of this query?

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 ?

Solution

  • 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 ?