This may be a question that is better posed to tech support, but hey, I thought I'd start here first anyway.

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

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.

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.

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.

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

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.

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!

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?

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

Putting the data in a flat hold file is no different than a FOCUS file without indexes and you save yourself some overhead.

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

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

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)

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.

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.

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.

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.

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.

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.

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!

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.