Hi,

Mitchell Peek wrote:

>
>williamvdw2004 wrote:
>
>
>
>>I have a table with about 630 000 records. I do the following count
>>query. select count(transactionkey) from account.
>>
>>
>>
>>
>unless things have changed regarding this, ....
>
>Select count(*) from account gives the same results as select
>count(transactionKey) from account (given that transactionkey is the
>primary key.
>
>The results could only be different if the data element allowed nulls.
>< the nulls would not be counted>
>
>This doesn't answer your question, but in the future with different
>columns, you could be getting, what appears to you, erroneous results
>unless you are aware of this.
>
>Subsequently, if memory serves, you might even get a slight boost in
>performace using select count(*) instead because it needn't analyze the
>data in trasnactionkey.
>
>
>
>>This query takes about 30 seconds the first time and 3 seconds
>>subsequently. Transactionkey (bigint) is defined as the pimarykey of
>>the table I have also added a descending index to the transactionkey
>>column, page size is 8192. If I change the query to count the rows
>>in the backup table the same thing 30 seconds the first time and 3
>>seconds subsequently. Is there anything I can do to speed this up
>>(the first query?)
>>
>>
>>
>>
>Why do you need the count? Keep in mind that FB/IB does not maintain
>this value as does MS SQL and others. . If you maintain counts in
>auxillary tables that give you a count based on your trasnaction, it
>could also complicate your UI because you could have a lot of users
>trying to update that same row in the same table simultaneously.
>
>
>

IIRC Ann has pointed sometimes when someone needs a count of registers.

The solution is very simple and fast.

Keep a table with a column called for example counter
Create Table InvoiceCount(Counter integer)

then create an after insert trigger like this:
create trigger TI_Invoice_Counter for Invoice after insert as
begin
Insert into InvoiceCounter (Counter) values(1);
end

do the same for the delete but add -1 instead.

then to get the total count you use this query:

Select sum(counter) from InvoiceCount

Then you asks me, but how does this differ from a count ? How can I gain
speed with this ?

Simple, from time to time (monthly, daily, hourly, ???) you sum all the
counts and remove all the records and put just one record with the
total, the triggers will continue to populate the table so you will have
something like this:
Counter
123003
1
1
1
1
-1
1

when you asks for a sum for that column you will get 123007 and will run
trough 7 records to get it instead of 123007, and the insert aproach
doens't causes lock conflicts and thus doesn't become a bottleneck.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005