[RBASE-L] - Re: Query Optimization

Thu, 28 May 2009 11:25:29 -0700 

Hello Jim,
While digging into archive documents for revamping the R:BASE eXtreme 9.0 Online Help, many topics as well as the following, was uncovered and added: 

MICRORIM_EXPLAIN Output
-----------------------

The output displays the optimization time in 
milli-seconds (OptimizationTime=930ms). This time 
is how long it took R:BASE to determine the best 
table and index order. Each table order and index 
the optimizer checks is given a value 
(SelectCost=9.796483). The lowest value, or 
select cost, determines which query order is 
used. There are four types of access methods in 
MICRORIM_EXPLAIN that can be used to retrieve the data:



Sequential - No index was used. The rows are 
retrieved from the table in the order they were entered.

Random - An index access method.
Very Random - An index access method.

IndexOnly - No data was retrieved from file 2, 
all the data was retrieved from file 3.



When index access is used (IndexOnly, Random, 
Very Random), the column name or index name and 
the index type are displayed. For example:


transmaster (ColumnName=empid, Type=F) IndexOnly Dup=3.8 Adj=1
transmaster (IndexName=dateindex,Type=I) Random Dup=1.055556 Adj=1

The index types are:

P - Primary Key
F - Foreign Key
I - Index


In addition, the terms "PreSort" and "SortMerge" 
are used. In multi-table joins, a small table may 
be placed entirely in memory and sorted there-the 
table is pre-sorted. Lookups are then done from 
the pre-sorted table in memory to the other 
tables in the join. PreSort is used when the sort 
column(s) is in only one table. The sort 
column(s) is not a linking column. When this 
method is used, PreSort shows in the EXPLAIN.DAT 
output. SortMerge is used only if PreSort is also 
used. The SortMerge method is most efficient if 
the tables are not indexed. When PreSort and 
SortMerge are indicated, you also see 
SortRemoved. The sort has already been done, and 
is not repeated again for the final output. 
Sorting the final output is not included in the SelectCost. For example:


SelectCost=O.3035209 (OptimizationTime=Oms) SortRemoved
  Prodlnfo Sequential PreSort (DATA_ONLY)
  Customer (ColumnName=CustID,Type=I) Random Dup=1 Adj=1
  Products (ColumnName=ProdID,Type=I) Random Dup=1 Adj=1


The 32-bit option of MICRORIM_EXPLAIN can be used 
to display the sort strategy R:BASE used for the query. For example:


SortStrategy = DB_TAG (internal=1)
SortStrategy = DATA_ONLY (internal=2)

The possible sort strategy options are:

DB_TAG (Internal=1)

The sort is done using the rowid value. Only the 
columns specified in the ORDER BY are used to 
sort the data. Lookups are then done from the 
ordered list of rowid values to retrieve the rest 
of the data for display. When there are many rows 
of data, or many columns to display, this is the type of sort used.


DATA_ONLY (Internal=2)

The sort is done using all of the data to be 
displayed. An entire row of data is sorted 
together. This type of sort is used when there is 
a small number of rows or only a few columns to display.


FILE_TAG (Internal=3)

The sort is done to a temporary file. Multi-table 
queries involving NOTE fields and sorts on views usually use this type of sort.


DB_TAG_PRESORT (Internal=4)

The sort is done using the rowid of the column(s) 
specified in the ORDER BY. The sort columns must 
be from just one table in a multi-table SELECT. 
The table is presorted, then lookups are done to retrieve the rest of the data.


FILE_TAG_PRESORT (Internal=5)

The sort is done to a temporary file. The table 
is presorted, then lookups are done to retrieve the rest of the data.

---------------------


The above answers all of your questions except 
the last one. To possibly see different results 
for your query, you should try using the complete 
SELECT command statement, from which the view is 
based upon, rather than using SELECT * FROM ViewName.


John

At 12:07 PM 5/28/2009, you wrote:

Paul,
I often use this feature when I am trying to understand the view I create.

