Hello,
I am getting poor performance when using a match against a sql 2005 table with 200,000 combined with a sql 2005 table with close to 5 million records. In testing to see where the performance issue is, it seems that if I remove some of my BYs on the MATCH part performance is much better. I cannot remove the BYs as they are required for the users to see. What is another way I can accomplish the match with 23 BYs (it seems to slow down around 12)? I am on dev studio 7.6.4. The BYs slow things down on the first match part and not the FILE part as I have the same BYs in the same order on both parts. Some of my sql fields are indexed but not every single BY - do they all need to be indexed - maybe that would help? Thanks for the help.

This message has been edited. Last edited by: Kerry,

If both relations are on sql why not do a join on the server and do the table report on that view?

But more than 20 BY fields ? Is that really needed?

Indexing on the server would help, but that might be contrary to data entry.
And then what is the sequence of the BY's?
And the length of the BY fields, the longer the slower....

You could try sorting both file into the same order as your BY fields.

How do you mean? I thought I was sorting using the by fields already.

As you increase the number of BY's, you increase the number of distinct records generated (exponentially!). That increase affects
a. retrieval, sorting and summarization within SQL
b. transmission or resulting rows from SQL to WF server
c. accumulation of rows in MATCH FILE (or TABLE FILE) processing
d. performance of the merge part of MATCH FILE

Similarly, if SQL is not sorting the data, or is not reducing the data, performance will suffer.

Put in a statement trace, and check the generated SQL to verify that WF is passing through the sort and summation requirements to SQL (for both of the retrieval legs of the MATCH)

Is the hit primarily in a+b+c or in d: If you just select from each of the two tables (TABLE ... HOLD x 2 rather than MATCH FILE), do you get a similar hit in performance when you increase the number of BY fields?


If the net number of rows resulting from the MATCH FILE is not immense, you may be able to improve performance by permuting the order of the BY fields, without adversely affecting later reporting from the HOLD file.

NimaS, a couple of questions.

Are the number of BY fields for the match, the common ones, 23 or is it less?

Are you matching with a SUM, SUM or PRINT, SUM or PRINT, PRINT. If it is SUM, SUM, this would explain the performance degredation, from less to more BY fields.

If your match is a many to one, reduce your by fields on the many side to the minimum, use PRINT for the many side and SUM for the One side. Not seeing your code doesn't help, but this may reduce the processing.

I have to agree with Frank, why not do a join ? Is it because of the MATCH condition, what is it, an OLD, OLD-OR-NEW, OLD-AND-NEW... ?