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
scenariobelow: 
>
> 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
(like1 - 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
ifyour query can vary with user input, storing counts for every possible combination of the query's parameters can
quicklybecome unwieldy. 

Another popular option is that works well if your results have a unique key (eg. their primary key) somewhere is to
queryonce to get all the key values and pass those along to the other pages in the pager. The query to fetch the actual
resultsper 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
informationyou need to display. As long as your result set isn't too large that works fine, but if it is too large you
shouldbe 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
databasesession across the pages of your pager (eg. not PHP). To get the count you scroll the cursor to the end (which
takesabout 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,
thequery result status also contains the number of records that you scrolled forward (just like the result status of an
insertcontains 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
theother options may well outperform it. 

Alban Hertroys

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


!DSPAM:737,4b59f10c10604404016430!