|
On 10/30/05, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote: > I'm not sure of other platforms, Steve, but the definition of > DETERMINISTIC on iSeries (and elsewhere?) is: > > Specifies that the function will always return the same result from > successive invocations with identical input arguments. > > So it seems that, even if a function is declared as deterministic, if > used on a column name, there has to be a table scan. Someone else > (Charles?) said that the only time a deterministic function returns > the same value every time is when there is no column involved - and > the definition above suggests that the only time would be when there > are no parameters at all. lets say you have a 3 char season code, where the first char is either "S" for spring and "F" for fall. then chars 2-3 are the 2 digit year. The function "DateToSeasonCode" could be classified as deterministic. The order header contains an entry date. The following sql selects all the orders for the current season: select * from ordhdr a where DateToSeasonCode( a.EntryDt) = "F05" since the function is deterministic, sql could store the result of the above function for each row in the table, then the second time the sql is run, use the stored result instead of recalculating a value that it knows will not change. who knows if the system is that clever. but I figure that is what the deterministic distinction is for. just as sql can be too slow at times, it can also be blazingly fast. -Steve > Walden beat me to some other thoughts. Let me add a couple tools for > analyzing possible problems. These are listed in the Query > Performance manual (name is different in different versions of OS/400). > 1. Use STRDBG and look at optimizer messages in the job log. > 2. Use STRDBMON and analyze the resulting data. There're some papers > at www.iseries.ibm.com/db2 on doing this. > 3. Set up a monitor in Ops Navigator - these actually start DBMON to > get the data. I don't remember whether you can use an existing test > data set as input. You then view things in Visual Explain > 4. Get Centerfield Technology's insure/ANALYSIS or insure/INDEX > product - not "free". as OpsNav is, but you can use existing DBMON > data as input to it. > 5. Get a Centerfield Technology database/ASSESSMENT - web site > (www.centerfieldtechnology.com) says $2995. You collect data over a > period of time and send it in. You get back a nice report with top > consumption kinds of things, as well as good suggestions for setting > up indexes, etc. The specific link is > http://www.centerfieldtechnology.com/tools/analysisoffer.asp > 6. Go to www.iseries.ibm.com/db2 and look for the articles and online > classes on performance, including some on indexing strategies. > > HTH > Vern > > At 06:17 AM 10/30/2005, you wrote: > > >On 10/29/05, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote: > > > One of the replies used the DIGITS() function and still ran a table > > > scan. This illustrates a basic rule - avoid expressions that use > > > functions in the WHERE clause and in JOIN specifications, because it > > > will ALWAYS result in a table scan (or probably at least 99% of the > > > time). This is because it cannot eliminate anything without first > > > running the calculation, so it has to run that calculation on ALL > > the records. > > > >that is an interesting point Vern. Is this true on all platform > >implementations of SQL? What about those "deterministic" attributes of > >the sql function, do they enable SQL to reuse and cache function > >results? > > > >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. > > > >-Steve > > > > > > > > Make sense? > > > > > > The solution offered here is least likely to use a table scan, as it > > > has no functions in it. > > > > > > HTH > > > Vern > > > > > > At 03:29 PM 10/28/2005, you wrote: > > > > > > >How about: > > > >(DATE_YY > SelectedYear or > > > > (DATE_YY = SelectedYear and > > > > (DATE_MM > SelectedMonth or > > > > DATE_MM = SelectedMonth and > > > > DATE_DD >= SelectedDay))) and > > > >(DATE_YY < Selected Year or > > > > (DATE_YY = SelectedYear and > > > > (DATE_MM < SelectedMonth or > > > > DATE_MM = Selected Month and > > > > DATE_DD <= SelectedDay))) > > > > > > > >Out of curiosity, why do you want to use SQL if the RPG code can easily > > > >select the records? > > > > > > > >Donald R. Fisher, III > > > >Project Manager > > > >Roomstore Furniture Company > > > >(804) 784-7600 extension 2124 > > > >DFisher@xxxxxxxxxxxxx > > > > > > > ><clip> > > > >What I can't seem to figure out is how to use SQL to select a date range > > > >that doesn't result in a table scan. > > > > > > > >These result in a table scan: > > > >SELECT DATE_YY * 10000 + DATE_MM * 100 + DATE_DD FROM TABLE WHERE > > > > (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) >= 20051001 AND > > > > (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) <= 20051028 > > > > > > > >SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE > > > >WHERE (DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) >= > > > >'20051001' AND > > > >(DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) <= '20051028' > > > > > > > > > > > >This one doesn't, but it's problematic: > > > > > > > >SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE > > > >WHERE > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 1) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 2) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 3) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 4) OR > > > > ... > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 25) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 26) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 27) OR > > > > (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 28) > > > ><clip> > > > >-- > > > >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. > > -- > 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. > >
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
