Many prompt queries make the report slow, how do I speed it up

    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.

    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.