I have a large SQL query that relates 6-7 different tables and returns fourteen different rows as distinct sets, in a union with an equally-large query, that at times can return over a thousand results.
Luckily for us, we have the option to simply return the first 100 results of that query, which significantly reduces its processing time.
However, we also need an exact count of that query, which we've only managed to get by querying the entire set, and when we hit the larger return sets, it causes our app to hang.
This is hugely inefficient, on top of which the real issue is our count query - it is basically the exact same query, but instead of returning the distinct rows, it's just returning a count of those rows!
We can't filter out our tables or our return values - because they are a distinct set, they change the row count. But we can't run this query twice in a row just to get the number of rows we're expecting, with the rowcount being the more cumbersome of the two each time.
There has to be a better way - how can I get my SQL to return a rowcount of a large query, without wasting the resources of that query?