Tore and I have already mentioned indexes, and Tore has explained why it helps to create a variable.

However, there are some additional factors you may want to keep in mind here (please look up Rushmore optimization in the docs if this isn't sufficient help.)

1. Optimization will take advantage of whatever indexes are available, but your request must be formulated correctly. Make sure you put the expression or field on which you have indexed on the LEFT side of the comparison operator, and the variable on the RIGHT side.

2. Your multiple conditions seem to be connected with AND operators, rather than OR, and this is a Good Thing <s>. It means that the first condition which evaluates to .F. will stop the evaluation process for a given record.

This being the case, you should make sure that the first condition is the one that is going to filter out the majority of the records you don't want in the result set.

For example, suppose one condition is a customer number and the second is a type of transaction. Your system has 20,000 customers and 3 types of transactions. If you say custno = m.ID AND transtype = m.Type, and if you have indexed on both values, then the first condition eliminates all but the records for that customer, and the second condition only has to be applied for 1/20,000 of your table. If you say transtype = m.Type AND custno = m.ID, then FoxPro has to compare 1/3 of your table against the customer number. Do you see this?

3. SELECT statements (SQL WHERE clause) can take advantage of Rushmore as well, so you might look into setting your initial filter up by creating a temporary cursor rather than working on the original file. However...

4. Rushmore isn't the only factor to consider <s>. It seems to me that you might benefit from putting some of your actions together in a single statement and then using this result, whether using SELECT or CALCULATE. For example, instead of:

     Count To t
     Count For category="P" To p
     Count For category="A" To A
     Select 6
     Replace tattend With t
     Replace pattend With p
     Replace aattend With t-p

... you could try this, with no FOR clauses:

    CALCULATE COUNT(1), ;
               SUM(IIF(category="A",1,0)), ;
               SUM(IIF(category="P",1,0)) TO m.t, m.p, m.a

    SELECT 6

    REPLACE  tattend With m.t, ;
             pattend With m.p, ;
             aattend With m.t-m.p

... notice that the CALCULATE statement is going to make a single pass through the table where you were doing 3 passes.

Now, it is true that if you had an index on the Category field, your FOR clause on the COUNT statements were going to take advantage of that index. However, to get your first COUNT you were already going through the whole table, with no filters. Therefore you should leverage that initial pass to get all three of your values at once.

If you used a SELECT for the initial filter, you would have the added benefit of not even needing to calculate the full count; you could get that from the RECCOUNT() of the cursor <s>. Obviously, if the report is using the table in question, you can set the report up to use this cursor instead, too.

FWIW, I also used a single REPLACE statement rather than three. This is not as essential as what I've pointed out using about CALCULATE rather than COUNT; however, it is better practice and somewhat faster, considering that you are issuing the statement multiple times in a loop.

As I look back over your code, I notice some other things I don't understand (the COUNT TO T appears to give the same result as the COUNT TO C and I'm not sure I see where the COUNT TO A is being used; perhaps it is in the report). But, HTH, anyway,


>L<