> Also, do you really need 100 characters for the CATEGORIA column ?  That's
> an awful lot of wasted space, if not.

Hi

I have change structure of my database.

i don't use more the Long field CATEGORIA.

i have add a CODICE_CATEGORIA ( integer Field , indexed , Case Insensitive,
Full Compress )



old filter ( with the long field CATEGORIA ) :

SELECT * from DataOffice2008 where UPPER(REGIONE) = UPPER('NEWZONE') and
UPPER(CATEGORIA) = UPPER('SOFTWARE')

the new Filter :

SELECT * from DataOffice2008 where UPPER(REGIONE) = UPPER('NEWZONE') and
CODICE_CATEGORIA = 1010

( 1010  , is the code number of  'SOFTWARE'  )



but always , slow. partial.



i have try this filters are ALL FAST , Full Optimized

ID = 1210249 and CODICE_CATEGORIA = 1010
ID > 1000000 and CODICE_CATEGORIA = 1010
PROVINCIA = 'NO' and CODICE_CATEGORIA = 1010
Upper(PROVINCIA) = Upper('NO') and CODICE_CATEGORIA = 1010

Upper(PROVINCIA) = Upper('NO') and CODICE_CATEGORIA = 1010 : IS FAST !!!

the problem is in REGIONE field / index.

REGIONE in english is STATE , PROVINCIA is... MAIN CITY.

REGIONE show much records.

but the simple filter :

UPPER(REGIONE) = UPPER('NEWZONE')

work always, well , fast , Full optimized.

database size : 1'300'000 records
REGIONE = 'ZONE1_BIG' ,   250'000 records
REGIONE = 'ZONE2_SMALL'' ,   35'000 records
UPPER(REGIONE) = UPPER(''ZONE1_BIG') and CODICE_CATEGORIA = 1010 ,    4851
records
UPPER(REGIONE) = UPPER('ZONE2_SMALL') and CODICE_CATEGORIA = 1010 ,    550
records




i have found this :

UPPER(REGIONE) = UPPER('ZONE1_BIG') and CODICE_CATEGORIA = 1010
ZONE1_BIG is a big zone , with much records

-> SLOW - PARTIAL OPTMIZED


UPPER(REGIONE) = UPPER('ZONE2_SMALL') and CODICE_CATEGORIA = 1010
ZONE2_SMALL is a very small zone

-> FAST - FULL OPTMIZED


The problem is the number of records of return.

but don't work much well.... in this mode.

on a network drive , take 2 MINUTES for this query. ((

this is a very big for me.

Enrico,

<<  i have found this : >>

Yes, this is what I've been telling you all along - DBISAM will optimize a
WHERE clause differently depending upon the number of rows that are returned
from a given expression.  It does this based upon the I/O costs of each
expression, and DBISAM always picks the execution plan with the least I/O
costs.

<< on a network drive , take 2 MINUTES for this query. (( >>

You're probably not going to be able to put multi-million row tables on a
network and have the performance be good for queries that have to access
large portions of these tables.  You should investigate using the DBISAM
database server instead since it won't have to transfer large portions of
the table and indexes across the wire.

--
Tim Young
Elevate Software
www.elevatesoft.com