I have a report that runs in the BI Portal but uses SQL pass...

[Steven Hall]

What is the final output ( ie HTML, Excel, PDF )

[Subbarao Desu]

Make sure in you adapter settings the number of records to fetch at a time. In that same there will be SQL optimization options as well. Verify if your driver connecting to the DB (adapter connection) is latest.

Coming to the report level. Verify how many records WebFOCUS is fetching from DB before getting your final data set. Make sure aggregations are done at DB level by avoiding unnecessary DEFINE fields, Make sure less HOLD files and try to use appropriate functions

[Debra Waybright]

Final output type is selected by the user running the report, either HTML or Excel.

Subbu those are great suggestions. I will check into the settings. For the one report I am working on now, there are no defines (other than choosing the output format) or holds.

Thank you!

[Steven Hall]

I agree with @subbu. Make sure that the SQL is optimized. This should be easy since you are just using a SQLOUT. Do any defines and aggregations on the database server.

Try outputting the report as ON TABLE PCHOLD FORMAT JSON and see if there is any improvement. This will show you if the hangup is in processing the Excel or HTML.

We ran into an issue yesterday that the Excel processing was making the server lag, and if two people ran the same report at the same time, our server CPU spiked to 99% and took forever. We are now using a JSON output to load the data into Excel. That took it from 3-5 minutes to ~30 seconds

[Debra Waybright]

I have a report that runs in the BI Portal but uses SQL pass through. In MSSMS it runs in 11 seconds, but in WebFOCUS it takes over 20 minutes! Im looking for hints on how to improve the WF run time.

Thanks!

[Debra Waybright]

The JSON output format didnt improve run time even a little.

Our FETCHSIZE is 100. Im thinking that should be higher, but how do I determine what it should be

Thanks,

Deb

[Debra Waybright]

@subbu - we found that we have MS SQL Server OLE DB 2008 adapter configured, but it is talking to SQL 2016. How do we update that adapter Can you give me direction or should I reach out to IBI tech support Thanks!

[Manoj Chaurasia]

I have used FETCHSIZE of 5000 and seen big improvements

[Subbarao Desu]

@debra.waybright What is the version of the WebFOCUS you are currently using I know the earlier versions of the adapaters used to have the SQL version but the latest WebFOCUS versions doesnt show the SQL versions on the adapter name. When you right click on the adapter there will be a prerequisites option that will take to the documentation on how to update the driver. Not sure if ODBC is an additional license but you can configure and compare the performance against OLE and ODBC adapters.

As @chuck.wolff mentioned you can easily set the fetch size to 5000. You can still check with IBI for better assistance

[Debra Waybright]

We are on 8.2.03. Thanks for all the info. I will let you know where we land!

[Toby Mills]

Hi Debra!

Performance problems are nebulous for sure. They can be lots of things.

I wouldnt worry too much about your the way youve written your SQL Query for now. WebFOCUS shouldnt take too much longer than MSSQL using the Management Studio.

To narrow things down after you already know your MSSQL speed is way faster then WebFOCUS, it helps to first remember all the places that the data flows through to render the final report.

Lets just assume the SQL Passthrough has been handed to MSSQL successfully and start with what happens next:

 

 

Communication delay possible - the data flows from MSSQL back to the WFRS (short for WebFOCUS Reporting Server)

 

 

Typically the output is written to temp area called SQLOUT on the WFRS. Ive not really seen much of a delay at this point by the WFRS

 

 

Core FOCUS Code delay possible -The WFRS then does whatever your core FOCUS code tells it to do (like DEFINEs, COMPUTES, BY phrases for Sorting etc). Here - you might be asking WebFOCUS to do some fancy things in sorting and summing. So this could be a source of slowness but probably not. Note: at this point the data is still in temp work files on the WFRS and has not been sent back to the Client (tomcat) or formatted for real output yet.

 

 

Next comes the output writer phase. At this point, the WFRS has all the info lined up and it needs to get your report actually formatted up - like it might add HTML tags or decide its got to do some fancy maneuvering to create an Excel or PDF file. Communication and Processing Delays can happen right here.

 

 

Data flows from the WFRS -> Client - Depending on report output format, either the WFRS will create the final report before sending it back to the Client (Tomcat) , or, by default for Excel, the rows of the data in the WFRS will start flowing over to the client (Tomcat) for more processing. In other words, either the already completed, formatted report goes over the wire to the Client, or else the data goes over the wire with instructions to tell the Client its got work to do.

 

 

5a) Optionally, the Client has to format the XLSX or Graph request. By default, the client gets this workload dumped on it. Thats fine if your tomcat instance has a lot memory to work with. If you were watching your Client machine, you might see that Tomcat is super busy. Theres a way to tell the WFRS to do the work instead. Ill talk about that in a minute. On with the flow of data.

 

Report output starts landing in your Browser - In MSSQLMS, when the rows come back to you - it really only pulls a few rows back and it shows you those, while the rest of the rows are being buffered up. So in MSSQL, the answer may look much faster, but really - you didnt get to see all those rows right With WF, whether your answer is landing in a Web Browser (like HTML output) or an Excel file is going to open, you have to wait until your PC can render the whole report. So this might be something to think about.

 

