General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>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.
Check 1: try a group by instead of distinct.
Assumption 1: oodles of data have to be checked each time anew, making the disks spin a lot
Assumption 2: in the historical table all the history is kept forever (no periods are deleted <g>)
You might build a data driven period table which caches the previous periods.
Using pseudo syntax since I am too lazy to check the SQL-abilities of vfp8, something like
if !adir(laDir, period_cache_table)>0
create table ...
endif
Select period from historical ;
which are not in period_cache_table ;
group by 1 into cursor T_Tmp
union all (since you cacheTable is already distincted and cursor is distincted exclusion set)
cache and cursor of new periods into new cache table
Should be faster because all the "known" period data don't have to be grouped/distincted if the exclusion check can be made fast (for instance if all historical data is entered sequentially - then an easy check date > max(period_cache_table.inputdate) can bisect your history table with a single across an index). You must look onto tha data [entry patterns] for the most fitting (fastest error free) method for exclusion check.
HTH
thomas
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only