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,

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.

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.

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.

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.

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 ...

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.

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,

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.

Try this
/*
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET WEBVIEWER ON
?*

This will return one page at a time.

I would also change the request to TABLEF, assuming the sort conditions are passed to the rdbms.

May speed things up a bit.

Tom,

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

Hey Rick,

THANKS!! Memory faded a bit with that column.

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

Tom

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

Do you have any index fields on the Table ? If yes, are they identified and are you using them as BY fields ?

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.

Thank you for all your help. I am able to make it work now.

Steven