Hi guys,

just wanna share thoughts, we're just implementing webfocus with data coming from oracle 9i. in one of our test fex, we have like 21 lookup tables being joined and the main table has roughly about a million record (all tables are indexed and contraints are inforced). we've included atleast a column for each table in our report and running it, it just took forever, it finally painted out the report after and hour or two thought. anyway, there might be steps we're missing, so any idea is really much appreciated.

thanks,

We're running Oracle 9i also with WebFOCUS 5.21 on Windows NT.

You might want to create an Oracle view, if possible, of the tables already joined instead of joining them in WebFOCUS. I've found that is faster and more efficient. Our Oracle DBA creates the views we need.

Are there any parameters for the main table or do you really need a million records in the report?

Regards,
Raelene

Have you tried just passing SQL through WebFOCUS?

Be Sure that you joins are unique and not join to all.

We also use WebFOCUS against Oracle, and I've been told to be very careful of sending off queries without good selection criteria in them as they will take a long time. If you don't use appropriate selection criteria, focus pulls back all the records in all the tables and joins them all together before running your query (this is also true if you use Oracle views for your joins and perhaps pull back a field from each table with only a recordlimit on and no selections). Start your joins with whichever table you can limit the size of the most. For example if you are using one selection criteria which will limit the records from the table to only a few hundred start with that. The most important thing when performing multiple joins on tables is to think carefully about which table most of your selection criteria is against and start with that one, you may need to experiment with altering the order of your joins and selection criteria to really improve performance.

Just as an example, if I run a query against an oracle view we have and use only a recordlimit the query though quicker than yours may take as much as 20 minutes, if I put in one selection criteria to limit the amount of joining, it takes 1 minute!

Regards

Tewy

you can also write a procedure (pl/sql) and call it. The procedure will execute on the Oracle end and if written correctly will greatly ease the performance problems.

Vipul

You could write a small book on this topic, but let me give you some hints. Some of this will echo previous comments.

----------------------
To start off, you need to examine the SQL that's being passed to your RDBMS (the techniques to do this are discussed in the documentation). Does the SQL make sense? E.g.,

- Are you seeing one large query with all those joins, or are you seeing a bunch of smaller queries?
- Are you seeing the appropriate WHERE clauses to limit the selection?
- If you are doing a SUM/WRITE in your report, are you seeing the aggregation in the SQL?

If the SQL does not make sense or if it is not doing what you think it should, then the first place to look is your WebFOCUS code. Certain DEFINEs and/or COMPUTEs will not get passed to the RDBMS and will cause inefficient SQL to be generated. If this is the case, you can create a multi-step report. The initial TABLE FILE should generate efficient SQL and extract the data, and following steps will do any additional processing/formatting.

-------------------
If the SQL makes sense, then the next step is to verify that it is the SQL that is causing the problem, Take the SQL and run it using your RDBMS front end tool (SQL*Plus for ORacle, Query Analyzer for SQL*Server, etc). I can almost guarantee that this SQL will run just as slowly as through WebFOCUS. Oracle RDBMS has a very efficient engine, but I strongly suspect that 21 joins to a million row table is too much. In my experience, even Oracle runs out of steam after about 10 joins. If this is the case, it may make sense to split up the request into multiple statements. Here you have to know your data and your request. For purposes of illustration, let's make 2 assumptions:

1) Let's assume that you don't want a million lines in your final report, instead you want hundreds or thousands.
2) Let's also assume that your lookup tables are fairly small (say hundreds or thousands of rows)

Your report should start off with an initial TABLE FILE against the large table, this initial TABLE FILE should extract only those data rows you want in your final report. You may do some joins in this initial TABLE FILE (especially if they are needed to filter your results). Do a HOLD FORMAT FOCUS on this initial query. Next, do TABLE FILEs against the remaining lookup tables and do HOLD FORMAT FOCUS with an index on the lookup keys. Finally join the initial result set (HOLD FORMAT FOCUS) to the lookup tables (HOLD FORMAT FOCUS with index on lookup keys).

You may have to experiment to get the most efficient performance. E.g., (and I'm just making up these numbers here) maybe you can do 8 joins in the initial TABLE FILE, then join to the remaining lookup tables in groups of 4.

While this may sound complicated at first, after you've done it once it will become obvious. I have used this technique to reduce report run times from hours to under a minute.


EricH

Thank you all for the tips. its been a good week trying out almost all of the tips you guys gave, hurray anyway, i guess the best solution for us will be executing pl/procedures and let the rdbms do the joins instead of letting webfocus do it. we've tested one of our existing pl procedure and got back the data in few minutes. just a thought, worst case scenario if we have to implement our joins in webfocus, a user might click the submit button without even selecting a single selection criteria. am pretty much sure that will certainly happen a lot! so having all our joins ready as a view in our rdbms would certainly ease webfocus - even if a user wont select any selection criteria. we're getting excited in our shop!!! cheers