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

[NYCBabak .]

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.

[Manoj Chaurasia]

I completely agree with Babak

[Toby Mills]

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

[Franco Simone]

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

[Franciscus van Dortmont]

Its important to know if the functions you have in a masterfile translate to SQL or not, some legacy functions result in big data requests. if you encounter this, try rewriting it to a simplified function or use a native SQL function with DB_EXPR.

[Franco Simone]

Thank you all for the great answers!