Its seens reasonable, i wrote a pagination script that works like:

First Prev 1 2 3 4 [5] 6 7 8 9 Next Last

I count it so i can know how many pages are there so i can make all the
links and stuff... the problem is that when i wrote that almost one year
ago, i didnt think about the performance of this thing, i always did this in
websites, but websites are different from what i did.

But anyway thanx for the tips, i ll try to come up with something better
that what i did.

On 8/8/06, Nigel Weeks <nweeks@...> wrote:
>
> Don't use count for pagination - it's not really necessary.
>
> There's nothing wrong with [First 20] [Prev 20] [Next 20] buttons, and if
> you wander off the end of the recordset, you simply get produce a "no more
> records" message...
>
>
> N
>
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>
> > [mailto:firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>]On
> Behalf Of Adam
> > Sent: Wednesday, 9 August 2006 9:53 AM
> > To: firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>
> > Subject: [firebird-support] Re: Is there a way to optimize a
> > count()? or
> > am i doing it the wrong way?
> >
> >
> > --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> "Fabio Gomes" <fabioxgn@...>
> > wrote:
> > >
> > > 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!
> >
> > You are right, that is way too slow.
> >
> > Count is an expensive operation (just as expensive in prepare and
> > execution as selecting a field, quicker on fetch obviously). I think
> > your problem is most likely to do with garbage collection rather than
> > the count per se. You are probably 'shooting the messenger' by blaming
> > count. If you remove the count, it will take 20+ seconds to return the
> > rows in the select itself, because instead of the count cleaning up
> > the garbage, your select is.
> >
> > Some Firebird 2 index improvements will improve this, but in the
> > meantime you must avoid creating an index on a table with lots of
> > duplicates. Such an index costs a lot of CPU time to maintain when
> > removing a record, which is what garbage collection largely does.
> >
> > But lets take a look at your query anyway to make sure it is not just
> > using a poor index. How long does the following query take?
> >
> > SELECT 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 assume there is an index that can be used to resolve your where
> > clause, and an index that can be used for the joins.
> >
> > Adam
> >
> >
> >
> >
> >
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > 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]