Re: SQL question

On 10/30/05, Walden H. Leverich <WaldenL@xxxxxxxxxxxxxxx> wrote:
> >Is this true on all platform implementations of SQL?
>
> Pretty much. The index is built on the underlying data, and you're
> trying to use the results of a function. A Materialized Query Table
> might help you here -- but they're not really useful yet. You can build
> them, but the optimizer won't look at them automatically.
>
> >What about those "deterministic" attributes of the sql function,
> >do they enable SQL to reuse and cache function results?
>
> Sure, but it probably wouldn't help you here, what results would it
> cache. It would still have to read the row to know what data to send
> into the function.

what about storing the results as part of the row. sql knows the row
has not changed ( the input to the deterministic function ), so it can
assume the results of the function call will be the same. once the
result is stored, then an index can be built, then the index is used
to narrow the subset of selected rows. all theory of course, but that
is my take on what the deterministic attribute is for.

>
> >I am curious about this because the large i5
> >where I am at is being brought to its knees by
> >sql coded by people who dont have an as400 background.
>
> My guess is that it's not that they don't have an as/400 background, but
> rather that they don't have a large-table background. You can get away
> w/just about anything on a table w/50,000 rows. <G> Also, has your DBA
> looked at the issues. And don't say "the as/400 doesn't need a dba."
> Those queries might run perfectly fine if there was a good index for
> them to use.

better if the system could do the work. we are too busy and risk
averse! What the heck. The system is a 3/4 million dollar query
machine runnng full throttle all day long.  If it is building
temporary access paths it is doing it hundreds of times a day. Would
be fun to watch it have the brains to retain those access paths
instead of deleting them.

I will check the access paths and the debug joblog.  on friday, there
was a query the system refused to run.  it said it was estimated it
would take ~8000 seconds to run.  had to use the CHGQRYA command
before it would execute. - once it ran it took about 60 seconds. :)

thanks,

-Steve

>
> Finally, if there are a couple of queries that are bringing the i5 to
> its knees, have a good sql developer look at them. For example, we
> recently removed a function call from a select and added a join and
> moved a query from ~7 seconds to too quick to measure w/o tools.
>
> Remember, the beauty of SQL is that it will get you the data you ask for
> in the order you want regardless of how you've indexed your tables. But
> remember, the curse of SQL is that it will get you the data you ask for
> in the order you want regardless of how you've indexed your tables. <G>
>
> -Walden
>
> ------------
> Walden H Leverich III
> Tech Software
> (516) 627-3800 x11
> WaldenL@xxxxxxxxxxxxxxx
> http://www.TechSoftInc.com
>  Quiquid latine dictum sit altum viditur.
> (Whatever is said in Latin seems profound.)
>
> --
> 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.
>
>