× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I've just done a little bit of searching about SQL Server and am running out of my area of knowledge very quickly. My search was on deterministic in SQL in google. I find that SQL Server seems to have the ability to define a column of a table using a function, whether system-defined or user-defined. I remember now seeing this when creating a column as a identifier column. And this column that uses the function CAN be indexed if it is deterministic and precise and that the system can verify these conditions and maybe some other criteria.

Also, it appears that SQL Server lets you create an index over a view, so long as the view is deterministic. I did not go any further with that.

I know that even at V5R3, iSeries SQL does not allow creating an index over a view. Is this stuff coming? I have no idea. I might call PWD support to see if they will tell me anything. But this shows at least a couple differences between SQL Server and DB2 for iSeries.

Does that muddy the waters a bit?   ;-)
Vern

At 06:55 PM 10/30/2005, you wrote:

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

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

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.