>>From your description of your slow report I think you need to optimize your SELECT statements. First read about Sys(3054), Rushmore Optimization Level in the Help system and try it out with a few simple SELECT statements.
>>
>>Second, Rushmore works when you have indexes that exactly match the WHERE clauses in your SELECT statements. Assuming that when they pick a date range your users are looking at only a small portion of the 30,000 records you will need to have an index on that date. It must be in exactly the same format as your WHERE clause: If you use
>>
>>... Between(MyDateField, Date()-7, Date() )
>>
>>then your index will need to be on the date field. If you use dtos(StartDate) and dtos(EndDate) you'll need to index on dtos(MyDateField).
>
>I'm taking the opportunity to check my general rule of thumb for slow SQL Selects. Here it is:
>
>If your Select involves more tables, possibly a few joins, pick the largest one of them and select from it into a cursor first. Then work with that cursor - don't do any joins on a large table.
>
>I don't know if anyone was trying this out, but I've had cases which were sped up just by selecting from a large table into a cursor in a select where no other tables were involved (like taking out the needed month out of a few years of history data) and then stuffing that cursor into further selects with joins etc. The speed gain was even five or ten times in some cases.
>
>If someone had similar experience, I'd like to shed some light on the issue. What I'd like to know is whether this is a general rule, or it has its ups and downs, or was I just plain lucky in these cases.

I've seen lots of situations where breaking up a complex query improved performance radically, and the exact situation that you point up - extracting the initial dataset from a large table to a cursor where the working set in the cursor was orders of magnitude smaller than the base table, especially if the selection criteria for the subset involved many fewer tags than might be used in optimizing a single complex query seems to be a good rule of thumb.

Additionally, if there is a single intial INNER JOIN that filters the recordset down considerably (eg SELECT partno, qty, price, disc FROM Items INNER JOIN Invoices ON Items.InvNo = Invoices.InvNo AND InvDate BETWEEN dStart, dEnd), it often can be profitably split away from the rest of the query, especially where the remainder of the query involves lots of OUTER JOINs, function results and aggregations.

I have no detailed documentary proof of this logic, but my thinking on the matter is as follows: VFP, in the process of optimizing the query, will pull down the tags involved in the WHERE and JOIN conditions if they are not already present in VFP's memory space. This uses buffer memory, and VFP seems to favor keeping data from tags when it choose between discarding unreferenced data vs tag entires that are 'clean' (data whose value in memory is accurately reflected on disk.) Dumping 'clean' data is less 'costly' than discarding 'dirty' data (memory that would need to be written to disk before releasing it.)

Our filtered data is treated preferentially, since the cursor is likely to be 'dirty', even if it's initially unreferenced. In addition, since there are no existing indexes on this intermediate result, we probably have had to read in fewer tags from the original table, which would reduce disk I/O if they weren't present already, and would make tag buffers from the original table clean and unreferenced in the current operation if we need working memory.

If any of the remaining parts of the initial query are not optimizable, the payoff is obvious, since we end up probably reading in fewer records from the base table(s). Additionally, the memory needed for forming each intermediate join result that involves the filtered set is smaller by orders of magnitude, since the size of the intermediate work result varies directly as the number of starting elements.

The down side to this is that each operation performed against the intial filtered data set will not be immediately optimizable; it will likely require the formation of temporary indexes prior to joins or optimizable query selections. We need to weigh the cost of creating these temporary indexes against the benefit of using the existing tags of the original table for subsequent optimizable operations.

The benefit becomes even greater if the intermediate result is useful in creating several independent queries (eg a net sales report, a salesmen's commission report, an inventory activity report and a cost of goods sold report may all share the common set of inventory transactions over a period of time, so pulling that subset separately and using it in each report's SELECT, especially when we explicitly build useful indexes.) The more repetitive reference to a given subset, either in the body of several queries, or in establishing reusable subselect sets, the higher the payout.