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