I am trying to hold data I retrieved using sqlora. If I just run the sql it returns in seconds, however when I add
TABLE
ON TABLE HOLD AS NEWDATA FORMAT ALPHA
END
it takes over 2 minutes to return. Does anyone know why? I query is returning approx 180,000 rows

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

TABLEF (as opposed to TABLE) would speed things up: It avoids the process of forming an internal matrix containing all the 180,000 incoming rows; instead each incoming data row is immediately emitted as a row of the HOLD file, which potentially cuts the data-handling overhead by two-thirds.

First try changing TABLE to TABLEF:

TABLEF FILE SQLOUT
PRINT *
ON TABLE HOLD AS NEWDATA FORMAT ALPHA
END

If that is rejected [I am unsure whether the keyword TABLEF is allowed in this context], change it back to "TABLE" and add
SET AUTOTABLEF=ON before the SQLORA request. That should cause the TABLE request against SQLOUT to be treated like TABLEF, since you are not requesting TABLE to sort the data.

Of course you can and should first test on a smaller subset of the data.

I guess I should be a little clearer in what I am doing. Below is the code
SQL SQLORA
SELECT FA.QTY_N, FA.QTY_G, FA.COMP, FA.FAC_NO, FAH.AREA,
SUBSTR(FA.YEAR, 1,4) || SUBSTR(LPAD(FA.PER_NO, 2, 0), 1,2) YRMO,
FA.ACCOUNT || FA.SUB_FEAT ACSF,
TO_CHAR(GP.PER_START, 'YYYYMM') CALPERIOD,
SF.USER_CODE_01 SFCODE1
FROM TABLE1 FA,
TABLE2 GP,
TABLE3 GH,
TABLE4 GGD,
TABLE5 SF,
TABLE6 FAH
WHERE FA.COMP = GP.COMP
AND FA.YEAR = GP.YEAR
AND FA.PER_NO = GP.PER_NO
AND GGD.GROUP_CODE = '&COMPANY'
AND GGD.COMP = FA.COMP
AND GH.COMP = GGD.COMP
AND GH.COMP = FA.COMP
AND GH.COMP >= ' '
AND (TO_CHAR(GP.PER_START, 'YYYYMM') >= '&SELCALPER') AND (TO_CHAR(GP.PER_START, 'YYYYMM') <= '&SELCALPERA')
AND GH.OG_COMP = FAH.COMP
AND FA.FAC_NO = FAH.FAC_NO
AND GH.GL_COMP= SF.COMP
AND FA.ACCOUNT = SF.ACCOUNT
AND FA.SUB_FEAT = SF.SUB_FEAT
AND SF.SUB_FEAT >= ' '
AND SF.COMP >= ' '
AND SF.ACCOUNT >= ' ';
TABLE
ON TABLE HOLD AS NEWDATA FORMAT ALPHA
END
-RUN
if I put an exit in following the semi-colon it runs in about 1 second, otherwise it takes over 2 minutes.

Laura

Laura,

It takes 1 second because it doesn't do anything, from WebFOCUS.

Have you tried:

 
SQL SQLORA PREPARE SQLOUT FOR
SELECT FA.QTY_N, FA.QTY_G, FA.COMP, FA.FAC_NO, FAH.AREA, 
SUBSTR(FA.YEAR, 1,4) || SUBSTR(LPAD(FA.PER_NO, 2, 0), 1,2) YRMO,
FA.ACCOUNT || FA.SUB_FEAT ACSF, 
TO_CHAR(GP.PER_START, 'YYYYMM') CALPERIOD, 
SF.USER_CODE_01 SFCODE1
FROM TABLE1 FA,
TABLE2 GP,
TABLE3 GH,
TABLE4 GGD,
TABLE5 SF,
TABLE6 FAH
WHERE FA.COMP = GP.COMP
AND FA.YEAR = GP.YEAR
AND FA.PER_NO = GP.PER_NO
AND GGD.GROUP_CODE = '&COMPANY'
AND GGD.COMP = FA.COMP
AND GH.COMP = GGD.COMP
AND GH.COMP = FA.COMP
AND GH.COMP >= ' '
AND (TO_CHAR(GP.PER_START, 'YYYYMM') >= '&SELCALPER') AND (TO_CHAR(GP.PER_START, 'YYYYMM') <= '&SELCALPERA')
AND GH.OG_COMP = FAH.COMP
AND FA.FAC_NO = FAH.FAC_NO
AND GH.GL_COMP= SF.COMP
AND FA.ACCOUNT = SF.ACCOUNT
AND FA.SUB_FEAT = SF.SUB_FEAT
AND SF.SUB_FEAT >= ' '
AND SF.COMP >= ' '
AND SF.ACCOUNT >= ' ';
END
-RUN
TABLE FILE SQLOUT PRINT *
ON TABLE HOLD AS NEWDATA FORMAT ALPHA
END
-RUN

You also have 5 INNER JOINs? Why wouldn't it take a couple of minutes.

What does it actually take when running the SQL by itself on ORACLE?

It "looks" like you need to create this as an aggregate table and use WebFOCUS to report from that...

