John, Many Thanks. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293
________________________________ From: John Minyo II <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, May 28, 2009 1:23:32 PM Subject: [RBASE-L] - Re: Query Optimization 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 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 www.ForenSys.ca<http://www.forensys.ca/> 416 512 6950
