Dave,

<< Arggh. Operator error. I used KeyFieldCount:=1 just once, before the
SetRangeEnd but not before the ApplyRange. I thought the ApplyRange would
use the existing KeyFieldCount value. I added the extra KeyFieldCount and
its working find now. Oh well. Stand down from red alert. >>

Ah, cool, thanks.   But, it still is a little disconcerting that it is
taking ~6 seconds to do a range in the first case.  They should always be
instantaeous as of 1.02.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> << Arggh. Operator error. I used KeyFieldCount:=1 just once, before the
> SetRangeEnd but not before the ApplyRange. I thought the ApplyRange would
> use the existing KeyFieldCount value. I added the extra KeyFieldCount and
> its working find now. Oh well. Stand down from red alert. >>
>
> Ah, cool, thanks.   But, it still is a little disconcerting that it is
> taking ~6 seconds to do a range in the first case.  They should always be
> instantaeous as of 1.02.
>

I re-did the range benchmark and with the proper KeyFieldCount:=1 at
both locations, the range works faster. The first invocation takes
between 500ms and 900ms to display the range. Subsequent calls on the
same range will get it down to 16ms to 30ms due largely I think to the
Windows cache.

I did notice that the more rows that were returned by the range, the
slower the range. So I suspect the culprit might be the record count. It
may be counting the # of rows returned which is slower than the
execution of the range. Is there any way to turn the RecordCount off?

Dave

Dave,

<< I did notice that the more rows that were returned by the range, the
slower the range. So I suspect the culprit might be the record count. It may
be counting the # of rows returned which is slower than the execution of the
range. Is there any way to turn the RecordCount off? >>

It's not counting, but it does have to set a bit in a bitmap for each row
that satisfies the range.  That could take some time for hundreds of
thousands of rows.  DBISAM uses statistics to get the count without a
bitmap, but EDB doesn't use statistics so it has to use the bitmap to get
the count.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>It's not counting, but it does have to set a bit in a bitmap for each row
>that satisfies the range. That could take some time for hundreds of
>thousands of rows. DBISAM uses statistics to get the count without a
>bitmap, but EDB doesn't use statistics so it has to use the bitmap to get
>the count.

Are there implications with this for tests like if table.RecordCount > 0 then ..  ?

Should we convert them to if not (table.eof and table.bof) then ....  ?

Roy Lambert

Tim Young [Elevate Software] wrote:
> Dave,
>
> << I did notice that the more rows that were returned by the range, the
> slower the range. So I suspect the culprit might be the record count. It may
> be counting the # of rows returned which is slower than the execution of the
> range. Is there any way to turn the RecordCount off? >>
>
> It's not counting, but it does have to set a bit in a bitmap for each row
> that satisfies the range.  That could take some time for hundreds of
> thousands of rows.  

It takes time for a few hundred rows or a couple thousand rows too. When
the range is repeated, the average time to execute is 35ms with dips
down to 15ms. It never goes to 0ms which a competing db can achieve. It
might not seem like much of a difference, but on a webserver it means a
difference of 50 ranges/sec compared to 5,000+ ranges/sec (yes, I did
get the other db's range going that fast after a few hours because I had
record count turned off and the data ended up in the db cache).

> DBISAM uses statistics to get the count without a
> bitmap, but EDB doesn't use statistics so it has to use the bitmap to get
> the count.

What if you didn't need the count? Shouldn't you be able to turn it off
if you needed more speed?

Dave

Roy Lambert wrote:

> Tim
>
>
>
>>It's not counting, but it does have to set a bit in a bitmap for each row
>>that satisfies the range. That could take some time for hundreds of
>>thousands of rows. DBISAM uses statistics to get the count without a
>>bitmap, but EDB doesn't use statistics so it has to use the bitmap to get
>>the count.
>
>
> Are there implications with this for tests like if table.RecordCount > 0 then ..  ?
>
> Should we convert them to if not (table.eof and table.bof) then ....  ?
>
> Roy Lambert

