SQL Summary Views (Virtual Tables) and System Impact

[DBohner-(db)]

Someone else has done this - so I'm looking for feedback and insight....

File Structure looks like this:
A - Header Table
1 - Detail 1 Table
b - Detail of Detail 1 Table

We have several Applications and Reports - that summarize the Detail and the 'Detail of Detail' - and due to the I/0 - these processes can be very time consuming. The Summaries, via SQL, could be done 'almost instantaneous' - but because we are doing them via Select/Fetch/While loops - the processing is crazy-long.

One thought is to create SQL Views that are summaries of the Detail tables (Using the Primary Keys of the Parent and summarizing or averaging the detail). If we create these SQL Views, we could make them Virtual Tables - then do a single fetch against the Summary 'View' instead of hundreds of fetches.

What I don't know - is what is the overall system impact of creating a 'Summary' SQL View. Generally speaking, any time there would be an Insert, Update/Edit, Delete to the Detail data - that Summary SQL View would be 'auto-magically' updated by the database. How much system impact could occur when such I/O occurs to the Detail Records.

That said - who's done it, what issues occurred - and how was the experience?

Is this a path I want to pursue?

(db)

 

[jdelisths]

"that Summary SQL View would be 'auto-magically' updated by the database"

SQL views are not "updated" when the underlying data is updated (to my knowledge) (what that means is that, the SQL engine does not have to "do" anything when the data is updated) . The view is just a logical representation of the table columns, joins etc. and any optimization that goes with it. When your detail table is updated, the performance of the view may be affected, but it will still be much, much better than doing the select/fetch. This is definitely a path you want o pursue.

 

[BOster]

Hari is correct. A SQL view is basically just a "saved" sub-query and performance will definitely be much better for all servers involved (ES, DB) by letting the database do what it is good at.

Having said that, if you want to avoid the hassle of maintaining a bunch of SQL views and JDE virtual tables you should look into the JDEBASE aggregate APIs.

JDB_SetAggregate
JDB_SetGroupBy
JDB_FetchAggregate

This can completely eliminate the need to create SQL views for aggregate type queries in most cases and is probably even more efficient in terms of processing/speed than using SQL views.

 

[Larry_Jones]

Actually, there is a type of database view that is updated when the underlying data is updated. Its called a "Materialized View" which is stored like a table in that it occupies space and has its own data.
There's an obvious potential performance hit from forcing any updates to the base tables to also update one or more materialized view tables.
The reason for them however is to handle mega queries which just take too long when run dynamically and consume too many resources.

Its a cool solution to some problems - but use should be limited only to those kind of problems it was designed to solve (very expensive dynamic queries).

 

[BOster]

And I guess to be technically correct, I believe you can (in MS SQL server at least) place indexes on the view which will effectively "create data" and has to be updated when the underlying table(s) are updated - maybe that's the same thing you are talking about. All the SQL views / VTables I have done are effectively nothing more than a simple sub-query - and now that Oracle has removed the ridiculous table limit in business views I will probably have to do a lot less.

 

[johndanter]

It's definitely faster.
I had a workfile before called F5911 which had certain ledger values written to it and several reports needed the SUM of AA.

Some bright spark had the idea of creating a new table F5911D1 which was an exact clone of F5911, drop it and then create select SUM view called F5911D1 as follows

CREATE VIEW F5911D1 AS
SELECT DT59$LANO DM59$LANO, DTCLASS DMCLASS, DTCTRY DMCTRY, DTFY DMFY, DTPN DMPN, SUM(DTAA) DMAA
FROM (environment).F5911
GROUP BY DT59$LANO, DTCLASS, DTCTRY, DTFY, DTPN

It was a million miles faster than doing it a loop within ER. So much faster, this was done in my 1st JDE job some 14+ years ago.
I wrote it down and saved the SQL and use it's principle in almost everywhere I've worked since.

I don't follow your comment on magically updating anything. I don't think it's needed. This SQL was fired off everytime I wrote fetch single F5911D1 in my code. So it's an on demand thing

 

[sselman]

Do it all the time when we need a performance boost. Set processing vs record by record processing.

Worlds of performance difference when you use the right tool for the job.

 

[DBohner-(db)]

I think this is what I'm looking for:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/index.html

(db)

 

[DBohner-(db)]

Maybe I'm thinking 'better', today....

Another route might be to script a query that does a Unique Keyed insert, into a summary table

Skizzled-Logic-Formatted:
- Grab a Unique Key (that F00022 function) - call it &UniqueKey
- Delete from Schema.SumTable where &UniqueKey
- Call a custom function that executes a supper-fast SQL script, synchronously

Psuedo SQL Statement ----
Insert into Schema.SumTable (
Select &UniqueKey, Key1, Key2, Key3, Sum(ValueA), Sum(ValueB), Sum(ValueC)
From Schema.DetailTable
Group by Key1, Key2, Key3 );

- Now fetch against &UniqueKey, Key1, Key2, Key3 - to get the total of the Summary line.

Generally - the summery from within an SQL 'should be' quicker than the File I/O within ER (right)??

That Materialize View - could be troublesome, at times.

(db)

 

[Larry_Jones]

Daniel,
I think you've described what the Materialized Query Table (MQT) does. Its a table populated from a query that you can schedule updates to.

I also don't understand why you don't just use a regular JDE Virtual table that points to a Named DB View / Query.