Now - youre report has finally been delivered - lets talk about things you can do to narrow down where you roadblock is.

First, lets see how your speed is regarding steps 1 and 2 above. Try swapping out your PCHOLD with a plain old ON TABLE HOLD with no FORMAT statement. You wont get your report back of course, but this is just looking at steps 1 and 2. By using the HOLD instead of PCHOLD we avoid the network and Client processing. If thats fast, you can suspect your Client machine (or network delay) is the culprit.

My guess is that this test will show pretty fast speeds. Take note of the number of rows youre pulling back too (this may be important for step 6 about flooding your own browser on your PC).

If thats fast, we suspect any of the steps 3 through 6.

You could further narrow this down by adding a FORMAT. Like ON TABLE HOLD FORMAT HTML. Is that still fast or did it slow down a lot when you added your HTML Try XLSX - since youre not saying PCHOLD, remember your report wont really come back. Dont worry about having to go delete these test reports by the way. They will be cleaned up off the WFRS after you try these tests automatically (unless youve messed with default APP HOLD commands). Still pretty fast with HOLD but slow with PCHOLD Thats the client dragging you down.

Do you have access to the WF Administration Console on the Client

If you do, a popular step to take is to make sure the WFRS does as much work as possible as far as building Excel or Graph requests. This can be a big help for some customers.

In the Admin Console of the Client, look under Configuration / Client Settings and scroll down hunting for this value:

IBIF_excelservurl

By default, it will likely be filled in with some value that probably looks like this:

Changing WebFOCUS Client Settings in the WebFOCUS Administration Console

&URL_PROTOCOL://servername/alias/IBIEXCELSERVURL

When its filled in, its telling the Client to do the work of zipping up the XLS file into a XLSX file. This work could be done by the WFRS in a lot of situations. Its worth a try to copy that value to a notepad session and then just delete the value all together. Youll end up with IBIF_excelservurl = blank

Changing WebFOCUS Client Settings in the WebFOCUS Administration Console

Note: For more information on IBIF_excelservurl, see the Creating Reports with WebFOCUS Language manual. (I also see references in the Security manual)

Try switching the PCHOLD to the HOLD and maybe the IBIF_excelservurl to see if that helps and let us know.

Another thing to try if you have permission is to change your Tomcat heap size to be larger by using Tomcat Manager.

If youre not having good luck yet, well talk next about how to make your request nice and small so you can open a case with techsupport and get a relatively fast answer.

Just a quick mention since were in a new forum space here. @chuck.wolff is super experienced and has years of practical, hands on knowledge about WebFOCUS. Just thought that might be worth noting.

Toby Mills, CISSP

[Debra Waybright]

Toby,

Wow! Thanks for all the good info. So I did the step of changing from PCHOLD to just HOLD and then watching the process on the Reporting Console. It seemed to be stuck in the DBMS Call state. Im guessing that means if we update our adapter we will see an improvement. When I run the SQL in SQL Server Management Studio, I get all 124 rows back in less than 15 seconds, so sitting in the DBMS Call state for 20+ minutes is puzzling to me. I will keep working at this with my systems guy and see where we land.

Thanks again for all the info!

[Charles Roller]

just a couple of complete off the top of my head, shots-in- the-dark type suggestions (maybe shot in the dark is not the best analogy to use - anyway ) Thoughts:

 

Use of XFOCUS hold formats 64K sized file blocks instead of the usual .FTM formats. ( I forget where it is but there is SET option (not -SET) SET TEMP = XFOC, SET FOCUS = XFOC something like that).

Declaration of Indexes on Temporary Tables (HOLD Files) especially if you have multiple JOINed temporary workfiles.

Use of FOCCACHE (in memory disk)-- especially if you have multiple reads of relatively small but key lookup tables.

i know this one is completely counter-intuitive but Ive actually used this technique with great success in a number of situations INDEXING of discreet arithmetic values. In substantial number of cases, the range of arithimetic values tend to cluster around, near, or on a limited number of discreet values. A range of 1 - 1000 ( with 1/10 precision for example) may actually have only 150 actual discrete values 17.1 , 23.0, 43.2, etc. If those values are indexed then subsequent index lookups can then converted to equi-joins or ranges (WHERE value FROM 1.25 TO 7.85 THEN YES ELSE ) . So if youre banding simply put the Band label as part of the discrete pair lookup (ex. 13.4, Age 10-19) .

This is technique that Ive used both in the WebFocus world and in the SQL world.

[Kamesh Gopalan]

Another way to look in to by checking the Session Monitor on the client side. Go to Administration Console --> Diagnostics --> Session Monitor and run your report and see where the time it is taking.

If it hangs an DBMS call then it is definitely with the ODBC adapter settings. Try this link

https://webfocusinfocenter.informationbuilders.com/wfappent/TLs/TL_srv_server/source/ms_sql_odbc308.htm

