Preserve everything count and get filtered results in t-sql?

I have created a complex sql server 2008/coldfusion search page, that searches thru a variety of tables.

On the left is a list of the categories, plus an everything category, by each category or type of result is a total number of results of that type found in the current search result.

I have everything fine, but I am hoping there is a more optimal approach.

Because everytime i filter the search to a specific category, i still have to get all the results, so as to make sure the everything category has the correct totals.

And because of this, I have realized this is a problem I've had in lots of other programs in coldfusion/sql.

Where you want to reduce the number of results by some field in the select, but you need to keep the original recordcount total.

But you really don't want to re-run the whole massive query everytime, when you just need to get the trimmed results.

This program is 1 cfc, 1 cfm, 1 stored procedure, and jquery/aja开发者_运维问答x inside the cfm to call the cfc.

The cfm calls the cfc when it originally get's a form submitted search request, and then any filtering does the same thing.

However if there are more than 20 results then it show's a button at the bottom to do via ajax get 20 more records.

My main goal is to improve performance, make sure i keep an accurate record of what the record count is before any filtering is done, without having to rerun the unfiltered query every time.

This is a kind of complex problem, so there might not be any answers...

Thank you all for trying..

---

I would run the "big" query once, then pop it into a SESSION variable. Then I'd use Query-of-Query to return subsets based on filters.

The main query always exists, so you can query against that or use metadata like bigQuery.recordCount. Your QofQ is a smaller set of data you can use for display. And you can re-apply filters without having to return to the database.

---

Well you need to run the query (or a count(*)) at least once to get the total number. You could:

• Cache this query and refer to the cached query's recordcount again and again

• Store the record count in the session scope until the next time it is run for this user