How to Query Range of Data in Db2 with Highest Performance

How to query range of data in DB2 with highest performance?

My requirement have been added into DB2 9.7.2 already.

DB2 9.7.2 adds new syntax for limit query result as illustrate below:

SELECT * FROM TABLE LIMIT 5 OFFSET 20

the database will retrieve result from row no. 21 - 25

BETWEEN Versus = and = in DB2 SQL Query - Performance

Both those should give you exactly the same execution profile, based on my knowledge of DB2/z (the LUW product may be different but I doubt it).

If you're really concerned, you should run an EXPLAIN on the two queries to see if there are any differences.

Efficient DB2 query pagination and show total pages?

I don't think this exists in DB2.

Note that the total number of rows is a value that needs extra calculation to obtain. It isn't just lying around somewhere--it would have to be specifically built into the LIMIT logic. Which it doesn't look like they did.

DB2 - optimized query - apply function to current date or db column?

I would write the second version as:

select *
from mytable mt
where mycol in (timestamp(current date - 100 days, '00:00:00'),
                timestamp(current date - 200 days, '00:00:00')
               );

If you care about performance, then you should have an index on mytable(mycol), because this will speed the query. Without an index, the additional overhead is the call to date() in each row. You would need to run timings in your environment to determine whether that is an issue in your environment.

DB2 Using LIMIT and OFFSET

DB2 for Linux Unix Windows (LUW) and DB2 for iSeries are different products. Likely, DB2 for iSeries does not support DB2_COMPATIBILITY_VECTOR. I'm not able to find mention of it in the iSeries Information Center.

Instead of LIMIT, you can use the FETCH FIRST 10 ROWS ONLY clause.

Instead of LIMIT and OFFSET, you should be able to use a subselect with the ROW_NUMBER olap function. Something like this:

 SELECT emp.EMPNO, emp.SALARY
 FROM (

     SELECT EMPNO, SALARY, 
            ROW_NUMBER() OVER(ORDER BY SALARY DESC) as row_number
     FROM EMPLOYEE

 ) emp
 WHERE emp.row_number > 10
 AND emp.row_number <= 20

---

Related Topics