Re: DDL question

On 22 Jan 2010, at 16:31, Volkan Varol wrote:

> Hello precious and brilliant minds,
>
> I would like to know if there's a better way (syntactical or logical) to achieve a better performance for the scenario below:
>
> Let's say we have any kind of query with a LIMIT and an OFFSET (used to limit/offset rows displayed on the web page).. For example:
>
> QUERY1:
> SELECT col1, col2, col3 FROM tableA WHERE col1='qwerty' LIMIT 25 OFFSET 0;
>
> Now I would like to run the same query WITHOUT LIMIT and OFFSET to obtain the total count of the rows:
>
> QUERY2:
> SELECT COUNT(*) INTO row_count FROM tableA WHERE col1='qwerty';
>
> I use QUERY1 to display the actual data and QUERY2 to calculate the number of 'pages' to display web page pagination (like 1 - 2 - 3 --- 10).
> To achive this I either run the two queries separately or combine the two queries by placing the second one as a subselect:

...

> I'm in search for a new method / syntax to combine these two queries and I welcome your suggestions.
> Thanks in advance.

There are a few options.

You can, as you suggested, pre-calculate your counts and store them in a table somewhere. This has an obvious drawback if your query can vary with user input, storing counts for every possible combination of the query's parameters can quickly become unwieldy.

Another popular option is that works well if your results have a unique key (eg. their primary key) somewhere is to query once to get all the key values and pass those along to the other pages in the pager. The query to fetch the actual results per page often gets much simpler if you already know the primary keys.

A variation on the above is to store the results in for example the session. You store all of them, but only the information you need to display. As long as your result set isn't too large that works fine, but if it is too large you should be asking yourself how your users are going to find what they need among that many results.

It's also possible to use a scrollable cursor, but that depends on your language being able to maintain the same database session across the pages of your pager (eg. not PHP). To get the count you scroll the cursor to the end (which takes about as much time as a COUNT(*) query).
With CTE's you could get an actual row number so that you know which number the one at the end is, but regardless that, the query result status also contains the number of records that you scrolled forward (just like the result status of an insert contains the number of rows inserted).
IMHO that's the approach that matches best with what you're trying to achieve, but it's not always possible and some of the other options may well outperform it.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b59f10c10604404016430!