Tim Young [Elevate Software] wrote:

> I'm very close to getting the pre-beta completed, and I'd like to get
> some feedback on the use of statistics in the indexes in ElevateDB.
> First, a little background......
>
> Currently, ElevateDB uses statistics in indexes like DBISAM does, and
> these facilities allow for this functionality in ElevateDB:
>
> 1) Accurate row sequence numbers, regardless of the active index and
> even with ranges set (but not filters).  This also means an accurate
> grid scrollbar when there are a) no filters or ranges or b) just
> ranges set on the table cursor.
>
> 2) Exact I/O cost calculations for optimized index scans in queries
> and filters.  This means that ElevateDB can very accurately determine
> if it is more efficient to use a row scan or an index scan for a
> particular filtering operation, be it for JOINs or a WHERE clause.
>
> Now, those two items are significant.  However, the statistics in the
> indexes also present some problems, especially with larger tables.
> They cause write I/O to occur on the indexes in proportion to the
> depth of the index in terms of levels multiplied by the index page
> size, even if only one page was actually updated.  For example, say
> an index tree is 3 levels deep. That would mean that any update would
> incur 4096 bytes per page (default page size) multiplied by the 3
> levels to equal 12288 bytes.  If the index statistics were not used,
> then the write I/O would only be 4096 bytes.  This extra I/O is only
> really an issue when not using transactions and performing individual
> row-by-row updates over several hundred rows or more. Transactions
> keep the pages in memory, so the effects of the extra I/O are
> somewhat negated.  In addition, the index statistics cause the
> indexes to be larger than they would otherwise be, and this causes a
> bit more read and write I/O in general.  And, of course, these
> problems simply aggregate as the tables (and the indexes) get larger.
> They particularly become an issue with multi-millions of rows (10
> mil+).
>
Not really an issue for me since I cache the data with clientDataSets.

--
David Farrell-Garcia
Whidbey Island Software, LLC

Not really an issue for me since I cache the data with ClientDataSets.


--
David Farrell-Garcia
Whidbey Island Software, LLC

Eduardo,

<< What about performance of SetRange and Sql with joins in these situations
? >>

Joins are faster in ElevateDB in general, but it removing the statistics
mainly improves the searches down the index tree and inserts/deletes in the
tree.  It won't do much for the leaf page scans in the index because they
don't include statistics anyways.

--
Tim Young
Elevate Software
www.elevatesoft.com

Terry,

<< But if it will make significant difference in performance for all tables,
increase the robustness of the database, and make it easier to eventually
have full RI, then I would drop them in a heartbeat. >>

Well, it does certainly allow for more esoteric transactional features like
versioning of index keys.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Please don't ever even consider not doing
this!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! >>

Do you mean navigational ?  If so, then no, we won't stop providing
navigational access ever (ever).  Did I mention ever ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Matt,

<< I don't think I understand the problem then! I don't think I've ever used
the logical record numbers as-is. I presume they are actually
still available for bookmarks etc, but that this is just an index thing. >>

If you open a table in DBSYS, check out the record number reporting at the
bottom right.  You'll notice that it increments logically based upon the
position of the record in the table.  If you change the active index, it is
still accurate even though the records are in a completely-different order.
That's basically the logical record numbers.  Woo-hoo !!  Start the parade
!!

--
Tim Young
Elevate Software
www.elevatesoft.com

AJ Marknette wrote:
....
>> Unfortunately, that's something I need in several applications. Some
>> customers don't want a 3-state scrollbar, and it's not possible to
>> turn these grids to "load all records" (I only use DevExpress grids).
>>
>> Ralf
>
> cxGrid *can* load all records. Mind you I wouldn't recommend it with
> anything over 500,000 records.

That's exactly what I ment with "not possible" Some forms must open
instantaneous, and loading 50k records via LAN, blocking the servers NIC for
the rest of the stations for several seconds, is, well, not the best idea.

Ralf

Jose Eduardo Helminsky wrote:
> Tim
>
> After think, and think and think. I have decided an my vote will go
> to:
>
> REMOVE indexes statistics. Performance will be much better (I think)
> and
> the side effects won´t be so bad. IOW, Recno with Ranges and/or
> filters
> will not work any more and instead of we can use RecordId.

That's the problem: I'll have a _very_ difficult time to change all the
applications and discuss with my clients. Yes, a working scrollbar is
important to them, and I don't give a <ping> why. If it's important for
them, then it's important for me.

I vote or "Don't touch". Maybe something like switching off the statistic
calculation for batch inserts and deletes could be useful, only allowed with
exclusive access or something like that. But throwing away functionality the
(ok, my) users demand, for some speed gain...

Ralf

Tim


Oh good - reaches for chequebook to buy ElevateDB.....

Roy Lambert

Ralf,

<< I vote or "Don't touch". Maybe something like switching off the statistic
calculation for batch inserts and deletes could be useful, only allowed with
exclusive access or something like that. >>

Batch updates aren't an issue - it's the row-by-row updates in aggregate
that are the issue.  In a large index, it could be as much as 4-5 times more
index I/O than without the statistics.  That's not exactly a small amount.

<< But throwing away functionality the (ok, my) users demand, for some speed
gain... >>

I understand, but we're not throwing away functionality in DBISAM -
ElevateDB hasn't been released yet and is a new product with different
functionality.  There's a lot more than this that is different from DBISAM,
so if the scroll bars are an issue then you might have a lot more issues
that are bigger than that.  IOW, you might find that sticking with DBISAM
for your existing projects is the best route to go, and then seeing if
anything new coming up might be able to use ElevateDB.  Finally, there is
more at stake here than just a small amount of speed gain.  There are
avenues that we could take ElevateDB without the stats, that we cannot take
ElevateDB with the stats, especially in terms of better transaction models.
There is more than just the immediate future for us to consider.

--
Tim Young
Elevate Software
www.elevatesoft.com