Hi Walter,

Your (and Thomas') suggestion is much faster than a "Select Distinct ..." but still takes some time for a large master table. To get almost instant response, "Index On...Unique" is the only way to go.

Create 2 indexes in advance:

Index On Period Tag Period
Index On Period Tag UPeriod Unique

To get access to the unique period in the application, issue the following command:

Use TheTable Again Order UPeriod Alias TablePeriod	&& Get the unique period records

The problem of a unique index is when the key value of a records which was picked up by the index during indexing get deleted, the key value will completely remove from the index even though the same value exists in other records (which were not picked up during indexing). Therefore, in order to make this solution work, the following statement must be issued whenever a record is deleted:

nPeriod=TheTable.Period
Delete In TheTable	&& Tag UPeriod may be invalid after this delete command
If seek(m.nPeriod,'TheTable','Period')
    Replace Period With 0 In TheTable 
    Replace Period With m.nPeriod in TheTable	&& Force tag UPeriod to update
EndIf

Ben

>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.