buppcpp wrote:

>Were the changes to the indexes in 2.0 suppose to provide more than
>larger indexes?
>
>The reason I'm asking is because some of my queries still run just
>as slow in 2.0 as they did 1.5.2.
>
>Ex. SELECT distinct store_no from mytable; (Does a table scan)
>
>mytable has 2.1 million records with an index on store_no.
>It takes 3.32 minutes to run.
>
>On the database I want to convert from, it takes 15 secs. (Data is
>exactly the same between the two databases)
>
>SELECT * from mytable where store_no > 2; (Uses store_no index)
>
>It takes 3.27 minutes
>On the database I want to convert from, it takes 1.57 minutes.
>
>This is a freshly restored DB.
>
>Will these types of indexing issues be taken care of 2.0, if not,
>when?
>
>I for one would prefer to have the indexing system fixed in 2.0,
>because I think we may need to sit on it for awhile, while 3.0 gets
>all of it's bugs knocked out. Plus, 3.0 will have the same
>problems, if it's not taken care of now, but with the overhead of
>SMP code (for 1 CPU systems).
>
>Thanks
>
>P.S. My restore time was cut in half from 50 mins on 1.5.2 to 26
>min with 2.0 !! (DB Size 2.6 Gig)
>
>System Specs:
> WinXP Pro. SP 2
>2.6 MHZ
>512 MB RAM
>ODSVersion = 11.0
>Charset = NONE
>PageSize = 8192
>PageBuffers = 32768
>SQLDialect = 3
>SweepInterval = 20000
>ForcedWrites = False
>ReadOnly = False
>
>
>
>

I don't know wich database you have used, but FB can't just use indexes
for lookup values, it has to go to the datapage to see if that record
are visible or not to the current transaction.

In Oracle for example, if you have an index on store_no and send a:
select distinct store_no fromMyTable

Oracle will only scan the index pages.

FB will scan the index page and the go to the datapage to see if the
record is or isn't visible by the current transaction. Since there is no
"where" clause to restrict the scan every datapage will be read anyway,
then a natural scan (with no index) will be faster.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 267.2.0 - Release Date: 27/05/2005