Tim


Possibly a silly comment, but you said one reason for not having a switch is the impact on table structures - couldn't we have a .EDBStats as well as the existing three?

Roy Lambert

Tim

My suggestions:

1) How Oracle, SQL Server and MySql (most popular databases in the world)
work with a DBGrid ?
If they work like a three steps scrollbars, then just "follow" then in this
aspect and remove the index stats.

2) As you said, if it is possible to have two options instead of "hard"
remove the stats and like other suggests, IndexStats = True/False. If you
just remove the stats, some people will not see any diference but other
(with large databases) will se a good performance improvement.

This is a difficult decision to make because, I´ve read other threads and
there are a lot of different opinions about it.

Good luck

Eduardo

Tim,

> In ElevateDB constraints always use indexes (except for check constraints,
> of course), hence what you're proposing would require that a user
> duplicate every constraint's index in order to have the logical record
> numbers, i.e. the situation would be worse than just leaving the stats in
> the indexes.
This is what happens in pretty much any SQL server. You will not see the
names
of indexes used for constraints, thus you will not be able to use them, so
if you
need an index explicitly, you should always create one. And yes, the
situation
will be worse, but in those rare cases when you need to have a huge table
you do not use stat indexes. The engine may assume, that constraint indexes
have no stats. I must add that most databases do not allow sequential
access, anyway

> Sure, but my point was that you either allow for the stats to be specified
> during the constraint creation (non-standard) or you do the above, which
> involves duplicating the already-established constraint indexes.  Neither
> of these situations is particularly appealing to me.
I think duplication is what commonly accepted, so I personally see no
problem
with that. If you need a very fast large table, do not create constraints
and only
use non-stat indexes with it.

> << That is why I specifically mentioned that it's an either-or choice.
> Well, I can't help you there . I just think, that having stats is
> good - for my purposes at least, cause I rarely deal with tables larger
> then 500000 records. I think it's important to design the database so it
> can later be extended with additional types of database objects, so I
> think if you could consider having non-stat indexes in a similar way to,
> say, fulltext indexes, that would be the best solution. >>
>
> I understand what you're saying, but the situation is slightly different
> than just a different index type.  Full-text indexes are a different index
> type in the metadata, for example, but they are implemented using the same
> structure as any other index.  The ElevateDB index structure is designed
> to work well with both unique and non-unique keys, and adapt the
> compression, etc. accordingly.  IOW, there is really only one type of
> index used in ElevateDB, and I'd like to keep it that way if I can.
I see. Well, then I could only repeat my voting for not removing statistics.
DBISAM and ElevateDB is first choice for relatively small systems. Having
statistics would create more comfortable enviornment for descktop database
developers.

> Thanks for the input.  I know it's hard to discuss this stuff without
> seeing the product, so I think I may postpone this discussion until
> ElevateDB gets into your hands.
That's a good idea too, but unless having stats vs. not having stats creates
50% drop in speed (which I hope it is not), this discussion is really only a
matter of what you are using the database for.

Anyway, thanks for giving us chance to participate in the discussion.

Regards,
Michael

Tim,

My friend here asks a reasonble question, regarding 2) - if you remove
stats from index, how will you ever determine, that the index should be
used in query optimization, or will you use any index, which contains
selected field? In latter case you may endup with slower query processing,
especially on huge tables, so in fact not having stats might end up more
cost effective anyway.

The other solution is as it is done in Mimer, where the engine temporarily
delays statistics accumilation (so it becomes outdated) and then you may
force it to rebuild stats at once. This however will not help with 1).

