Speeding Data Access

[wendas]

I have been battling the speed of a few reports.
Tables are from a third party package, so the keys and table layout I have no control on.
I have about 7 tables linked and the filtering is primarily on one that is over 600,000 records and it seems that with the fields filter it wants to read every one.

But I can filter on another table that will reduce the pull to about 10,000 records pulled.

Problem is, when using the crystal filter, it seems the first, second, third item filtered is not my choice. I arrange filter and exit and everything gets rearranged. If the smaller file filter is first it runs fast, but it really prefers to put the slower table filters first..

Is there a to force the filter to keep your arrangement. Is there differnt way then what I am attempting to say first filter this, then filter that?

Any other suggestions greatly appreciated.

 

[lbass]

Do you mean it is being rearranged in the Show SQL query area? What version of CR are you using? You could also copy the query into a new report in the command area and then arrange the query as you like. You could also potentially move some clauses in the where clause into the from clause area to speed up the report.

-LB

 

[wendas]

Crystal version 8.5.. I don't think it has a SQL anything.. Just a filter.. The filter re-arranges thing.

Also the third party data is btrieve based not MS SQL based. Other reports with databases MS SQL based we just build a stored procedure in SQL and then pull that in.

We have a newer version of Crystal v 10.. But there is something about these programs that the software drivers can't be updated to run on the drivers the newer Crystal needs.

I am playing around with sub reports, to try to force what is filtered first or second. But that still has some perplexing problems with getting the groupings right.

The quicker group will pull in based on being within the range of dates requested. The second group needs to sort and group on the per day.. So I need to force group 1 to reset and repull for how many days the report asks to look at.

 

[lbass]

You are limited by using 8.5. I'm not sure it will make a difference in rearranging the query that is actually used (go to database->show sql query to see what query is generated by your record selection formula), but instead of using the select expert, go directly to report->edit selection formula->record, and enter the selection criteria there. This might prevent the rearranging.

-LB