Hi,

The product manual sais:

"Limitations to the Optimizer

ElevateDB does not currently optimize multiple JOIN or WHERE expressions joined by an AND
operator by mapping them to multiple columns in an available index."

We use optimized filtering and multiply Master-Detail relationships to support the GUI,
which rely heavily on multi column indexes. Is there any plan to enhance the optimization
here?


Regards,
Leslie

Leslei

AFAIK, the next build will have this feature.

Eduardo

Leslie,

<< We use optimized filtering and multiply Master-Detail relationships to
support the GUI, which rely heavily on multi column indexes. Is there any
plan to enhance the optimization here?  >>

As Eduardo indicated, it's on the list for the 2.02 release, so it will
coincide with the Free Pascal support.  We've also got plans to improve the
execution plans quite a bit so that they are XML and can be run through an
analyzer in order to give optimization tips.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

"As Eduardo indicated, it's on the list for the 2.02 release, so it will
coincide with the Free Pascal support."

Yep, unfortunately there is only 24 hour in a day. I mean no rushing at all.

In my book the WinCE port has priority over this optimization issue, but I am about to
make a decision to build the net next major version of our main products on ElevateDB, and
I must make sure it really fits are needs, so I am bringing up all the issues I need to be
convinced on. I can sense the determination for quality about ElevateSoft, so I am
confident that ElevateDB will be developed to a very good full featured product, but I
need to make serious business decision for now. TBH this one is a “go”-“no go” feature we
cannot live without, so my question is the usual : where does this issue currently lies
in your timetable. Once again, I mean no rushing at all, just collecting the info I need.

Leslie

Leslie,

<< Yep, unfortunately there is only 24 hour in a day. I mean no rushing
at all. >>

No problem.

<< In my book the WinCE port has priority over this optimization issue, but
I am about to make a decision to build the net next major version of our
main products on ElevateDB, and I must make sure it really fits are needs,
so I am bringing up all the issues I need to be convinced on. I can sense
the determination for quality about ElevateSoft, so I am confident that
ElevateDB will be developed to a very good full featured product, but I need
to make serious business decision for now. TBH this one is a “go”-“no go”
feature we cannot live without, so my question is the usual : where does
this issue currently lies in your timetable. Once again, I mean no rushing
at all, just collecting the info I need.  >>

This feature is very high on the list, and will most definitely be in 2.02.
It's been something that we've been needing to do for some time now.  The
tentative schedule for 2.02 is the same as what I told you for FP WinCE
support - early August.  Currently, there is nothing to indicate that it
will be delayed at all.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

"This feature is very high on the list, and will most definitely be in 2.02.
It's been something that we've been needing to do for some time now.  The
tentative schedule for 2.02 is the same as what I told you for FP WinCE
support - early August.  Currently, there is nothing to indicate that it
will be delayed at all."

Thanks, great to here that.


Leslie

Ooops, I meant "Thanks, great to hear that." :D

Tim,

I have some more optimization related questions. What to expect when the following SQL
statements are used? Are/will be these optimized for using available indexes? (I have read
the manual. Is there anything else to know/planned?)


- derived tables   
- views in a join instead of a table
- IN
- BETWEEN
- "<,>,>= ... " in WHERE or JOIN ON   

Leslie

Leslie,

<< I have some more optimization related questions. What to expect when the
following SQL statements are used? Are/will be these optimized for using
available indexes? >>

- derived tables
- views in a join instead of a table

Both of these depend upon whether the derived table or view (internally
implemented in the same way) can generate a sensitive result set or not
according to the rules here:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=17

If they can generate a sensitive result set, then the indexes in their
source tables are exposed directly to the calling query and can be used for
optimization.  If they do not generate a sensitive result set (static), then
the indexes that are available for the calling query are determined by any
GROUP BY or ORDER BY clauses.  If these clauses aren't present, then EDB
will attempt to create an index for ordering the result set that matches the
primary key of the first table in the FROM clause of the view or derived
table.

<< - IN
- BETWEEN
- "<,>,>= ... " in WHERE or JOIN ON  >>

All optimized according to available indexes, including LIKE with wildcards.
However, negating certain operations with a NOT can result in additional I/O
to remove deleted rows from the physical set representation that we use
internally.  However, this is reflected in the cost estimates so as to weigh
the estimates properly.  In a multi-condition WHERE or JOIN clause, this
allows a more efficient condition to be executed first if it results in less
I/O.

I'm planning on expanding the execution plans soon, and they will include a
lot more information on what the optimizer is doing, and suggestions on how
to improve the performance (analysis).  They're pretty good now, but lack
good formatting and the analysis side of things.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thanks for the info.  
Any chance that queries with joins will result sensitive resultsets? If yes, does this
mean that optimisation will use indexes for such queries?

Leslie