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] Table with 4.8 Million Records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Table with 4.8 Million Records
 Login/Join
 
Member
posted
Hi all,

I have a table with 4.8 mil records. I just do a simple report to display all rows but the report is not coming back. Do you see anything wrong with my code below? What can I do to make it run faster and return an output?

Thanks,
Steven

JOIN
ROALOGM.ROALOGM.ROALOGM_PIDM IN ROALOGM TO MULTIPLE
SPRIDEN.SPRIDEN.SPRIDEN_PIDM IN SPRIDEN AS J0
END
TABLE FILE ROALOGM
SUM
ROALOGM_USER_ID AS 'USER ID'
ROALOGM_ACTION_IND AS 'ACTIVITY'
ROALOGM_ACTIVITY_DATE/HYYMD AS 'ACTIVITY DATE'
ROALOGM_PIDM NOPRINT
BY ROALOGM_AIDY_CODE AS 'AIDY'
BY SPRIDEN_LAST_NAME AS 'LAST NAME'
BY SPRIDEN_FIRST_NAME AS 'FIRST NAME'
BY SPRIDEN_ID AS 'ID'
HEADING
"FINANCIAL AID ARCHIVE"
FOOTING
"Submitted by: <+0>&IBIMR_user"
"Date: <+0>&DATEtMDYY<+0> <+0> "
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
WHERE SPRIDEN_ID EQ '&NEW_ID';
WHERE ROALOGM_AIDY_CODE EQ '&NEW_AIDY';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON

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


7.6.4
OS is Windows
HTML, Excel, and PDF


 
Posts: 29 | Registered: November 19, 2008Report This Post
Expert
posted Hide Post
First of all, it doesn't appear that you want to retrieve all the records (which is a good thing) because you have WHERE clauses in your request.

Second, you need to do a trace on your code. Put these commands at the beginning of your program:
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

and either have the Dev Studio message viewer on or produce the output in HTML and do a view source. You will see the SQL generated. Post that with any messages.

You also didn't say what the data source types is/are, i.e. RDBMS, flat, etc. If you are doing a mixed join, that would be another reason for the slowness of the report.

So get us that information and we'll be better able to help you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
If, after your exclusion filters, 3 million rows are returned, don't expect the report to display in your web browser window - it will be too big for the web browser.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
try to use a
WHERE RECORDLIMIT EQ 1000;
or some other number smaller than 4 million. If it returns quickly than the problem is the shear amount of data it's retrieving, not the performance of the query itself.

If it takes a long time just to return 1000 records, I would look into indexing ROALOGM_PIDM ROALOGM_AIDY_CODE
SPRIDEN_PIDM SPRIDEN_CHANGE_IND, SPRIDEN_ID
depending on what database your running, it would be a good idea to create a bitmap or EVI index over the SPRIDEN_CHANGE_IND as you're looking for nulls.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Virtuoso
posted Hide Post
My first thought was along the lines of Francis' comment. That many rows are not going to return to a browser (or otherwise) in any sort of record time. Your browser will hang before it fits that many records, that's about 53000 pages in PDF, and I don't know if even the new Excel 2007 can handle that many records.

Second thought, as I looked at you environment, is that you have WF on a Window box but are accessing DB2 on an AS400. Is this working in a hub-sub setup with a data adapter on the AS400 or exactly how is that working? Hub-sub especially when joins are used, it just not very efficient.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
quote:
you have WF on a Window box but are accessing DB2 on an AS400

Darin, That's Jason's env, not kingfish's. Of course you're right, but not neccessarily in kingfish's case.

And as all the other replys agree upon, that many records is not going to display at all in the browser. You can either follow the other suggestions (putting in recordlimit and such) but if you really really really do want or need al these records to show up in the browser, then there is only one way to do it, and that is to use the webviewer. Simply activate it by saying ON TABLE SET WEBVIEWER ON.

Hope this helps ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Oopsy!

I was trying to hurry and answer that one before running off to a meeting and looked at the wrong post. Kingfish did not list a DBMS so anyone's guess. One thing that I did notice now going back and looking at the original post is that the two first selection criteria are on fields in the cross-referenced table which is not going to buy much efficiency. However, there is a single criteria on the host file - depends which one is the most restrictive. If that is one of the first two, you may want to try reversing the join. You could verify efficiency by creating SQL traces like Ginny suggests.

That being said, your first problem is how to display that many records. Refer back to previous posts by me and Francis. GamP's suggestion would be an excellent solution if that works for you. I personally dont use the web viewer much, but its primary purpose is to deliver smaller data sets, a piece at a time, back to the user.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
This looks like SCT Banner, Oracle warehouse, using the GUI and 1 pass to go against SPRIDEN. I am not familiar with the ROALOGM table.

Since you have
  
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
WHERE SPRIDEN_ID EQ '&NEW_ID';


Spriden_ID is the alpha of SPRIDEN_PIDM.

I would change this
WHERE SPRIDEN_ID EQ '&NEW_ID';
to
WHERE SPRIDEN_PIDM EQ &NEW_ID;

Also, SPRIDEN_CHANGE_IND is an ALPHA field, "usually" with MISSING=ON.

I would change this
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
to
WHERE SPRIDEN_CHANGE_IND EQ ' ';

Hope this helps...

Tom

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
If I look at your fex you will not expect 4.8 mil records.
You are doing a sum on the user_id, activity, a date field and a non printed ID.
Your by fields all come from the other table.

Your where fields will bring the result back to only one record output as far as I can see this.

The performance problem must be something else.




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
Guru
posted Hide Post
Try this
/*
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET WEBVIEWER ON
?*

This will return one page at a time.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
I would also change the request to TABLEF, assuming the sort conditions are passed to the rdbms.

May speed things up a bit.


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
Platinum Member
posted Hide Post
Tom,

FYI the SPRIDEN_CHANGE_IND is a NULL field. Checking for blanks or '' will not find the record.


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Expert
posted Hide Post
Hey Rick,

THANKS!! Memory faded a bit with that column.

Kingfish seems to have left the building, anyway...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Member
posted Hide Post
Hi all,

Thank you very much for all the responses.

I tried RSquared's suggestion (ON TABLE SET WEBVIEWER ON) and it took about 10 minutes to run but it did come back.

I will try SQLOUT to see if it runs faster since it returns in a few seconds in TOAD.

Thanks
Steven


7.6.4
OS is Windows
HTML, Excel, and PDF


 
Posts: 29 | Registered: November 19, 2008Report This Post
Guru
posted Hide Post
Do you have any index fields on the Table ? If yes, are they identified and are you using them as BY fields ?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
As previously mentioned, set XRETRIEVAL off and set SQL traces on. This will provide the SQL generated by your WebFOCUS along with messages if the generated code is inefficient.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Thank you for all your help. I am able to make it work now.

Steven


7.6.4
OS is Windows
HTML, Excel, and PDF


 
Posts: 29 | Registered: November 19, 2008Report 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] Table with 4.8 Million Records

Copyright © 1996-2020 Information Builders