I run 2 queries, one that just counts, and another that returns just 20
results, the only purpose of the count is to generate the paging numbers the
query is very very fast.. some milliseconds.. but the count takes a lot of
time.

Database header page information:
Flags 0
Checksum 12345
Generation 122091
Page size 4096
ODS version 10.1
Oldest transaction 122088
Oldest active 122089
Oldest snapshot 122089
Next transaction 122090
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Dec 28, 2005 7:40:25

On 8/8/06, Rick Debay <rdebay@...> wrote:
>
> If the query listed here will only return 20 results, the count should
> be fast.
> If it is not, then it's probably because you have lots of old versions of
> these records available, that COULD match I.pro_cod='1' if they were
> visible to your transaction.
>
> Can you show the plan, the current/oldest transaction numbers, and perhaps
> run a sweep and test again?
>
> Rick DeBay
>
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>[mailto:
> firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>] On
> Behalf Of Fabio Gomes
> Sent: Tuesday, August 08, 2006 12:49 PM
> To: firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>
> Subject: [firebird-support] Is there a way to optimize a count()? or am i
> doing it the wrong way?
>
> I have this query:
>
> SELECT count(I.vnd_cod)
> FROM items_venda I
> JOIN vendas N ON (N.vnd_cod=I.vnd_cod)
> JOIN clientes C ON (N.cli_cod=C.cli_cod) JOIN vendedores V ON (
> N.ved_cod=V.ved_cod) WHERE I.pro_cod='1'
>
> I m using it on php, so this code is just for the pagination, the main
> query will just select 20 rows, no more than it. but i need a count of all
> the rows so i can know how many rows the query returned, and pass it to my
> pagination function.
>
> We have a slow server here (P4 2.0ghz 512MB) this query takes about
> 20+ seconds to return and uses almost 100% of the cpu to do it.
>
> So, is there a way to optmize this count() function?
>
> I m asking it cause currently we have just one company using this
> application, but my boss said that almost sold it to another company that
> have a lot more data then the current one, so i m beggining to worry about
> the performance of some stuff, cause i use this in almost all reports, this
> one is the worst in performance, but wating 20 seconds to show up 20 rows is
> too much!
>
> thanx for any help,
>
> Fábio.
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item on the main
> (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>


[Non-text portions of this message have been removed]