Re: [Firebird-devel] Count(*) faster without index ?

[Derryck w. <der...@ce...>]

Hi All,

Dmitry Yemanov" wrote:
>How many rows is in lineitem: (a) totally and (b) corresponding your
>predicate? I suppose the difference between these two numbers is not
>very big.
in this case:
a=6001215
b=5916591

> Depending on what do you want us to improve :-)

in general make count faster :-)

on details i dont know , scan the index for the count ?
i can already see there is less fetching with an index but
the endresult is more time for the count operation

What would really help is to have a param to cache the counted value (hash 
per unique query) for a certain time.
example SELECT CACHED <seconds> COUNT(*) FROM..
For example if this count is called within 1 minute it just returns the 
cached value. then after time ellapsed time
do a real count again.
I do something like this with stored procedures and a temp table but it is a 
little bit messy
for example if 5 users start the procedure at once, (my actual counts run 
for more than a minute)
i still get 5 simultanous counts performed on the server.

oke the cached thing maybe looks horrible  :-)  but for very large counts 
its the only solution.

>adam wrote:
>How did you test this? You need to remember that caching happens both
>within Firebird and also your operating system, so the query that runs
>first will have to wait for disks, whereas the query that runs second
>can often just read from the cache.

>paul wrote:
>It might also be interesting (and very quick to verify) to just reverse the
>order of the tests. On the evidence presented it looks like the second test 
>is
>benefiting from the cache created by the first test.

Dont mind the testing i rebooted to give the test identical conditions.
misc info: FB CS 2.1.1 /XP/1GB mem/dual core pentium 1.8Ghz

steps:

1e) reboot
2e)
PLAN (LINEITEM INDEX (LINEITEM_SHIPDATE))

 COUNT_ORDER
============
     5916591

Current memory = 4633152
Delta memory = 3696412
Max memory = 4633152
Elapsed time= 39.03 sec
Buffers = 75
Reads = 111237
Writes 0
Fetches = 11837255

3e)
Current memory = 4633156
Delta memory = 3696412
Max memory = 4633156
Elapsed time= 37.87 sec
Buffers = 75
Reads = 111237
Writes 0
Fetches = 11837255

4e)
Current memory = 4633156
Delta memory = 3696412
Max memory = 4633156
Elapsed time= 37.78 sec
Buffers = 75
Reads = 111237
Writes 0
Fetches = 11837255


1e)reboot:
2e)
PLAN (LINEITEM NATURAL)

 COUNT_ORDER
============
     5916591

Current memory = 1009508
Delta memory = 72772
Max memory = 1156796
Elapsed time= 37.87 sec
Buffers = 75
Reads = 107563
Writes 0
Fetches = 12217691

3e)
Current memory = 1009512
Delta memory = 72772
Max memory = 1156800
Elapsed time= 37.21 sec
Buffers = 75
Reads = 107563
Writes 0
Fetches = 12217691

4e)
Current memory = 1009512
Delta memory = 72772
Max memory = 1156800
Elapsed time= 36.16 sec
Buffers = 75
Reads = 107563
Writes 0
Fetches = 12217691

To me this is closer to a production environment
alternate between the 2 queries Q1=with use of index.,Q2=without index.
Q1      Q2 Q1 Q2 Q1      Q2
38.06,  36.14, 37.29,  36.18   37.32   36.17


Derryck