Here's the quote from Mimer manual:
-------------
Update statistics includes an automatic operation which ensures the
consistency of secondary indexes (both explicitly created indexes and those
created by the system when certain constraints are defined). The operation
is transparent to users of the database and is performed on indexes selected
by the UPDATE STATISTICS statement that are contained in a databank with the
TRANS or LOG option and which are flagged as `not consistent'.

The process of ensuring the consistency of an index, and updating statistics
for all tables in the database (the default operation), can be rather
time-consuming. Therefore, it is generally recommended that these operations
be performed in batch mode and at off-peak times, refer to the Mimer SQL
System Management Handbook, Database Statistics for more information.

A secondary index is flagged as not consistent if it is contained in a
databank with the NULL option or if the databank containing it has been
upgraded from Mimer SQL version 7 or 8.1.

-------------

In other words the best of both worlds I think can be achieved by:
1. Keeping statistics in index
2. Delaying update of staistics in case of large insert block (this can be
done by user explicitly). In this case
user will not be able to use sequential access until stats are updated
3. Force rebuilding stats manually at the end of operation.



Michael

+1 more...

Jerry Hayes wrote:

> > Remove the statistics, I don't use data-aware UI controls anyway,
> > they are  problematic. Using C/S exclusively, I run my queries,
> > fill my UI controls,  and close the query.  Go in Get what you need
> > Get out
>
> +1


> Security, Integrity, Stability, Speed are absolutely first tier.  UI
> can be tweaked based on the queries, mem data sets, etc.


--

Eeek - only just seen this, and I don't like the way it is going. I bought
DBISAM because it is a nice DB for writing friendly applications with. And
that means the scroll bar on a grid should be accurate. The
top/somewhere/bottom scroll bars are horrible to use. I feel strong enough
that I'd never use ElevateDB if it didn't do this.

But more importantly, how about a data-purpose? I have a multi-threaded
analysis app that I'm working on at the moment, and it has to do various
complex selects and then process each of the rows. I need to know at the
start of each process how many rows there are to process since I've got to
allocate memory etc. I can't do this dynamically, so I'd have to go count
and that could add a lot to the overhead of analysing (the database
read/write portion already takes over 50% of the time). The data to
analyse can be anywhere from 30 rows through 100,000 rows.

IMO, the few people with millions of rows of data shouldn't be your main
concern. Sure it would be nice to speed it up, but what percentage of your
customers would benefit? What percentage would lose? I'll bet more people
depend on this than are aware of it. It will have a significant impact on
the UI of many apps.

Finally, if you take it out, you will have a hard time putting it back in.

And really finally, what do your main competitors have with this? I looked
at the online help for one and frankly I can't work out if it has accurate
stats or not (lots of "??todo" items!), but don't give away a major
feature and benefit of your database(s). Please!

/Matthew Jones/

> 1) How Oracle, SQL Server and MySql (most popular databases in the
> world) work with a DBGrid ?
> If they work like a three steps scrollbars, then just "follow" then in
> this aspect and remove the index stats.

What I like about Delphi is that I can make great apps easily using great
components. Why should we step back in user experience?

/Matthew Jones/

Chris,

<< Go ahead and remove the stats - one never knows when one's going to need
the boosted performance, and if it's not there when you do need it, you end
up feeling you chose the wrong DB. >>

That's the big downside to the stats - they're great for most things, but if
you need to have big tables, then they start getting in the way.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Possibly a silly comment, but you said one reason for not having a switch
is the impact on table structures - couldn't we have a .EDBStats as well as
the existing three? >>

As a separate index type, or simply as a file where statistics are stored
and collected 'ala an UPDATE STATISTICS command like other database servers
out there ?  If it's the latter, then that won't help with the logical
record numbers, but it would allow for more accurate cost optimizations
(provided that the stats are kept up to date).

--
Tim Young
Elevate Software
www.elevatesoft.com

Eduardo,

<< 1) How Oracle, SQL Server and MySql (most popular databases in the world)
work with a DBGrid ? >>

They don't even deal with the client-side apart from the transport layer and
shuttling SQL results back from the server.   Most client-side libraries for
those database servers use static caching to allow for accurate scrollbars,
such as what ADO and ADO.NET do.

<< This is a difficult decision to make because, I´ve read other threads and
there are a lot of different opinions about it. >>

Indeed, it is a very difficult decision.  The easiest solution for me would
be to just keep things the way they are and save myself some time and
effort.  However, that is not always in the best interests of the product.


--
Tim Young
Elevate Software
www.elevatesoft.com