We have a query that returns over 20,000 rows on some occasions but times out due to the large amount of data.

We have written a COUNT(*) query to throw an error if the record count is more than 20,000. If the count is less than 20,000, we go ahead and do some processing on the query result returned. However, this is a huge performance overhead, executing 2 queries on about 20,000 rows. In order to mitigate this, we decided to restrict the number of results returned using fetch first 20,000 rows only. However, in this case we are not able to identify when more than 20,000 rows are returned for storing in our log.

We need a mechanism using a single query to fetch only 20,000 rows and throw an error if the result contains more.

  • 2
    First...I guess I'd ask..is your problem to know the count of the result set? Or are you wanting to fix the performance problem so you can return more than 20000 records? Also, is the problem on the db side or the application side? What exactly times out? What is your table? What is your query? Please provide this information as it will help with troubleshooting your issue. Sep 14, 2012 at 19:47
  • Which edition are you on? LUW, z/OS, i?
    – WarrenT
    Sep 14, 2012 at 20:51
  • 1
    Yes, we need to know more about your table definition, what your query is, whether there are appropriate indexes, etc. And what is the question here?
    – WarrenT
    Sep 15, 2012 at 4:23

1 Answer 1


Use fetch first 20001, and log an error if you get the last record.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.