Good Luck!!!

[Manoj Chaurasia]

Toby - Thanks for those kind words, you always have valuable guidance to provide as well.

[Toby Mills]

Hi Debra

Thats great that youve narrowed it to the SQL Server call.

Are you using JDBC or WebFOCUSs native adapter to reach out to hit your SQL Server

I think it should work either way. I usually use our native adapter to hit WebFOCUS - so Im not sure what adapter youre talking about that you might be able to update.

Want to send us a redacted Connect String (change the info to all dummy info) so we can see how youre connecting

Thanks!

Toby

[Toby Mills]

Hi Debra

One more thing - Does your report EVER come back Or do you give up and kill it because it takes so long.

I ask because I wonder if your SQL Passthrough statement is hitting the right SQL Server Database

Heres an example of one of my connect strings.

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES WF_TST_Custom devsqlserver.dev.local/wfuserid,wfpasswordEncrypted;DefaultDBName

Does yours look kind of like that

Before I move on, take a look at the DefaultDBName at the end. You could think of this as: the WebFOCUS connection string named WF_TST_Custom points to the SQL server DB named DefaultDBName.

Try looking at your current SQL server Connection settings with this:

ENGINE SQLMSS

Youll see something like the following (using my connect string example above):

(FOC1450) CURRENT SQLMSS INTERFACE SETTINGS ARE :

(FOC1656) DEFAULT SERVER NAME - : WF_TST_Custom devsqlserver.dev.local

(FOC1502) USERID AND PASSWORD ARE - :

(FOC1758) CURRENT ISOLATION LEVEL IS - : RU

(FOC1496) AUTODISCONNECT OPTION IS - : ON FIN

(FOC1499) AUTOCOMMIT OPTION IS - : ON COMMAND

(FOC1491) FETCH BUFFERING FACTOR - : 3000

(FOC1755) CURSORS TYPE IS - : CLIENT

(FOC1723) TRANSACTION MODE IS - : LOCAL

(FOC1441) WRITE FUNCTIONALITY IS - : ON

(FOC1445) OPTIMIZATION OPTION IS - : ON

(FOC1763) IF-THEN-ELSE OPTIMIZATION IS - : ON

(FOC1709) NCHAR OPTION IS - : SBCS

The piece I want you to pay attention to is the DEFAULT SERVER NAME line. See how mine says WF_TST_Custom in my example If I fire off a SQL Passthrough command, the SQL will be sent to the SQL Server database named DefaultDBName mentioned above.

Ive had this problem before - where your Passthrough statement doesnt go where you expect.

If youre like most of us, you have multiple connections defined in WF. These usually point to different SQL databases. Like heres an abbreviated example:

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES CONN1 = RetailDB

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES CONN2 = AccountingDB

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES CONN2 = HRDB

Suppose your mean to run a query that hits a table in the AccountingDB now. If you just pop out with a SQL Passthru query while these 3 connections are turned on - by default, your query will really be aimed at the RetailDB because thats the first SQL Server connection WebFOCUS sees.

To point to the second connection, youd need to say this before your SQL query:

ENGINE SQLMSS SET DEFAULT_CONNECTION CONN2

Now when you run your SQL Passthrough, itll be aimed at the right database (AccountingDB in this example).

Make sense Just passing it along.

Toby

[Debra Waybright]

More great tips.

Now I have to eat a little crow. Somewhere along the way I got my SQL and my WF report mixed up. The report I have been working on doesnt match the SQL I ran. Doh! The reports SQL really does run super long. So back to the SQL server to index tables or see what else needs tweaked on that side. But I will definitely use these tips on some of our other long running reports. Plus, hopefully it will help someone else, too.

[Toby Mills]

Thats great! Sounds like youve got it narrowed down. So can we say youre not really having a performance problem then

Dont feel bad for missing something - I recently spent like 2 days chasing a really simple error. I tend to jump to the most in-depth, deep, hard to find explanation for a problem.

In my case, it was about as bad as being told you have to turn the computer on first or if the car is out of gas, it wont run. Even as a kid, I took a whole bunch of my motorcycle apart to dig into the carburetor before I figured out that I was simply out of gas.

Let us know if youre all set here

Toby

[Debra Waybright]

Toby - LOL! I have a tendency to work on many things at once and I guess it caught up with me.

I think I am set. In Focal Point we changed the subject to have {SOLVED] in front of it. Do we do something like that here

Thanks again!

Deb

[Steven Hall]

I think that it would be a great idea to change the subject to start with [sOLVED]. You can also mark a post in the thread as the solution.

[Manoj Chaurasia]

I agree with that idea, it has been a good thing on Focal Point.

[Toby Mills]

The [solved] was what I was thinking of. I didnt participate a whole lot on Focal Point.

Is the original poster (Deb in this case) the person who changes the Subject

Thanks!

Toby

[Manoj Chaurasia]

Yes the original poster should change it but as an administrator I can also do that

[Subbarao Desu]

Good this is solved. Nice step by step detail explanation of the flow process by Toby.