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 [mailto:firebird-support@yahoogroups.com] On Behalf Of Fabio Gomes
Sent: Tuesday, August 08, 2006 12:49 PM
To: firebird-support@yahoogroups.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