[Solved] Paging in SQL extremely slow when using row number
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Paging in SQL extremely slow when using row number

I am trying to get data in batches. This one goes pretty fast:

select top 10 * from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '%[email protected]%'

Now when trying to get that data in batches it takes around two minutes. The query is extremely:

SELECT * FROM ( 
 select*, ROW_NUMBER() OVER (ORDER BY name) as row from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '%[email protected]%'
 ) a WHERE a.row > 10 and a.row <= 20 ​

Am I getting the batches in a wrong way?

Just discovered even this is pretty slow:

select top 10 *, ROW_NUMBER() OVER (ORDER BY id) as row from Table1 where (subject like '%Recap%' or subject like '% CP %') and Body like '%Details:%' and  ToAddr like '%xxx%'​

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 7): The database will not use an index when using like searches with a leading wildcard (e.g. '%Recap%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Avoid LIKE Searches With Leading Wildcard (query line: 8): The database will not use an index when using like searches with a leading wildcard (e.g. '% CP %'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  3. Avoid LIKE Searches With Leading Wildcard (query line: 10): The database will not use an index when using like searches with a leading wildcard (e.g. '%Details:%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  4. Avoid LIKE Searches With Leading Wildcard (query line: 11): The database will not use an index when using like searches with a leading wildcard (e.g. '%[email protected]%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  5. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        TOP 10 * 
    FROM
        Table1 
    WHERE
        (
            Table1.subject LIKE '%Recap%' 
            OR Table1.subject LIKE '% CP %'
        ) 
        AND Table1.Body LIKE '%Details:%' 
        AND Table1.ToAddr LIKE '%[email protected]%'

Related Articles



* original question posted on StackOverflow here.