The actual wording come I sent came from the 
v6.5++ on line syntax reference at <http://www.rsyntax.com>www.rsyntax.com.
I am sure AI got the initial information way 
back when from the late lamented "R:Base Exchange" magazine.
As to explanations of how to interpret the 
information I can not remember and discussions 
on this subjects in print or online.



It would be nice to open a dialog on this list 
to flesh out how one can better use the 
information presented in the EXPLAIN.DAT file.


For example using the following view:
CREATE VIEW RptVuMbrDtlDp +
(FChrLastName, LastNameSortSeq, FirstName, MiddleName, LastName, FullNameLF+
M, MbrNumber, Salutation, PrsnTitle, DearBroGreet, NickName, SigOtherName,+
Birthday, Initiated, HighSchoolNbr, HighSchoolClassOf, CurHomeAudAction, Cu+
rHomeAudDate, CurHomeAudTime, CurWorkAudAction, CurWorkAudDate, CurWorkAudT+
ime, DateOfDeath, AcademicRecCnt, MbrStatus, RegisterChgDate, HqMailStatus,+
 MAdrSource, HAdrSource, HPhoneNbr, HCellNbr, EMailAdr, HomeAdrBlock, HCoun+
try, HomeChgDate, WAdrSource, WPhoneNbr, WFaxNbr, JobTitle, CpyName, WorkAd+
rBlock, WCountry, WorkChgDate, FromOrgNameNbr, FromChapLit, EstAge) +
AS SELECT T1.FChrLastName,T1.LastNameSortSeq,T1.FirstName,T1.MiddleName,T1.+
LastName,T1.FullNameLFM,T1.MbrNumber,T1.Salutation,T1.PrsnTitle,T1.DearBroG+
reet,T1.NickName,T1.SigOtherName,T1.Birthday,T1.Initiated,T1.HighSchoolNbr,+
T1.HighSchoolClassOf,T1.CurHomeAudAction,T1.CurHomeAudDate,T1.CurHomeAudTim+
e,T1.CurWorkAudAction,T1.CurWorkAudDate,T1.CurWorkAudTime,T1.DateOfDeath,T1+
.AcademicRecCnt,T1.MbrStatus,T1.RegisterChgDate,T1.HqMailStatus,T1.MAdrSour+
ce,T2.HAdrSource,T2.HPhoneNbr,T2.HCellNbr,T2.EMailAdr,T2.HomeAdrBlock,T2.HC+
ountry,T2.HomeChgDate,T3.WAdrSource,T3.WPhoneNbr,T3.WFaxNbr,T3.JobTitle,T3.+
CpyName,T3.WorkAdrBlock,T3.WCountry,T3.WorkChgDate,T4.FromOrgNameNbr,T4.Fro+
mChapLit,(FORMAT(IFNULL(DeathYear,   INT((.#DATE - IFEXISTS(Birthday,Birthd+
ay,IFEXISTS(Initiated,   ADDYR(Initiated,-19),.#DATE)))/365.25),DeathYear -+
IYR4(Birthday)),AgeFmtMask)) FROM RegisterOfMbrs T1,A1AllNbrHome01 T2,A1All+
NbrWork01 T3,A1FromOrgNameNbr T4 WHERE T1.MbrNumber = T2.MbrNumber AND T1.M+
brNumber = T3.MbrNumber AND T1.MbrNumber = T4.MbrNumber

RegisterOfMbrs is the base table
others are views:

A1AllNbrHome01 is an outer join of 
RegisterOfMbrs with the MbrCurHomeAdr table (not 
all members have home address rec)
A1AllNbrWork01 is an outer join of 
RegisterOfMbrs with the MbrCurWorkAdr table (not 
all members have work address rec)
A1FromOrgNameNbr is an outer join of 
RegisterOfMbrs with MbrFromChapter (very limited 
number of members listed on the MbrFromChapter)


Here is the contents of EXPLAIN.DAT when I do a SELECT * from RptVuMbrDtlDp

R> prompt
05/28/2009 10:42:30
SelectCost=1  (OptimizationTime=0ms)
  RptVuMbrDtlDp Sequential

R> prompt
05/28/2009 10:42:30
SelectCost=5.059211  (OptimizationTime=0ms)
  A1AllNbrHome01 Sequential
  RegisterOfMbrs (ColumnName=MbrNumber,Type=P) Random Dup=1 Adj=0.9956141
  A1AllNbrWork01 Sequential
  A1FromOrgNameNbr Sequential

R> prompt
05/28/2009 10:42:30
SelectCost=1.846558  (OptimizationTime=0ms)
  RegisterOfMbrs (ColumnName=MbrNumber,Type=P) IndexOnly Dup=1 Adj=0.9956141
  MbrCurHomeAdr (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=0.995283

R> prompt
05/28/2009 10:42:30
SelectCost=0.06554899  (OptimizationTime=0ms)
  RegisterOfMbrs (ColumnName=MbrNumber,Type=P) IndexOnly Dup=1 Adj=0.9956141
  MbrCurWorkAdr (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=0.9753846

R> prompt
05/28/2009 10:42:30
SelectCost=0.1005131  (OptimizationTime=0ms)
  RegisterOfMbrs (ColumnName=MbrNumber,Type=P) Random Dup=1 Adj=0.9956141
  MbrFromChapter (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=1

Questions:
What is SelectCost and how is it calculated?
Type=P,Type=F seem to indicate PRIMARY and FOREIGN keys
What do the other terms indicate?

How would I use this information to construct a 
faster/better version of this view?


Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293



From: Paul InterlockInfo <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Thursday, May 28, 2009 9:26:18 AM
Subject: [RBASE-L] - Re: Query Optimization

Thank You – MR.. James Bentley,




All I can say is Ho  Haaaa!  (Scent of a 
Women)  where did you find that.  Nope I take 
that back, I do not want to know.   MR James 
Bentley that is good recall and information that 
blew me away this morning,  and I add - so early in the morning.





I will have to go back in time to my RBase 
magazines back in the late 80’s and 90’s and see what I can find.




Sincerely,

Paul Dewey











From: [email protected] [mailto:[email protected]] On Behalf Of James Bentley
Sent: Thursday, May 28, 2009 6:45 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Query Optimization



Stephen,

Here is what you need to do. I have these 
commands in a RMD file. I then RBEDIT explain.dat
To the best of my knowledge there is virtually 
no information on how to interpret the various 
parts of data that appears in the EXPLAIN.DAT 
file.  It does however break down how it 
processes the various tables in the command.


"SET DEBUG ON
SET VAR MICRORIM_EXPLAIN = 47
RETURN
*(MICRORIM_EXPLAIN
This variable shows the decision the optimizer made when executing a
particular command.  The optimizer results are placed in the file
EXPLAIN.DAT.  The DEBUG command must be on to use microrim_ explain.

The available options for microrim_ explain are
    1 - output the table order.
    2 - force optimization of joins over five tables.  This option is
         ignored if microrim_fullopt is set.
    4 - output the command file name and next byte offset.
    8 - output the current date and time.
   32 - display the sort technique used.

To use multiple options, add the option numbers together and set the
variable to the result.  For example:

SET VAR microrim_explain = (1 + 4 + 8)
SET VAR microrim_explain = 13
)



Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293





From: Stephen Markson <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Wednesday, May 27, 2009 7:36:53 PM
Subject: [RBASE-L] - Query Optimization



Is there any info on how the RBase engine parses queries to use indexes
effectively? Are there are any techniques that can force the engine to use a
particular index? I seem to recall that in the old days there was a system
variable, MICRORIM_EXPLAIN_PLAN or something like that would show how R:Base
had parsed the query.



Regards,



Stephen Markson
ForenSys The Forensic Systems Group
<http://www.ForenSys.ca>www.ForenSys.ca <http://www.forensys.ca/>
416 512 6950



























					Reply via email to