Hi,

I have a database +/- 2.600.000 records.
I have 5 indexes on this file.
One of them is on ProductID, another is on TransactionDate.
The file has been uploaded in order of ProductID.
When I do a select ProductID, count(*) where productId = 80 - I get after 2
seconds the result (11.000)
When I do a select on TranscactionDate, count(*) where
TransactionDate=Current_Date() - it takes more then 3 minutes (result =
14.000)
When I select another date .. it's going fast (as long as the index is in
cache). When I reboot the computer it takes again more then 3 minutes.

Now I unloaded and uploaded the file again according to the TransactionDate
index.
Now the select on the TransactionDate is really fast (2 seconds) and the
select on the ProductID takes more then 3 minutes.
How is this possible - can this be solved ?
Are indexes working in Pervasive SQL2000i ???

Regards,

Roland

I have to ask, just what exactly is "unloaded and uploaded the file
again"?

The other question I have is, does the data file have keys matching
the SQL indexes? A check database run will let you know if the
indexes match or not. If not, they can not be used by the SQL engine.

To see exactly how the database engine is optimizing the query (or
not) you may want to run the query plan viewer.

Leonard

On Sun, 8 Aug 2004 22:00:26 +0200, "Roland Wolfs"
<r.wolfs (AT) rw-software (DOT) com> wrote:

Leonard wrote:

I'm guessing an export to text ordered by a given key and then
a reload. BUTIL and all that.

Always a good question.

I'm wondering if the database has to read each record of if it need
only scan the index structure. If it need only scan the index data
then I see no reason for the difference in speed. If however each
record has to be read to check it matches the selection criteria then
I can see they there might be a difference.

When scanning by the index which matches the load order all the records
for a given key value are going to be contigious within the file. This
means that very little disk head seeking needs to be done. However when
scanning by an index which does not match the load order then the record
will be spread through the file and a lot more seeking will be needed to
read each record to check it matches the selection criteria.

Even if the actual records don't need to be read a similar thing might
be occuring with the way the different indicies were built at load time.

After that I assume we are seeing the effects of caching.


--
Guy
-- --------------------------------------------------------------------
Guy Dawson I.T. Manager Crossflight Ltd
gnues (AT) crossflight (DOT) co.uk