0

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?

  • This feels a bit unclear currently, but if it is clarified would be a better question for Stack Overflow (or potentially, if you added your query, Code Review). – enderland Mar 8 '16 at 14:42
  • 3
    The DBA wizards over at dba.stackexchange.com can probably give you the best answer. – poke Mar 8 '16 at 14:45
  • For both of them, I would have to first rewrite the entire 75-line query to have non-sensitive aliases and DB Schemas so that they could poke at it, and then I would probably just be told that there's other problems with the query, without ever getting an actual answer to the question I'm really asking - What's the most efficient way to get a Rowcount for a complex query? – Zibbobz Mar 8 '16 at 14:48
  • 3
    @Zibbobz Incidentally, there is no silver bullet for these sorts of questions without seeing the query, the data, the stats, the configuration, the execution plan and the hardware. Suggestions can only be speculative at best. To cut a long story short - speak to your DBA. – Robbie Dee Mar 8 '16 at 15:02
  • 1
    Why do you need an exact count? You might be able to get away with selecting the top 101 records, returning the top 100, and reporting "there are more records that match your parameters - please refine your query" – Dan Pichelman Mar 8 '16 at 15:59
2

If you need the precise count of the records, there's little you can do but to actually run the same whole query. Data fetching is likely not the heaviest part of the query, joins are. (Were it not so, select count(*)... would be fast and you'd use it.)

You can speed up the query in various ways. It's hard to say anything without seeing the query and the query plan.

If there is a heavyweight join (or a cluster of them) that does not depend from (most of) the rest of the query, you could create an auxiliary table with its results pre-computed and use in your join instead. You'd likely need a trigger or a batch job to update it, or use a materialized view if your DBMS supports them.

If the pre-computed subquery table is not updated immediately with every update of the tables involved in the subquery, you could possibly by content with the approximate results, and recompute the data e.g. once a minute or once a hour.

2

If the query is a canned query that doesn't take any parameters and the results don't change often, you could consider running it during non-peak times and cacheing the result to use later. If you do use parameters and retrieve different result sets, then you could look at the query and see if there are any static portions and consider creating the view to encapsulate those parts; a good RDBMS will cache the view results so you don't have to re-execute those parts of the query again. If you can't do either of those things, then the best advice I can give is to go ahead and return all the results all the time and simply count the rows from the resultset. At least then you don't have to run the query twice.

-4

Use the COUNT aggregate function in your query.

SELECT COUNT(*) AS 'row_count', * FROM Table WHERE [...]

The COUNT function is standard in most SQL databases.

  • 2
    I don't think the OP is actually struggling with the count part. They're just trying to pull the count without running the main body of the data fetch query... – Robbie Dee Mar 8 '16 at 16:52

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.