Report performance degredation

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?

Hi GeofS,

Sounds like you need to add a key on the sales invoice line table with the Item category code. As this is not part on the standard keys it will struggle with the filter. I have found this before that more that one filter causes the counting dialog box to appear if there is not a suitable key.

Thanks for the suggestion, Dave. I probably should have mentioned in my description of the issue that there is a key on the table that contains these two fields. It isn’t directly set in this report because we need to access the records via Document No.

SQL Server only uses one index at a time, so if it is using a key for document number, is is spending a lot of time scanning those for values for the other two fields, or vice versa. Instead of specifying two separate indexes, try adding the Document number to the index with the two fields, or, if document number is not the primary key, try adding those two fields to the index that it is using.

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, I’m still puzzled as to why, before combining everything into a single key, it ran very fast with some filter values but slow to a crawl with others. Any ideas?

Because you filtered on fields that were not indexed. Think about it, if you keep your books by title, it is easy to find ‘Catcher in the Rye’ because it’s under the C’s. Try to find a book by J.D. Salinger is more of a task because you don’t keep them by author. SQL Server works pretty much the same (although MUCH more complicated). SQL Server uses indexes to retrieve data, so if data retrieval is slow, there is a high probability that you might need to add an index.

This is one of the reasons why lookup forms can be slow one day and fast the other. Sometimes users filter on fields that are indexed, and sometimes they filter on fields that are NOT indexed. Makes all the difference in the world.

I guess I didn’t frame the question very well. There are only two fields used in this situation, Item Category Code and Season. The performance issue arose with certain combinations of values were used in those two fields. That is, when a single value was entered for either field or both fields, the report ran quickly. When multiple values were entered for either field (e.g., ‘AA|BB’ for Season), the report slowed to a crawl.

I understand that filters work much more efficiently on ‘keyed’ fields. In this case, the filter fields were fixed and, although they were included in a key, that key was not directly referenced by the report. So why was there a such a drastic change in performance when different values were entered for the those fields?

Geoff is this Native Database or SQL?

We’re running SQL2005.

Then I would agree with Daniel.

Basically SQL does not use the SETCURRENTKEY command for getting data, it just uses it to sort the results. In this case the important issue will be the sequence that you apply the filters. With out actually tracing whats going on its all guess work, but it will be somethign like; when you select one filter (it doesn’t matter which), SQL choses an index that for that filter, and returns the data. But when you set the both filters, becasue of the sequence not matching any key, maybe SQL is searching on the primary key so it is reading the entire table into the TempDB and sorting it there, which takes much longer.

You might want to check which updates and hotfixes you have applied, since there could be a fix there for you.

Check Waldos blog for the latest info.

Thanks for the tutorial. I’ll check the blog as you suggested.