Inefficient query generated when making use of Take() or Top() - implement FETCH FIRST n...

  • Thread starter Thread starter raphael.sch
  • Start date Start date
Status
Not open for further replies.
R

raphael.sch

Guest
When using “Take(100)” for example then the dataprovider maps this to a SQL statement that uses row_numbers. These row numbers are then used to select the rows where row_num smaller than the 100 (in this case). This is not efficient and is in fact causing one of our statements to come to a crawl and take 30 seconds! The proper DB2 way to perform a take top n rows, is to use “FETCH FIRST n ROWS ONLY” (at the end of a SQL statement) This prevents the massive performance impact of trying to number all rows and then performing a where on them.

Continue reading...
 
Status
Not open for further replies.
Back
Top