Roy,
   For databases in general, I've always found testing for EOF to be
faster than counting rows because you can stop after the first row is found.

Dave

Roy,

< Are there implications with this for tests like if table.RecordCount > 0
then .. ? >>

No, once the count is calculated, it isn't re-evaluated again unless the
data is refreshed due to other changes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dave,

<< It takes time for a few hundred rows or a couple thousand rows too. When
the range is repeated, the average time to execute is 35ms with dips down to
15ms. It never goes to 0ms which a competing db can achieve. It might not
seem like much of a difference, but on a webserver it means a difference of
50 ranges/sec compared to 5,000+ ranges/sec (yes, I did get the other db's
range going that fast after a few hours because I had record count turned
off and the data ended up in the db cache). >>

Yes, but all of this is only an issue if the web server is running into load
issues.  For most people, 15ms one way or the other is not going to make
that much of a difference.  A file seek that ends up going to disk could end
up costing many times more than that, and there is no way to control for
that with multiple clients/sessions/threads updating/reading data.

<< What if you didn't need the count? Shouldn't you be able to turn it off
if you needed more speed? >>

It's always possible that we could add such a thing, but we have it in there
because the count is usually something that is useful to have.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> << It takes time for a few hundred rows or a couple thousand rows too. When
> the range is repeated, the average time to execute is 35ms with dips down to
> 15ms. It never goes to 0ms which a competing db can achieve. It might not
> seem like much of a difference, but on a webserver it means a difference of
> 50 ranges/sec compared to 5,000+ ranges/sec (yes, I did get the other db's
> range going that fast after a few hours because I had record count turned
> off and the data ended up in the db cache). >>
>
> Yes, but all of this is only an issue if the web server is running into load
> issues.

Correct. I do expect load problems because there will be a lot of
database access via ranges. I will likely have to use ranges because
queries may not execute fast enough.

> For most people, 15ms one way or the other is not going to make
> that much of a difference.

That's true if you have only 5 people on the site. But if there are
1,000 people clicking away, they are going to have to wait almost 7
seconds seconds for a response (if the max range speed is 148
ranges/second-see below). Either that, or I need to get 7 servers
instead of 1.

I've benchmarked Elevatedb ranges and it won't go faster than 148
ranges/second. At first this seems fast until I compare it to other
databases that I've tested. MySQL (improperly tuned) will exceed 1000
queries/second (7x faster) and I'm hoping I can improve on this. The
other database  I tested will do well over 2500 ranges/second (17x
faster). All of these tests ran for several hours and the speed
increased as more and more hits are found in the cache.

Suprisingly it doesn't appear that ElevateDb uses cache because the
Range speed starts at around 140 range/sec and stays there no matter how
long I let it run. I checked the memory consumption and sure enough, the
ElevateDb app does not use much memory at all. The memory useage is
pretty much rock stable without the life of the program and there is no
decline in memory even if the program runs for hours. Is there any way
to assign cache to ElevateDb?

>  A file seek that ends up going to disk could end
> up costing many times more than that, and there is no way to control for
> that with multiple clients/sessions/threads updating/reading data.

In my webapp 99% of disk access for my app will be reads (85% ranges and
14% fulltext searches). Only 1% will be updates but they will be cached
to a memory table and will written out every few minutes so the writes
won't block reads.

> << What if you didn't need the count? Shouldn't you be able to turn it off
> if you needed more speed? >>
>
> It's always possible that we could add such a thing, but we have it in there
> because the count is usually something that is useful to have.

The record count is useful to have only if you reference it in your
code. If you don't reference it, why get penalized for using it?

Dave

Tim,
    I see there is a "Maximum Row Buffer Pool Size" and "Maximum Index
Buffer Pool Size" which may help caching, but EDBM allows me to change
this values when I alter table, but the changes do not take effect. They
are always set to 32k and 64k respectively. I also tried SQL with Alter
Table to change these values and although it says the SQL completed ok,
these buffers are never changed. Is this working, or slated for a later
release?

TIA

Dave