Re: AW: SQL Table Scan Concern.

[Michael_Schutte@xxxxxxxxxxxx]

Thanks for the suggestions.

Yes I've tried to switch the files around.   Same results.

There was a table scan over both files, the qtemp file is small with only
25 records in it.

One thing you mention that caught my attention.  You said that the
"optimizer will only use an index if less than 15-20% of the complete
dataset will be selected".

I wonder if the optimizer doesn't know the percentage because I don't have
a where clause since I'm using the qtemp table to select the records.  I
suppose I could change the query to do both... have the actual dates to
select in the file, but still use the date range in the where clause.

Thanks again.

--

Michael Schutte
Admin Professional



Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is your
own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
using Bob Evans grilling sausage, visit http://www.BobEvans.com/Recipes



midrange-l-bounces@xxxxxxxxxxxx wrote on 07/21/2009 02:04:21 AM:

> Hi,
>
> First are you on the current PTF level for SQL and database?
>
> SQE's table scan is much faster than CQE's because of an feature
> named SQL Primitives within the SQE.
> The SQE optimizer interacts with statistics, in which information
> are stored such as the number of records for specific key values.
> (CQE optimizer only works with estimates).
> Also the optimizer will only use an index if less than 15-20% of the
> complete dataset will be selected.
>
> You may try the following:
> 1. Delete the logical file with the key Company/Date
> 2. Create an SQL index with this keys
> 3. Recreate the logical file.
> In this way you'll get only a single access path for the SQL index
> and the logical file. SQL normally prefers SQL indexes over logical
> files, because of the larger page size (64K versus 8K).
>
> You may also try to create an EVI with Company/Date.
>
> Is there only a single table scan within your query or 2 (over both
files)?
> May be a table scan is used over the second file, while for the
> first one an index access is executed.
>
> You may also try to change your select statement, i.e. specify the
> second file first.
>
>
> 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 Michael_Schutte@xxxxxxxxxxxx
> Gesendet: Monday, 20. July 2009 22:54
> An: Midrange Systems Technical Discussion
> Betreff: RE: SQL Table Scan Concern.
>
> It's possible.   On V5R4.   I understand the SQE to CQE handling.  I'm
just
> confused as to why SQE not using the logical.
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is
> your own backyard with Bob Evans Brats and Italian Sausage! For
> tasty recipes using Bob Evans grilling sausage, visit http://www.
> BobEvans.com/Recipes

>              "DeLong, Eric"

>              <EDeLong@Sallybea

>              uty.com>
To
>              Sent by:                  "Midrange Systems Technical

>              midrange-l-bounce         Discussion"

>              s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>

>
cc
>

>              07/20/2009 04:34
Subject
>              PM                        RE: SQL Table Scan Concern.

>

>

>              Please respond to

>              Midrange Systems

>                  Technical

>                 Discussion

>              <midrange-l@midra

>                  nge.com>

>

>

