Paging

[cryan]

A few questions:

Is there a way to use a query multiple times? I know you can use a CTE but that will be removed after the next query and I do not want to create temp tables on a highly used database.

Is there a best practice for doing searches? For example, if someone searches "SQL" on 10mil rows, I want to return both the paged results but also show categories/total results/etc.

Lastly, is there a way to reduce reads on paging results using "row_number ()" and "between" on large databases? A query that uses 10k reads jumps to 240k reads.

Thanks for the help.

[Rabbit]

To be able to reuse something, something about it must be saved. You eliminated temp tables. You can use an indexed view. Or you can "use" the cache, but that's automatic.

I don't know what you're asking in your second question.

Without seeing the query in question, it's hard to say anything about optimization.

[cryan]

Basically, on the second question I would like to do this efficently:

Return Results of a search query (containstable, etc) depending on page.
Return Category Present in the all results queried above.
Return Total Number of Results

The results could be 100k but I want to return all the available categories and total count but only want to return 20 results based on what page they are on.

Sorry if I am confusing but it would be just like a shopping search engine. It will give ways to refine results based on search query.

[Rabbit]

Well, to find all the distinct categories in the result set, I think the quickest way to do that will be to run a separate query. You could try processing it in code but that will probably take longer.

As for the record count, you can either use a third query for that, or combine it with the query for distinct categories and turn it into an aggregate count query. Then you can add that up in your code as you output the categories.