 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Table with 4.8 Million Records
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Table with 4.8 Million Records Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Table with 4.8 Million Records | Login/Join |
| Member |
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.( ON TABLE SET HTMLCSS ONThis message has been edited. Last edited by: Kerry, 7.6.4 OS is Windows HTML, Excel, and PDF | ||
|
| Expert |
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 | |||
|
| Expert |
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 | |||
|
| Master |
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. | |||
|
| Virtuoso |
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 | |||
|
| Virtuoso |
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
| |||||||||
| ||||||||||
| Virtuoso |
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 | |||
|
| Expert |
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... TomThis message has been edited. Last edited by: Tom Flynn, Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Virtuoso |
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.
| |||||||
|
| Guru |
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 | |||
|
| Expert |
I would also change the request to TABLEF, assuming the sort conditions are passed to the rdbms. May speed things up a bit. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
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 | |||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Guru |
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 | |||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Table with 4.8 Million Records
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Table with 4.8 Million RecordsCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |