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