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.