Having a lot of different prompts or having a prompt that
displays a lot of entries can slow down the execution of a report. This
document describes methods of improving this.
Resolving the problem
The first, and easiest thing to do is to ensure that your
prompts are retrieving their data from the smallest dataset possible. Thus, if
the same data exists in two different (joined) tables, use the smaller table.
Second, you can try to modify the prompt query to include a
filter that will reduce the amount of data that the prompt has to go through.
Third, try to make your prompts based on key data instead of
text data by setting the use and display options
of the prompt; so if you're prompting on
Customer, you want the prompt Use to be based on Customer #, not Customer Name.
Customer, you want the prompt Use to be based on Customer #, not Customer Name.
Fourth, make sure that the data you're prompting for is indexed.
This is tied to the previous suggestion, because key data is
automatically indexed in most database systems.
Fifth, and the most difficult but potentially most effective
solution is to join many of your prompt queries together. There are a couple of
strategies that might be effective.
If you expand the query to include other fields you can set
multiple prompts to use different parts of the same query. This could be
effective if some of your prompts are exclusive of other prompts.
Alternatively, you can set up cascading prompts. This allows
you to use the selection of one prompt as a filter for another prompt. This is
highly effective in cases where you are selecting increasingly restrictive
fields
(so if you were selecting a product, you'd show a product Line prompt,
and then restrict the query for the product name based on the product line that
was selected). This allows the queries to show less data, which allow them to
run faster.
