RE: BSVW Table Joins

David Robertson

David Robertson

Reputable Poster
RE: BSVW Table Joins

Hey Larry,
Thanks for your comments. I was not questioning whether ER to fetch my 1 million records was slower than using an view joining the million records. The view I was trying to create only results in less than 100,000 records, so there is a significant difference in the two methods. Also, I've heard that using the ER Fetch.Single is relatively slow, although I've also heard that using Handles speeds this up.

The join of the four tables in OneWorld was in fact a normal SQL join, as you can see the SQL statement generated by the view in the JDEDEBUG.LOG when you run the UBE locally. I would be very surprised to see OneWorld doing the joins any other way when the tables all exist on the same server. The problem is simply that there is no way (at present) to represent precedence such as "AND (a=b OR c=d)" in the OneWorld toolset.

I did manage to get my view to work by using the dummy file and create view as per the foreign table method. This works really well, and is worth knowing, it just requires good documentation for the future, as well as a little trickier to move it up to CRP and Prod.

Thanks to everyone for their help and comments.

David Robertson
[email protected]
WorldSoftware A4.1 to A8.1
OneWorld B7a to B733.3
All O/S's, All DB's

> Subject: Re: RE: BSVW Table Joins
> Poster: ljones
> Posted on: 11/27/00 05:27 PM
> Hey Dave,
> regarding the impact on performance of reducing the tables in the view and using ER code to to perform additional data I-O and record rejection.
> It may not be slower than letting OneWorld try to handle all of it in one view. I don't believe you can assume that OneWorld posesses the benefits of a Relational Database in this regard. It may in fact be handling joins itself in many cases rather than constructing a single SQL statement. It certainly has to do this in cases where tables are not in the same database (OCM mappings, etc). So whether your code is processing a million records or JDE's code is processing a million records may not make a significant difference in performance. In fact, my experience is to not trust OW views with that many tables in them. You'll note that there are few if any JDE views that processing is based on that use that many tables. Wonder why ... :)
> The approach of creating a database view and treating it as a custom or external table should certainly work and will probably have the best performance. Its a lot of extra work - but if you have millions of records to process I suppose it will pay off for you.
> Cheers.
> Larry Jones
> [email protected]
> OneWorld B733.1, SP 11.3
> HPUX 11.0, Oracle SE 8.0.5
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
Hi David,
I am glad to read that you have resolved your problem.
As you know, many of us (including me) has put his/her coins to your issue.
I suppose, many of us (including me) is curious to how have you done it. It could be interesting and useful for us.
Would you be so good as to describe your method in a little bit detailed short outline.
Thanks in advance,

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)