CODEWITHSUNDEEP
sqlsql-serverpaginationunion
DestyNova
DestyNova

Reputation: 736

How to optimize a UNION with a final ORDER BY/OFFSET

I got a complex table on a webpage (with datatable.js) that use with 3 differents "sources". This datatable is paginated in the front, but the back is not.

I want to put a server processing of the pagination, my request works, but I want to optimize it:

SELECT toto, titi
FROM
    (SELECT toto, titi
    FROM S1

    UNION

    SELECT toto, titi
    FROM S2

    UNION

    SELECT toto, titi
    FROM S3)
ORDER BY titi, toto
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

My real code is more complex, but you have the main idea of my problem here.

S1, S2 and S3 may have 1 000 000 rows each, but at the end I only need 50 rows, any idea on how I can make it more efficient?

Upvotes: 1

Views: 2052

Answers (1)

The Impaler
The Impaler

Reputation: 48810

Make sure you have indexes on all three tables with the columns (titi, toto).

create index ix1 on s1 (titi, toto);
create index ix2 on s2 (titi, toto);
create index ix3 on s3 (titi, toto);

Then the query could look like:

select * 
  from (
    select toto, titi from s1 order by titi, toto 
      fetch next (50 + 50) rows only
    union all
    select toto, titi from s2 order by titi, toto
      fetch next (50 + 50) rows only
    union all
    select toto, titi from s3 order by titi, toto
      fetch next (50 + 50) rows only
  ) x
  order by titi, toto
  offset 50 rows fetch next 50 rows only

In any case, your pagination strategy is quite inefficient for high values of OFFSET. My query above can make a difference nevetheless.

The key improvements over your query are:

  • Using indexes on each table separately.
  • Use UNION ALL instead of UNION since it more efficient.

Upvotes: 2

Related Questions