Re: SFL Load - SQL vs. LF -- MIDRANGE-L

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

One of his "positives" is, however, not always a good idea, in myexperience. That is the ability to create views over views. Nesting viewscan 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 isused. A view, after all, is mostly just a SELECT statement stored in anattribute 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 thenested view hides any connection to indexes that could be used. You willget lots of index building on the fly, which can take a long time. Withnested vies you have the optimizer working on 2 SELECT statementsdynamically - there is no access plan save that I know of. I stronglysuggest avoiding nested views. They make the code simpler but theperformance often goes in the toilet.

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

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

The fairly recent article at iSeriesNetwork is flawed, IMO. There is a postin 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 sayintentionally, but things did not look the way the data actually went, inmy view. And I do not believe that the predicted performance improvementwould 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.