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     Performance Question regarding writing hold files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Performance Question regarding writing hold files
 Login/Join
 
Platinum Member
posted
This may be a question that is better posed to tech support, but hey, I thought I'd start here first anyway. Smiler

I'm evaluating the performance of one of my reports. It's a pretty complex little report and has the tendency to return a lot of data. I've done some testing today and learned that a lot of time is being spent writing the hold files. I'd like to see if there are similarities at other sites and/or if you all have figured out ways to speed writes up, etc.

As an example, given specific parameters, this report takes approximately 1:45 minutes to run from the time I hit the button to the time when the results are on-screen. The report runs a stored procedure, holds the results to a user specific hold file, and then displays the results on-screen.

In my example -- the first 30 sec is spent running the stored procedure on SQL Server that returns about 68k rows. The 2nd 30 seconds is spent writing the FOC$HOLD.FTM file -- which turns out to be about 150MB. The 3rd 30 seconds is spent writing my actual hold file (via APP HOLD xxx) that is specified in my code -- which turns out to be about 180MB. The final 15 seconds is spent (I'm assuming) in the final TABLE FILE request against that hold file and preparing the results for display on the screen.

Here's my question -- does 30 seconds each seem like a long time to write those hold files? (it does to me). Also, is there any way to avoid the intermediate step of creating that FOC$HOLD.FTM file? It seems somewhat inefficient to write that temp file and then turn right around and copy the contents of that into another final hold file. Even a straight copy of a file that size is much quicker. Now, I'm sure there's more going on that just a straight copy -- obviously the size is different. But if it's possible to shave 20-30 seconds off of this report, that's a REALLY big gain in my mind.

I had to create that final hold file as an XFOCUS file because of the enormous size that it can reach (our users like to run this report for massive amounts of data). But I didn't see that have any material affect on the performance.

Obviously I can focus some attention on the procedure -- but returning 68k rows is going to take at least some time, regardless of how much tuning I do on the SQL. But then again, if I can't affect the time it takes to write out 2 hold files, that may HAVE to be my focus...

I know this is a very general question, but beyond these points, is there anything else obvious that I should be looking at to speed this process up?

Thanks!!



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Virtuoso
posted Hide Post
Is it necessary to build your last hold file every time, or could that be done once a day (night) it would maybe save 1 min 30 sec each time, and you might be able to structure that hold file better, with more smart indexes.




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
Platinum Member
posted Hide Post
Unfortunately the point at which that hold file is produced is after the user selects their parameters. This is a highly parameterized report, so there really is no way to predict what they're going to ask for. I know what you're getting at and I've had some great reports in the past that I could do this with -- unfortunately this one is not one of those.

Regarding structuring the hold file better -- short of reducing the amount of rows or columns, do you think that simply changing indexes and such would allow it to generate any quicker? I guess I just assumed that given the same amount of data it would generate in roughly the same amount of time -- which is why I was sort of looking at that intermediate hold file that was appearing.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Expert
posted Hide Post
Check your report in 764 as I see you are testing it. The FOC$HOLD is not the default anymore. See if that helps at all.

A couple of other ideas. It appears that the output file is FOCUS or XFOCUS. Are there indexes? If no, ignore the rest of this post.

If yes, are they necessary? If yes, hold the data in a sequential file, do a CREATE FILE on a master with indexes but load the data with a master with no indexes. Then do a REBUILD INDEX.

If the file has no indexes, you might want to leave it as a flat hold file.


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
Platinum Member
posted Hide Post
Thought I had turned into an old Fool, out of the maket (Which is true too). But I can see that my problems of the 90's seem to still be Problem of the 3rd Millenium.
Decision support need proper contract with the User. Either you were able to Build Proper 'Visualisation Data Bases', at night, and thus you can answer, on line.
Either not, and then you say 'Sorry, it'll take some time, the process needs to be turned in our Back Office, we send you the results when it's ready'
Cordialement and Focusly
PS : Of course, you've a mean to contact our beloved Client, when the Job is done


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
 
Posts: 134 | Registered: November 06, 2007Report This Post
Master
posted Hide Post
When you are getting the results from your SQL, are you using TABLE or TABLEF. I thought the FOC$HOLD was created as ouput from the internal matrix. TABLEF doesn't use the internal matrix.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by GinnyJakes:
Check your report in 764 as I see you are testing it. The FOC$HOLD is not the default anymore. See if that helps at all.

A couple of other ideas. It appears that the output file is FOCUS or XFOCUS. Are there indexes? If no, ignore the rest of this post.


Good suggestion, I'll check out 764 -- and this particular file does not have any indexes.

Baillecl, unfortunately I live in a world where that's not really an acceptable response and I envy those that work in an environment that it is... For what it's worth, a major portion of the value of our BI system would be lost by doing this as well as it contradicts the original bet.

jgelona, I THINK I tried it both ways already and got the same results, but I'll have to check it again.

Lastly, thanks to all the suggestions so far. I also checked in with our server folks and some of the slow write times may actually be server related and they're going to move me to a different storage array and virtual machine to see if that helps (which appears that it might based on their tests). I'd still like to eliminate that extra file if I could though, assuming that it nets an increase in performance.

Thanks for the help so far. If anyone else has any ideas to entertain, I'd be grateful!



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Expert
posted Hide Post
A couple of other questions:

- Does the hold file have to be FOCUS? Can it be just a normal fixed hold file? There might be less to format with that.

- I've not done anything with stored procedures, but can you go right from the stored proc to the report like you can with passthru without creating the intermediate file?


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
Platinum Member
posted Hide Post
Appears that TABLEF still produces the FOC$HOLD file.

I think you might be able to return the results directly from the SQLOUT file, but in my case I need those results for later drilldowns, so that won't work in this situation.

It looks like removing the focus format eliminated the FOC$HOLD file though. I'll have to do a bit more testing to see if that's going to work, but at first glance it seems to be okay. I'll check performance and size limitations...



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Expert
posted Hide Post
Putting the data in a flat hold file is no different than a FOCUS file without indexes and you save yourself some overhead.


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
Platinum Member
posted Hide Post
Well, I was really excited by the performance increase until I realized that I couldn't drill back on that hold file multiple times. So, apparently it is different in some respects.

Is ON TABLE HOLD AS XYZ FORMAT FOCUS the only way to hold a file that can be drilled on in a separate process?

A quick background:
The procedure runs, holds the output to a file, and displays the results. If the user keeps the report open, they can then drill into the results further and bypass running the stored procedure over and over. This is a HUGE performance advantage -- but is the FOCUS format the only thing I can do this with? It appears that way at the moment...



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Expert
posted Hide Post
Trav,

When you say that the SP returns around 68K rows, are all these rows shown in the report or are they just so that you have a held file containing all the possible drill down requirements?

If this is the case could you not just return the summary info from one SP and then have a separate SP for the drill down - which would usually result in less returned rows for a drill down item than the original, and bulky, detail data file originally?

This might not assist if your tables have a high modify rate and you require a temporary "snap shot" of your data tables, as the drill down data might not match what the summary reported.

If you can't speed up the storing of the hold files I would be inclined to look at a method of reducing the rows returned if at all possible.

Good luck,

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Trav
SET DIRECTHOLD = {ON|OFF}
is the way to declare whether foc$hold is created, or bypassed.
make sure your Directhold=on
(reference: Creating Reports, page 402)




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
Trav,

I'm assuming that you are permanently saving the hold file in some directory via an APP HOLD or an APP FI (FILEDEF). That would be the only way to make it available to subsequent drills.

Then, because it is a flat file, to use it in a drill program, that program must know about the file via an APP FI.

Of course, you also have the issue of multiple people trying to run the program at the same time with different selection criteria. Folks here do that with a special directory they create to store the file for that user and put a time limit on when it can be deleted by the system.

I'm sure that there is a way that you can do this. There might be other posts that describe methods that you can use for temp/perm directories.

Tony's idea has merit as well.


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
Platinum Member
posted Hide Post
quote:
Originally posted by susannah:
Trav
SET DIRECTHOLD = {ON|OFF}
is the way to declare whether foc$hold is created, or bypassed.
make sure your Directhold=on
(reference: Creating Reports, page 402)


Looks promising, but it must be a 7.6.x feature as it did nothing in 7.1.6. I still need to do some testing in 7.6.4 on this one.


Tony, the 68k rows was actually just an example, but basically yes, the user has full control over what they run and get and then that file is held in a temp directory (user specific) so they can continue to drill further and further into the details. I know what you're saying, and I've contemplated that idea -- but my reservation is that instead of spending the time up-front on the retrieval, now we're spending more and more time each time the user drills -- and we're basically hitting the same source. It's a grey-area for sure -- but at this point, I'm leaning more towards making the user wait a little while up-front to get their initial results and then having the drills be very fast (comparably speaking). My other thought here is completely go the route of analysis services where everything is pre-aggregated (aka OLAP cube). Then you're just retrieving the aggregated values which should be very fast.

There are 2 problems with this approach (that I can think of) and that is it would pretty much require rewriting the way this report works and it also requires re-architecting the database model.

Ginny, yes I am "permanently" saving the hold file, but doing it in a user specific directory (via app hold). My problem was though that I couldn't get it to drill into it even after the app hold.

Here's a snippet of what I'm doing:
APP MAP GMTEMP c:\temp\&IBIMR_user
APP PREPENDPATH GMTEMP
APP HOLD GMTEMP
ENGINE SQLMSS SET DEFAULT_CONNECTION kmssqlprod1-bireportprod
SQL SQLMSS
-MRNOEDIT EX Gross_Margin_Report '&BUID', '&ITEM_GROUP_CODE', etc.;
TABLEF FILE SQLOUT
PRINT *
ON TABLE HOLD AS GROSS_MARGIN FORMAT FOCUS
END


That's the initial retrieval. Then when the report runs again from a drill-down (this is a recursive drill-down procedure), it skips the proc execution and assumes we're still using the "GROSS_MARGIN" hold file. If I simply remove the "FORMAT FOCUS" from that line, it runs quicker, but then I can't hit it again on my subsequent drill-down. Maybe I'm not defining the path to the file properly, but the fact that it works with FOCUS and nothing else led me in another direction... Long story short, the report works as expected unless I remove the "FORMAT FOCUS".

Again, I appreciate the ideas so far...but it looks like I may need to continue my 7.6.4 testing first to see if we can get there sooner rather than later. Smiler



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Expert
posted Hide Post
Trav,

If you put an APP FI or a FILEDEF in the drilled-to program to point to the file in the 'user temp' directory, then I think your problem will be resolved.

Don't give up just yet. Try that.

If you say ON TABLE HODL AS GROSS_MARGIN FORMAT ALPHA to create the file in GMTEMP, then in the drill-to program you can say:

APP MAP GMTEMP c:\temp\&IBIMR_user
APP PREPENDPATH GMTEMP
APP FI GROSS_MARGIN DISK GMTEMP\gross_margin.ftm
TABLE FILE GROSS_MARGIN
etc.
END


Could you try that and see if that works?

And, BTW, DIRECTHOLD is a 7.6 feature and ON by default.


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
Master
posted Hide Post
I know it seems drastic, but I'd suggest analyzing the entire process from the beginning.

If you can get around the use of a stored procedure and use a view (or better yet, materialized view, not sure if these are available on ms sql yet) then you can create the data you need without having to call a stored procedure to build it.

Without knowing exactly what your stored procedure is doing, I can't say that this solution is valid. It sounds to me, however, that your stored procedure is writing a tremendous amount of data to disk in temporary tables or something of that nature, which is I/O intensive, or that it hasn't been properly optimized.

If this were me, I'd contact my dba and have them run a monitor to see exactly what your sql is doing on the database side, and make an attempt at optimizing the code in the stored procedure.

If it is possible to turn the stored procedure into a view, I'd go that route. Different where clauses will direct the optimizer to different indexes, and you will see a large performance increase. Plain and simple, if that stored procedure is modifying a table, it also has to modify any indexes built over that table, a view does not.


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
Platinum Member
posted Hide Post
quote:
Originally posted by GinnyJakes:
APP MAP GMTEMP c:\temp\&IBIMR_user
APP PREPENDPATH GMTEMP
APP FI GROSS_MARGIN DISK GMTEMP\gross_margin.ftm
TABLE FILE GROSS_MARGIN
etc.
END



That didn't seem to work. It basically acts as if the table was empty (number of records in table= 0).

Since it's alpha, I can see the data if I just open the file -- but WF sure doesn't want to access it after the fact. Unless it's something obvious I'm missing, I don't think I'm going to lose much sleep over it since it appears that 7.6.4 ultimately behaves the way I want anyway and will actually mean I don't need to change any code. We're getting close to turning on 7.6.4.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Jason K.:
I know it seems drastic, but I'd suggest analyzing the entire process from the beginning.

If you can get around the use of a stored procedure and use a view (or better yet, materialized view, not sure if these are available on ms sql yet) then you can create the data you need without having to call a stored procedure to build it.

Without knowing exactly what your stored procedure is doing, I can't say that this solution is valid. It sounds to me, however, that your stored procedure is writing a tremendous amount of data to disk in temporary tables or something of that nature, which is I/O intensive, or that it hasn't been properly optimized.

If this were me, I'd contact my dba and have them run a monitor to see exactly what your sql is doing on the database side, and make an attempt at optimizing the code in the stored procedure.

If it is possible to turn the stored procedure into a view, I'd go that route. Different where clauses will direct the optimizer to different indexes, and you will see a large performance increase. Plain and simple, if that stored procedure is modifying a table, it also has to modify any indexes built over that table, a view does not.


Jason, in this situation, I actually see the stored procedure as an advantage. The reason I say that is that I can do a whole lot more processing on the database with a lot more efficiency than I could in multiple steps in WF. Also, because it isn't coming straight from a table (or set of tables) and there are some intermediate steps, processing, calculations, etc. that need to take place, a view wouldn't really fit the need here. The exception might be that if I could do all of that preprocessing at night and place the final results in a table. However, this particular report is beyond that in complexity and the amount of possible data that can be analyzed. Obviously, it's difficult to explain the entire situation in a short post like this, so I guess you'll have to take my word for it. Smiler

Also, for what it's worth, the I/O bottleneck was definitely not on the SQL side but rather on the WF side... That's why I was focusing my attention on those hold files.

Regardless, I appreciate the input!



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Master
posted Hide Post
I'm in the process of rewriting a bunch of stored procedures I created before I discovered the sql case/when/else syntax.

Just hoping that this would be the same type of situation.


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Performance Question regarding writing hold files

Copyright © 1996-2020 Information Builders