AW: have View need Stored Proc

First NEVER EVER specify a DDS described logical in any SQL SELECT
statement.
The query optimizer first has to rewrite the SELECT statement based on the
physical files by analyzing the DDS description first.
That means it takes the field selection, select/omit clauses and join
information from the DDS described logical file, but NOT the key
information.
After having rewritten the SQL statement optimization start. At this time
the optimizer does not know anymore that a logical file with key information
was specified.

Before release 7.1 all SQL statements where a DDS described logical file was
specified were rerouted to the OLD query engine, because only the CQE
optimizer could analyze DDS descriptions.
With release 7.1 the SQE optimizer is also able to analyze DDS description.

An SQL view never has a key. So for getting the best performance you need to
find out whether any indexes are needed and create them.
Without the right indexes the query is performed, but instead on an index
access method a table scan (reading all records) may be performed, which
slows down performance for large tables.

A stored procedure returning a result set for the same SELECT statement may
perform even worse, because a program call must be performed. The
optimization may be identical for a view and a SELECT statement returned
from a stored procedure. With the right indexes it will perform well without
the right indexes performance may be bad.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Hoteltravelfundotcom
Gesendet: Tuesday, 17.9 2013 15:46
An: Midrange Systems Technical Discussion
Betreff: Re: have View need Stored Proc

the issue is, there is a massive amount of data. I asked the user and she
said most of the time they run the report for periods over the past 2 years
but sometimes it can be more past dates.
So I was thinking of creating one view for past 2 years and another view for
previous years.

Wouldn't it be faster that way? Currently, it takes about 30+ minutes to run
the report. All the Links are being done in the Reporting tool, it queries 5
Logical files on the IBM i.

Since I now have 1 View for all these, I also want to see if other formulas
the tool is doing might be better to be done in the View. But still I
anticipate this as time taking.


On Tue, Sep 17, 2013 at 8:51 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

I see, this is good. But I was thinking of the time taking to read. I
thought it would be faster as a stored procedure.


On Mon, Sep 16, 2013 at 7:00 PM, Birgitta Hauser

<Hauser@xxxxxxxxxxxxxxx>wrote:

Why not simply adding a WHERE clause from within the reporting tool
that selects the records with the desired dates from the view?

Select * from View
Where IDDOCD between FirstDate and LastDate;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Monday, 16.9 2013 22:21
An: Midrange Systems Technical Discussion
Betreff: have View need Stored Proc

I did finally get that view to work. What I would like to do, and I
am not sure how, is to make a stored procedure so that this view will
accept dates from and to params on the column IDDOCD. Then, if I am
correct, the reporting tool can accept this stored procedure via
dates entered by the user. I have not done SP in IBM i, so so far am
bombing out. is there any one can set me on the right path here?



CREATE VIEW astccdta.acsusage
AS (
SELECT
IARCC9, IDDOCD, IDCOM#, ADSFX#, IDPRT#, IDGRC#, IDENT#, IDSFX#,
IDPRLC, IDNTU$, IDSHP#, ASTDTA.ADRESSAD.ADFNM, ASTDTA.ADRESSAD.ADLNM,
ASTTRN.OEINHDIH.IHVIAC, ASTDTA.ADRESSAD.ADSTTC, IDINV# FROM
ASTDTA.OEINDLID INNER JOIN ASTDTA.ICPRTMIA ON ASTDTA.OEINDLID.IDPRT#
= ASTDTA.ICPRTMIA.IAPRT# INNER JOIN ASTTRN.OEINHDIH ON
ASTDTA.OEINDLID.IDIDC#=ASTTRN.OEINHDIH.IHIDC#
INNER JOIN ASTDTA.ADRESSAD ON
ASTDTA.ADRESSAD.ADENT#=ASTTRN.OEINHDIH.IHENT#
WHERE ASTDTA.ICPRTMIA.IARCC9='ACS'
AND ASTDTA.OEINDLID.IDCOM#='001' AND ASTDTA.OEINDLID.IDPRT# LIKE
'ACS%')
)
--
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.

--
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.