> There is one last part of this optimization issue though and it has to
> do with getting the total number of rows found in the query. I
> understand that using COUNT(*) is a bad idea. But in order to know how
> many pages of data the user can browse through, I'm completely at a loss
> as to how to get this number dynamically. Note that the user has the
> option to filter the list down to a specific search criteria, so I can't
> store the total number of rows through a trigger and get it this way
> because I won't know their search criteria. Basically what I need to be
> able to do is issue the query twice in the stored procedure - once to
> get the total number of rows it would return, and secondly to return
> only the set of rows that makes sense for their query.
> But I'm trying
> to do this without a performance impact.
>
> Can it be done? Are there any tricks to do this sort of thing?

There's no way you run twice the query and it doesn't slow down your query. One suggerstion is that you limit the max number or rows that you procedure will count, so you can paginate till it... example of returning to user 20 rows for page:
row1
row2
row3
...
page 1, page 2, page 3,page 4,page 5,.... page 50, More pages...

So your procedure will count first 1001 results to see if theres more than 100 pages but in true will show just first 100 pages...
I think that searches engines works almost like this (despite the aproximated search results it can show)...
I don't know if it will be a good idea for your case, but it's a idea :)

HTH,