Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

SKIP and LIMT statement in SQL take long time to execute

  • 1.  SKIP and LIMT statement in SQL take long time to execute

    Posted Tue January 23, 2024 08:07 AM
    Dear Friends,
    For a business need, we need to get 20000 records from the table every day. A table has more than 38 million records. When it reaches to higher value similar to below, SQL running time is too long and the query execution terminated due to timeout.
     
    e,g -> 
    SQL 1 (quick response)
    ==================================
     
    SELECT skip 0 limit 20000 col  
    FROM tab1
    ORDER BY col
     
     
    SQL  2 (quick response)
    ==================================
     
    SELECT skip 20001 limit 40000 col  
    FROM tab1
    ORDER BY col
     
    .....
    ...
    .....
    ...
     
     
    SQL X (taking long time or timeout error)
     
    ==================================
    SELECT skip 30000001 limit 30020000 col  
    FROM tab1
    ORDER BY col
     
    Would you pl help us to resolve this issue?
     
    Thanks 


    ------------------------------
    Indika Jinadasa
    ------------------------------


  • 2.  RE: SKIP and LIMT statement in SQL take long time to execute

    Posted Tue January 23, 2024 11:43 AM

    Hi Indika,

    A 'SKIP <n> LIMIT <m>' query has no choice but reading through those <n> initial rows before returning the desired <m> rows after those - how else would it determine where to start with those <m> rows?  So the bigger the <n>, the longer this step is going to take.

    Now you're stating you need to get 20000 rows each day, so <m> should be 20000 in each such query while you're incrementing your <m> by 20000 each time.  If true, this might not only give you many more rows than desired, it will also prolong the run time, esp. given that ORDER BY clause.

    That said, and not knowing your exact intentions behind this approach:  yesterday's  <n>  might be a different set of rows from today's (identical)   <n>, and the database would not guarantee you today's <m> rows being exactly the next ones after yesterday's  - there could be overlaps or gaps.  One reason: new inserts are not guaranteed to reside in a table in any particular order.
    If 'col' is indexed, though, and new rows arriving with ever increasing 'col' values, your approach might make more sense - as long as the index is used for the query.

    Couldn't your aim be reached using some ID column and a good WHERE clause instead?

    BR,

     Andreas



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message