Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Hi All, First time posting on myibi I so excite (quoting Bo...


Franco Simone

Recommended Posts

Im not sure I understand your con. However, its not one or the other. You can have requests against Tables, Stored Proc, Views and even a SQL Statement. If youre going through all these steps to get to the eventual data necessary for the request, a View will be more efficient, because SQL Server will take care of all the data prep internally and you dont have to keep writing data into HOLD files. Whether or not you use the View for other requests doesnt matter because eventually either WebFOCUS or SQL Server will have to make that process happen. A general rule of thumb I use is to use WebFOCUS joins and data prep when they dont require massive amount of holding and rejoining. Based on the steps youre describing, a SQL View or even a SQL statement to prep the data prior to writing the Graph or Table will be more efficient.
Link to comment
Share on other sites

Hi Franco

Unfortunately theres really no one-size-fits-all recommendation.

There are lots of variables to really do performance tuning. The best bet for you is to try to do whatever task youre up against both ways and see whether the view outperforms the core FOCUS coding.

A big variable is the number of records in each request for data. If your answer set is going to be small, as Babak already pointed out, its probably best to stay in WebFOCUS. Larger answer sets start pointing you more toward using views.

So - whether youre migrating from a BO universe with like 64 joins in it, or just writing your own reports, nothing beats really experimenting with either way to see which is best. It helps to work together with a DBA if youre not familiar with Explain Plan kind of output. You might be surprised to see that a WebFOCUS cluster join master is faster than a View in some situations.

If you find the WF is almost the same as SQL Views, then itll be your call to decide which route is easier for you to maintain in the future Some people like to see all their logic in the Focexec so its easier for them to know whats happening, and other customers like to keep as much business logic inside the database as possible.

WebFOCUS has Foccache which may or may not be applicable/helpful to your situation. There are just so many variables Best to just try it a couple of ways to see.

Do let us know if youre hitting a slow query using WebFOCUS - we might be able to help you dial in your FOCUS code so your query runs faster.

Good luck!

Toby

Link to comment
Share on other sites

Hi All,

First time posting on myibi I so excite (quoting Borat there).

Anyhow, what are the pros and cons of using SQL Server Views instead of WebFOCUS TABLE commands I am asking because we have some pretty complex TABLE commands with HOLD tables and more TABLE commands using those HOLD files and more TABLE commands using those HOLD files etc I am thinking of creating Views for these queries in SQL Server instead and then adding the Views to our metadata.

Certainly, a con (against Views) is that we cant reuse the metadata for other fex files if I am only bringing in a View. So if I started using Views, I would NOT use them outright and completely. And I know I would have to use some HOLD files and WHERE clauses in GRAPH or TABLE commands, so I am ok with that.

I am wondering about the performance though. I have to think the View route will generally be faster than using an adapter to create the SQL from WebFOCUS code right

Thank you

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...