> Neill,
>
> The CQE/SQE optimizer was phased in over several releases, so
> earlier releases may have had additional constraints.  He doesn't
> mention what release he's using.
>
> Regarding the use of logical file name in a query, I believe that
> CQE was able to take advantage of explicit access path references,
> but SQE prefers to parse the physical file object to locate
> associated access paths.  To me, it sounds like his reference to the
> LF object is forcing CQE to handle the query.
>
> -Eric DeLong
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Neill Harper
> Sent: Monday, July 20, 2009 3:15 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL Table Scan Concern.
>
> Hi Michael
>
> From my understanding you hit the CQE if any of the following are in your
> statement:
>
> INSERT WITH VALUES statement or the target of an INSERT with
> subselect statement Logical files Tables with Read Triggers Read-
> only queries with more than 1000 dataspaces or updateable queries
> with more than 256 dataspaces.
> DB2Â Multisystem tables
> non-SQL queries, for example the QQQQry API, Query/400, or OPNQRYF
>
> Otherwise you use the SQE.
>
> It is generally accepted that the SQE is better/ faster than CQE,
> however perhaps in this instance you have found a query that the CQE
> is better at than the SQE.
>
> If you want to know more I'd suggest some googling on the QE and CQE.
>
> Neill
>
> May be you have found a particular scenario where going through the
> CQE is faster than the SQE
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> Michael_Schutte@xxxxxxxxxxxx
> Sent: 20 July 2009 20:45
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL Table Scan Concern.
>
> I think that you might have it backwards.
>
> When using the logical it works as expected, quickly.  But using the
> Physical, it does a table scan.
>
> Good piece of information...  the physical is a non-keyed physical.
>
> Anyway have fun on vaca.
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is
> your own backyard with Bob Evans Brats and Italian Sausage! For
> tasty recipes using Bob Evans grilling sausage, visit http://www.
> BobEvans.com/Recipes
>
>
>
> midrange-l-bounces@xxxxxxxxxxxx wrote on 07/20/2009 03:31:25 PM:
>
> > Several issues. If you have select/omit logicals on the table it could
> > be dropping into the old query engine. You need to specify in the
> > options
> file
> > ignore the logicals with select/omit.
> >
> > Going though the logical will do nothing. It will just using the
> > logical
> to
> > get the physical file and then go looking for the indexes it thinks is
> > correct.
> >
> > As far as not using the copy in QTEMP did you create the table using
> > SQL using the generate temp file option? (DECLARE GLOBAL TEMPORARY
> > TABLE) If not, I don't think SQL would even know the table exists.
> >
> > I am leaving on vacation so I can't research this further but others
> > can help you with more details.
> >
> > On Mon, Jul 20, 2009 at 12:59 PM, DeLong, Eric
> <EDeLong@xxxxxxxxxxxxxxx>wrote:
> > > Are there a lot of logicals over that file?  While I havenât seen
> > > this myself for a long time, there was a time when the query
> > > optimizer would timeout before it go to search a particular access
> > > path, so the
> optimizer
> > > would punt (force table scan).  I know I saw this prior to SQE...
> > >
> > > -Eric DeLong
> > >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> > > midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> Michael_Schutte@xxxxxxxxxxxx
> > > Sent: Monday, July 20, 2009 1:28 PM
> > > To: Midrange Systems Technical Discussion
> > > Subject: SQL Table Scan Concern.
> > >
> > >
> > > In several web programs, I thought I was being smart by creating
> > > tables
> in
> > > QTEMP that would contain selection criteria to query against large
> > > data files because we don't have logicals that met my selection
> criteria.
> I
> > > have one that is acting kinda strange.  It's not picking a locigal
> > > by company and date over the large data file.
> > >
> > > The QTEMP Table only has company and date in the file.
> > >
> > > i.e.
> > >
> > > 001   20090601
> > > 001   20090602
> > > 001   20090604
> > > 001   20090605
> > >
> > > Notice that one date is missing. The user wants to be able to select
> > > certain days of the week. ie   Weekdays only for an entire month.
> Anyway,
> > > I'm trying join this file to a large data file that does have an
> > > index
> by
> > > company and date.
> > >
> > > But VisualExplain is telling me that it's doing a table scan over
> > > RTA60 because the cost is better.  Which I know that isn't true.  If
> > > I
> replace
> > > RTA60 with the locigal name, it runs as I expect.  I hate to use the
> logical
> > > in the query because I also have to give the user the ability to run
> for
> > > select restaurants (so there's another table in qtemp that has the
> user's
> > > selection of restaurants).  But nevermind that for now.  Just
> > > company
> and
> > > date I get horrible response time when listing the physical in
> > > the query.   Anybody know why?
> > >
> > > select a.*
> > > from sar_dates b
> > > join rta60 a  on
> > > a.tcono = b.company and a.tdate = b.dateiso
> > >
> > > Does a table scan
> > >
> > >
> > > select a.*
> > > from sar_dates b
> > > join rta60L9 a  on
> > > a.tcono = b.company and a.tdate = b.dateiso
> > >
> > > Makes use of the RTA60L9 logical.
> > >
> > > Thanks in advance for the help.
> > >
> > >
> > >
> > >
> > > --
> > >
> > > Michael Schutte
> > > Admin Professional
> > >
> > >
> > >
> > > Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is
> your
> > > own backyard with Bob Evans Brats and Italian Sausage! For tasty
> recipes
> > > using Bob Evans grilling sausage, visit
> > > http://www.BobEvans.com/Recipes
> > > --
> > > 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.