Hi stephen,

I guess the tranaction table is quite long. It is scanning the whole table to get those values. Adding an index in general does not help for SQL.

If there are a lot of duplicate periods and the number of distinct periods is limited, You can speed up het whole process by using xBase commands. It is more work, but does not require so much bandwidth and that currently is slowing down.

The basic idea is a follows (Code not tested)

CREATE CURSOR Result (Period I)
SELECT HistoryTable
SET ORDER TO Period 
SET NEAR ON
GO TOP
nPeriod = Table.Period
INSERT INTO Result VALUES(nPeriod)
DO WHILE !EOF()
   =SEEK(nPeriod + 1)
   nPeriod = Table.Period
   INSERT INTO Result VALUES(nPeriod)
ENDDO

The idea is that it would skip a whole chunks of duplicate periods.

If you are not deleteing from this table, you can also use a unique index and set the order to that and scan trough it.

If the periods are months for exaple and there are not to many distincs periods posible, you could also enumerate through the posible periods and use a KEYMATCH() or SEEK() to see whether that period has been recorded.

HTH,

Walter,



>>A client reported a form taking it's time to display. I replicated the issue today. And it's a report form that lists periods you can select for a financial report.
>>
>>I did some research and prior to the form displaying, a "Select Distinct Period..." is run on a historical transaction table to find all periods that data exists for. This appears to be what's slowing the display down.
>>
>>I added an index on the period column, but that doesn't have any affect. Is there any way I can optimize this, or a quicker way I can come up available periods?
>
>What are your periods and how are you defining them? Is it a column of datetime, or is it an int and you have to combine the year for 2004-05
>
>I have done this for cross tab f'n reports for years, so I know the "joy" of the period.
>
>I take the date field and 4 digit the year +'-'+ 2 digit the month as a column of field list. You can group on it up the wazoo as well as sort the heck out of it.