Greetings,
It has been a long time since I have posted. I really like this new format. It will be really beneficial compared to outdated LISTSERV technology. Anyway, (warning - this is going to be a brain dump so my request may jump all over the place) I have a request that goes after about five different SQL tables, quite a bit of data. It takes about 4 minutes to come back in WebFOCUS, which is not big deal on a scheduler at 3 in the morning or deferred but not good when being accessed via the web in a self service application.

Using:
SET TRACEOFF=ALL
SET TRACEON=STMTRACE
SET TRACEUSER=ON

I can trace the four (correct by the way, my JOINS are efficient) SELECT statements and then put the SQL statements (generated from WebFOCUS) right into Query Analyzer on SQL 2000 tools and the entire request takes about 18 seconds. WebFOCUS = 4 minutes, SQL = 18 seconds. (Hopefully your wheels are spinning..not stuck like mine. This can't be just formating or network traffic.)

There are about 185,000 rows. So, how do I speed things up without having to bother the SQL DBAs. For instance, a great workaround we know is a SQL DBA can create a SQL Stored procedure (by the way, we do this and it works great)

Example of syntax:

SQL SQLMSS SET SERVER

SQL SQLMSS
EX sp_WF_storedprocname
'&&COUNTRY', '&&SEATS';

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END

-or-
of I simply use SQL Passthru and it works great using SQLOUT.

So, I guess my question is as follows, what can I do to super charge my request and keep it in WebFOCUS code(is it pulling one row at a time or why doesn't my SELECT recognize aggragation of data? Is WebFOCUS doing all the work?) Or, do I break up my requests in SQL Passthru. If I do that, am I limited to the one result set in SQLOUT or do I do a bunch of passes with various SQLOUT tables held differently then slam the holds together for my final report. I have been around a long time, mainframe to client/server to Web, I know I should just read the manual. But I guess I am looking for help and besides, I wanted to thank the IBI for this cool new forum.
Regards,
-Ben

When you generate output through an SQL procedure, is it styled in HTML? The issue may not be retrieval of the data, but the formatting of all the data into HTML, for display by WebFOCUS. What happens if you SET ONLINE-FMT=STANDARD, turing off the HTML styling, so only the unstyled data is returned?

Art,
I have been following the FOCWIZARDs like you for years, thanks for your efforts, quite valuable even though you are not thanked enough AND as you taught us long ago, don't waste a post with a one liner public thank you. With that in mind, I know you can't do much without seeing the code but all it is is simple retrieval WITH FOCUS DEFINES and SORTING (by the way, you taught many of us when to use CAPS or not too). Bottom line, there are two main parts to the focexec and my issue is when I can't send more criteria and I have to pull back the world. But..if it takes 18 seconds for SQL to pull back the same data. All I am doing is, pulling data, defines, sort, HOLDA. Pulling data, defines, sort, HOLDB. Joining the two, format PDF, done. Interestingly enough (and I have switched to a similar focexec for testing):
1) I can run this request as is: 67 Seconds
2) With the SET ONLINE-FMT=STANDARD: 67 Seconds
3) (WAG) Since I still develop inside the MRE I thought, I would try to wrap the two biggest chunks of data pulling in -MRNOEDIT BEGIN and -MRNOEDIT END and: 67 Seconds

I really don't want to do the whole: "Ask the D(wee)BA for a view thang" (by the way, for any reporting person like me one of the commandments right after "Know your data" should be "Be nice to your DBAs.")

There are always options - I just can't think of any and welcome any input. Thanks,
-Ben
p.s. I must be old school since I didn't know "Graemlins" were legit.

Ben,
Thanks for the kind words. Let's hope I can deserve them here.

I didn't realize you were producing PDF output, so SET ONLINE-FMT won't matter (I thought it was HTML output). I'm guessing that the difference between the original 4 minutes and the 67 seconds was due to the tracing being removed. Is that so?

As for the difference in retrieval time between Focus and SQL (67 seconds vs. 18 seconds), let's make sure we're comparing apples to apples. How are you producing the PDF output with SQL? There is an overhead in translating the output to PDF. Typically the files are larger (with all the control information), so data transmission takes more time. Then, you have to load the reader to SEE anything. So, are we comparing the same thing in both cases?

One of the things that I do to speed up WebFocus reporting against SQL tables is to use TABLEF to retrieve the data, including any WHERE statements that SQL can understand to bring back fewer rows and eliminate any DEFINEs, EDIT's and anything that may turn optimization off. Keep the request simple, put the data in a hold file, then do all your fancy formating, defines, calculations, etc. against the HOLD file. If the fields your sorting BY are indexed on the SQL table, then use TABLE and let SQL do the sorting for you. It's very helpful to look at the trace and see the SQL is being generated. I'm assuming WebFocus against SQL tables is similar to Focus against DB2 tables. Someone please correct me if I'm wrong. It's possible your DBA may need to add an Index for you. Good Luck and hope this helps!

Art and Carol,
Thanks for the replies. Art, first, I switched gears and used a different request for the whole 67 seconds thing, that was 13 weeks. The other 4 minute one was for 13 months of data.

Carol, the solution was exactly as you stated, I was just being lazy. I did three 'bare bones' FOCUS retreievals in my focexec(s) and then did all the funky formatting, defines etc. As I sit now, the 13 week one that took 67 seconds is down to 6 seconds. The 13 month 4 minute one is down to 40 seconds. So, I got to show this to the DBA who says 'FOCUS is slow' that...no...you have to know how to code and sometimes more code is better.

Thanks everyone, great forum,
-Ben