Tom

Tom,

Actually, it's the WebFOCUS server that returns the data extracted by the data server to the web browser, so I wouldn't agree with "It takes 1 second because it doesn't do anything, from WebFOCUS". Why it should take a different amount of time to display the results on a web browser versus saving in a file on the server I can't explain.

Also, what's wrong with "5 INNER JOINs?" - I have dozens of WebFOCUS programs that generate SQL like that, taking from 1 second to 10 minutes to run, so the performance problem cannot be the number of inner joins.

I tried using the TABLE FILE SQLOUT PRINT *
code from above but it didn't work.

My problem is not that is doesn't return the data but it is how long it is taking. By using -EXIT at several different locations, I was able to pinpoint where in the code the delay was. It happens when trying to save the data to a WebFocus table. When I run this same query using TOAD, it takes less than a second.

Thanks for trying to help.

Laura

Laura, I understood from your original posting that it returned the data. Putting a -EXIT before the end of the code probably does not make the code run at all - you should be getting messages.

E.g., putting a -EXIT here will cause an error, because WebFOCUS expects an END statement (and that's why the program will take one second to run - it isn't doing anything except displaying an error message:

SQL SQLORA
SELECT FA.QTY_N, FA.QTY_G, FA.COMP, FA.FAC_NO, FAH.AREA,
SUBSTR(FA.YEAR, 1,4) || SUBSTR(LPAD(FA.PER_NO, 2, 0), 1,2) YRMO,
FA.ACCOUNT || FA.SUB_FEAT ACSF,
TO_CHAR(GP.PER_START, 'YYYYMM') CALPERIOD,
SF.USER_CODE_01 SFCODE1
FROM TABLE1 FA,
TABLE2 GP,
TABLE3 GH,
TABLE4 GGD,
TABLE5 SF,
TABLE6 FAH
WHERE FA.COMP = GP.COMP
AND FA.YEAR = GP.YEAR
AND FA.PER_NO = GP.PER_NO
AND GGD.GROUP_CODE = '&COMPANY'
AND GGD.COMP = FA.COMP
AND GH.COMP = GGD.COMP
AND GH.COMP = FA.COMP
AND GH.COMP >= ' '
AND (TO_CHAR(GP.PER_START, 'YYYYMM') >= '&SELCALPER') AND (TO_CHAR(GP.PER_START, 'YYYYMM') <= '&SELCALPERA')
AND GH.OG_COMP = FAH.COMP
AND FA.FAC_NO = FAH.FAC_NO
AND GH.GL_COMP= SF.COMP
AND FA.ACCOUNT = SF.ACCOUNT
AND FA.SUB_FEAT = SF.SUB_FEAT
AND SF.SUB_FEAT >= ' '
AND SF.COMP >= ' '
AND SF.ACCOUNT >= ' ';
-EXIT
TABLE
ON TABLE HOLD AS NEWDATA FORMAT ALPHA
END
-RUN

You should be getting an error message.

If you code this:

SQL SQLORA
SELECT FA.QTY_N, FA.QTY_G, FA.COMP, FA.FAC_NO, FAH.AREA,
SUBSTR(FA.YEAR, 1,4) || SUBSTR(LPAD(FA.PER_NO, 2, 0), 1,2) YRMO,
FA.ACCOUNT || FA.SUB_FEAT ACSF,
TO_CHAR(GP.PER_START, 'YYYYMM') CALPERIOD,
SF.USER_CODE_01 SFCODE1
FROM TABLE1 FA,
TABLE2 GP,
TABLE3 GH,
TABLE4 GGD,
TABLE5 SF,
TABLE6 FAH
WHERE FA.COMP = GP.COMP
AND FA.YEAR = GP.YEAR
AND FA.PER_NO = GP.PER_NO
AND GGD.GROUP_CODE = '&COMPANY'
AND GGD.COMP = FA.COMP
AND GH.COMP = GGD.COMP
AND GH.COMP = FA.COMP
AND GH.COMP >= ' '
AND (TO_CHAR(GP.PER_START, 'YYYYMM') >= '&SELCALPER') AND (TO_CHAR(GP.PER_START, 'YYYYMM') <= '&SELCALPERA')
AND GH.OG_COMP = FAH.COMP
AND FA.FAC_NO = FAH.FAC_NO
AND GH.GL_COMP= SF.COMP
AND FA.ACCOUNT = SF.ACCOUNT
AND FA.SUB_FEAT = SF.SUB_FEAT
AND SF.SUB_FEAT >= ' '
AND SF.COMP >= ' '
AND SF.ACCOUNT >= ' ';
END
-RUN

The data will be returned to the web browser window.

When you run the query using Toad, does it return the 180,000 rows in one second?

I don't get an error when I put the exit following the semi-colon, I guess that is because SQL doesn't need to use END.

Yes, all 180,000 rows do get returned, but it only shows the first 500 at a time.

It takes awhile to write 180K rows. Maybe you have slow disk. If you reduce the number of rows, does it run faster?

At any rate, it is something to investigate.

(solved) I've summarized the rows before returning them and it is faster, so I will go with this solution.