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. – Chris Aldrich Sep 14 '12 at 19:47
  • Which edition are you on? LUW, z/OS, i? – WarrenT Sep 14 '12 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 '12 at 4:23

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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