Re: SFL Load - SQL vs. LF

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

I don't know, that article gives mixed reviews as I read it. Even Paul's 
conclusion says to use both as appropriate.

One of his "positives" is, however, not always a good idea, in my 
experience. That is the ability to create views over views. Nesting views 
can result in some of the worst performance you ever want to see with SQL. 
Why? Because the access path, as such, is not determined until the view is 
used. A view, after all, is mostly just a SELECT statement stored in an 
attribute of the logical file. And if you nest them, and they need indexes, 
existing ones will probably not be used, because the result set from the 
nested view hides any connection to indexes that could be used. You will 
get lots of index building on the fly, which can take a long time. With 
nested vies you have the optimizer working on 2 SELECT statements 
dynamically - there is no access plan save that I know of. I strongly 
suggest avoiding nested views. They make the code simpler but the 
performance often goes in the toilet.

Paul's hope that IBM will combine views with indexes in a single object, as 
you can do with LFs, will not, IMO, happen any time soon. The separation of 
views and indexes is an SQL standard kind of thing. Of course, each vendor 
can have their own extensions to the standard, and all do have some.

Paul points out that a potentially great performance and space benefit of 
LFs is not available to indexes - shared access paths. So, again, I think 
the article does not overwhelmingly support going only to DDL - not enough 
for me, at least to say "definitely" use DDL. If anything, a strong reason 
to go to DDL is that it is used on almost all platforms - not for 
performance reasons - see next paragraph.

The fairly recent article at iSeriesNetwork is flawed, IMO. There is a post 
in the archives where I state some problems I had with the testing methods. 
The graphs were presented in a fashion that was misleading - I do not say 
intentionally, but things did not look the way the data actually went, in 
my view. And I do not believe that the predicted performance improvement 
would often be attained. Here is the link to that post:

http://archive.midrange.com/midrange-l/200507/msg00069.html

for your consideration.

Regards
Vern

At 05:57 PM 9/7/2005, you wrote:

> Here is just one link for reading with good reasons:
>
> http://whatis.techtarget.com/cgi-bin/rd.pl/ftID-1046274-ctID-1021854?//tip/1,289483,sid3_gci1021854,00.html
>
> or just search on "sql and dds" using your chosen iSearch engine
> also iseriesnetwork has some good articles.
>
> hth,
> spaceBAR
>
> ------ Original Message ------
> Received: Wed, 07 Sep 2005 01:15:10 PM CDT
> From: "James H H Lampert" <jamesl@xxxxxxxxxxx>
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Subject: Re: SFL Load - SQL vs. LF
>
> Billy Rowe <billyrowe@xxxxxxx> wrote:
> > No matter which presentation method you decide to you,
> > definitely create the tables/indexes/views with SQL.
>
> Why?
>
> --
> JHHL
> (who is much more fluent in DDS and OPNQRYF than with SQL)
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.