Tim,

You are working on better use of indexes.

The scenario is this:

Index with two columns,  coloumn 2 is descending. Will this index be used in optimization
the same way in every situation as if it had only  ascending  columns? Does using
descending fields in indexes have any disadvantege?


Regards,
Leslie  

Leslie,

<< Index with two columns,  coloumn 2 is descending. Will this index be used
in optimization the same way in every situation as if it had only  ascending
columns? Does using descending fields in indexes have any disadvantege? >>

No, descending sorts on columns in indexes do not have any bearing on SQL
optimizations.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<No, descending sorts on columns in indexes do not have any bearing on SQL
optimizations.>>

Great.

What about primary indexes? EDBManager curently does not allow using descending fields there.

Regards,
Leslie

Leslie,

<< What about primary indexes? EDBManager curently does not allow using
descending fields there. >>

What is the question, exactly ?  The indexes that are used for enforcing
constraints operate under the same conditions as any other non-text index.
If you're asking whether primary keys will allow for descending column
specifications, then the answer is no.  Primary keys are constraints, not
indexes.  They simply use indexes to accomplish their job, and descending
column sorts have no relation to constraints.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< What is the question, exactly ?  The indexes that are used for enforcing
constraints operate under the same conditions as any other non-text index.
If you're asking whether primary keys will allow for descending column
specifications, then the answer is no.  Primary keys are constraints, not
indexes.  They simply use indexes to accomplish their job, and descending
column sorts have no relation to constraints.>>

Constraits automtically create the index they need with ascending fields. If I  understand
you right,  replacing this index manually with some  descending fields would  have no
effect on the constraint or speed.

Regards,
Lesie    

What I meant is that EDBMamager  automtically creates  the index required by the
constraints with ascending fields...

Regards,
Lesie    

Leslie,

<< Constraits automtically create the index they need with ascending fields.
If I  understand you right,  replacing this index manually with some
descending fields would  have no effect on the constraint or speed. >>

What do you mean by "replace" ?  Do you mean trying to modify the
system-generated and maintained index for the constraint ?  If so, then that
is definitely out of the question.  Once we start opening up these indexes
to modification, we're really opening up the engine to all sorts of issues.
Plus, we really do not want to start giving everyone the idea that a
constraint and an index are the same thing, when they are most definitely
not.  If you need different sorts on the columns, then you should just add
another index with the desired column sorts.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<If you need different sorts on the columns, then you should just add
another index with the desired column sorts.>>

I can obviously create the indexes, just tried to optimize index creation by using the
same index for the constraints and display order.

For normal PC-s  having one more index is a none issue, but for WinCE apps speed and
memory usage is much more of an  issue... higher level optimization may needed there.


<< Do you mean trying to modify the system-generated and maintained index for the
constraint ?  If so, then that
is definitely out of the question.  Once we start opening up these indexes
to modification, we're really opening up the engine to all sorts of issues. >>

The engine can easily be protected against tempering with the indexes by checking the
existence of the indexes required by the constraints and creating the ones which are
missing at start up.     

<<Plus, we really do not want to start giving everyone the idea that a
constraint and an index are the same thing, when they are most definitely
not.>>  

Anyone with basic understanding of SQL knows the difference. I believe the users of EDB
are professional programmers, so there is no point worrying about this one.


Thank you for your answer,
Leslie

Leslie,

<< For normal PC-s  having one more index is a none issue, but for WinCE
apps speed and memory usage is much more of an  issue... higher level
optimization may needed there. >>

EDB will only use the amount of memory you tell it to use for the index
buffering, with the default being 128k.  The number of indexes doesn't
affect this figure.

<< The engine can easily be protected against tempering with the indexes by
checking the existence of the indexes required by the constraints and
creating the ones which are missing at start up. >>

You use the word "easily" in reference to the development, but we have to
consider much more than just development.  These are the type of changes
that can really cause support inquiries to increase quite a bit.  And, for
the record, we can't just "create indexes on the fly" on a live table
without potential locking issues in a multi-user environment.

<< Anyone with basic understanding of SQL knows the difference. I believe
the users of EDB are professional programmers, so there is no point worrying
about this one.  >>

Many database engine products out there, including DBISAM, confuse the two,
so I would not be so sure about your conclusion.

--
Tim Young
Elevate Software
www.elevatesoft.com

I support Tim on this - I'm confused

Roy Lambert