Report performance degredation

GJSHUGER

I recently modified a custom report to add an Item Category Code filter on the Sales Invoice Line table. The filter value is entered on the Option tab of the request form and is set in the code using SETFILTER() if a value is entered. This is the same technique used for providing the original Season filter on the report.

When either filter is used by itself, the report runs as it should. However, when values are entered for both filters, the report takes much longer. For example, a report that runs in under 10 seconds with either individual filter takes over 10 minutes with both.

I've confirmed that there's no extra report code being run. The extra time is taken during the retrieval of certain records. Does anyone have any idea why?

ara3n

What keys do you have on the table?


Can export the object as text?

GJSHUGER

There is a key that contains the two fields, Item Category Code and Season. It isn't set as the current key in this report because the records need to be retrieved using their Document No. values.

Yes, I can export it as text. Would you like to see it?

ara3n

Sure. Are they on sql or Native?

GJSHUGER

We're running SQL2005 and Navision 4.0

ara3n

Do you know where the key Item Category Code and Season is used for?
If it can be changed
I would add to the beginning of the key Document No. and use that key.

GJSHUGER

Thanks, Rashed. I'll see if we can modify or add a key to deal with this.

GJSHUGER

Using a key with all of the filter fields as well as the Document No. seems to have done the trick. Thanks for the suggestion.

However, there was already a key with the two filter fields set up. Why wasn't that enough to handle the situation? And why would it work fine with some filter values but not others?

ara3n

SQL server is thinking that "Document No." key was faster because sql statement would be Sort by "Documentn No."