Filepro SQL Indexes

[AlanC]

I am running Filepro on a SCO Unix platform.  We have one particular database that has never been purged and is extremely large.  Our programmer is working on a purge routine, but I'm not sure how long that is going to take to implement.

Any time I attempt to write a report out of this module, it takes forever to run because it always has to sort through every record in the database.  The report I am trying to write now goes on a monthly basis, but it still looks through the entire database before returning the requested records.

Are there pre-written indexes that can be used so my reports don't have to run through every record in the database every time I run the report.  If so, how do I access them or view them to see what they are?

If not, how do you create an index for a particular database, or can you?

Any feedback would be much appreciated.

Alan

[kenbrody]

fPSQL will use whatever indexes you have built on the files, if they are appropriate.

For example, if you have the query:
    select * from clients where state = 'NY'
and you have an index built on "state", fPSQL will use that index to go directly to the "NY" records.  If there is no such index available, then it must read the entire file.

What is the query you are running, and what indexes are built on the referenced file(s)?

[AlanC]

Hi Ken.  Thanks for the quick response.

This is my query:

select stoordra.@4, stoordra.@3, stoordra.@12, stoordra.@61, stoordda.@5,
       stoordda.@6, stoordda.@7, stoordda.@8

from stoordra, stoordda

where stoordda.@23 = stoordra.@1 and
      stoordda.@22  ='018012' and
      stoordra.@12 >='04/01/09' and
      stoordra.@12 <='04/30/09' and
      stoordra.@47 <> 'Y'

stoordra is our Filepro database of archived orders and stoordda is where the detail (line items) for each order are stored.  Since I am using field ID #'s instead of actual names, I'll explain my selection criteria.  I am trying to list all of the orders (including line items) for customer #018012 between a range of dates (the date ranges will change from month to month).  stoordra.@47 <> 'Y' excludes canceled orders.  stoordda.@23 = stoordra.@1 establishes the common field between the parent/child database (in this case order #).

If I understand you correctly, if there is an index built on order date, it would still have to sort through all customers, but it could go straight to the first date in the date range and only run through the dates in the defined date range.

Or - if there was an index built on customer ID, it would have to sort through all line items in the defined date range, but only for customer 018012.

Am I understanding this concept correctly?  Could the order in which I am listing the criteria have anything to do with how fast the query processes (i.e. how many records it has to sort through)?

How do you determine what indexes exist for a particular Filepro database?

Thanks.

[AlanC]

Hi Ken,

I changed my selection criteria to search for a range of Order dates instead of Invoice dates and the query runs in about 15 minutes.  Not great, but acceptable - certainly better than all weekend.  Also, not exactly what I wanted but it will have to do (hopefully the customer requesting this data will feel the same way).

Apparently our programmer has built an index on order date but not on invoice date and is hesitant to build any more indexes than he already has because the file is so big.

In any case, I'm all set for now.

Thanks for your help!

Alan