Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using MATCH and BYs

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Using MATCH and BYs
 Login/Join
 
NimaS
Member
posted
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,


7.6.4
 
Posts: 4 | Registered: June 16, 2008Report This Post
FrankDutch
Virtuoso
posted Hide Post
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....




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
PBrightwell
Master
posted Hide Post
You could try sorting both file into the same order as your BY fields.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
NimaS
Member
posted Hide Post
How do you mean? I thought I was sorting using the by fields already.


7.6.4
 
Posts: 4 | Registered: June 16, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Waz
Expert
posted Hide Post
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... ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using MATCH and BYs

Copyright © 1996-2020 Information Builders