Rushmore and JOINs
Hello all, I know that Rushmore tech comes into play when you JOIN tables using fields that have index tags in their respective tables. However, does the tag have to match the name of the actual field being indexed? Reason I ask, in an app I support, performance was slow when joining two tables together on one key field. Let's say the field is: FGITEM The index tag is just ITEM The Join statement had: SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on tableA.FGITEM = tableB.ITEM Once I added a new index TAG to tableA FGITEM tag FGITEM The query ran much faster. Does this make sense or am I misunderstanding it? Thank you, Philip Borkholder Vicksburg, MI Old School Yearbook Pics View Class Yearbooks Online Free. Search by School Year. Look Now! http://thirdpartyoffers.netzero.net/TGL3241/558d7928daeb179283574st04duc ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/558d7918.70...@netzero.net ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Rushmore and JOINs
On Fri, Jun 26, 2015 at 11:33 AM, Fred Taylor fbtay...@gmail.com wrote: Yes, that's exactly correct. For Rushmore to kick in, the left side of your condition must match a tag's fields. The tag name is irrelevant. So in your example, TABLEA must have an index on FGITEM. The tag name does not matter. - Exactly. Making a compound index for the column(s) used as fkeys to other tables is the best way to keep querys fast. SOLines may have 3 fkeys, SOID, InventoryID, ContractPriceID as well as the primary key for the row. Put all of the fkeys into a single index. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMYJssv=WGXsoOaXfSjVDefTK_H=t4wjysk7invmo4rt...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Rushmore and JOINs
Thank you for the responses: What I find strange though, and the reason I posted, is that the JOIN field DID have an index TAG in the CDX with a TAG name that differed from the field, and the Query was slow. Once I added an additional Index tag, on the same field, with a TAG name that equaled the field, the query ran faster... very strange. -Philip On 6/26/2015 12:33 PM, Fred Taylor wrote: Yes, that's exactly correct. For Rushmore to kick in, the left side of your condition must match a tag's fields. The tag name is irrelevant. So in your example, TABLEA must have an index on FGITEM. The tag name does not matter. Fred On Fri, Jun 26, 2015 at 9:08 AM, Philip Borkholder plbor...@netzero.net wrote: Hello all, I know that Rushmore tech comes into play when you JOIN tables using fields that have index tags in their respective tables. However, does the tag have to match the name of the actual field being indexed? Reason I ask, in an app I support, performance was slow when joining two tables together on one key field. Let's say the field is: FGITEM The index tag is just ITEM The Join statement had: SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on tableA.FGITEM = tableB.ITEM Once I added a new index TAG to tableA FGITEM tag FGITEM The query ran much faster. Does this make sense or am I misunderstanding it? Thank you, Philip Borkholder Vicksburg, MI Old School Yearbook Pics View Class Yearbooks Online Free. Search by School Year. Look Now! http://thirdpartyoffers.netzero.net/TGL3241/558d7928daeb179283574st04duc [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/558d82c8.8040...@netzero.net ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Rushmore and JOINs
Yes, that's exactly correct. For Rushmore to kick in, the left side of your condition must match a tag's fields. The tag name is irrelevant. So in your example, TABLEA must have an index on FGITEM. The tag name does not matter. Fred On Fri, Jun 26, 2015 at 9:08 AM, Philip Borkholder plbor...@netzero.net wrote: Hello all, I know that Rushmore tech comes into play when you JOIN tables using fields that have index tags in their respective tables. However, does the tag have to match the name of the actual field being indexed? Reason I ask, in an app I support, performance was slow when joining two tables together on one key field. Let's say the field is: FGITEM The index tag is just ITEM The Join statement had: SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on tableA.FGITEM = tableB.ITEM Once I added a new index TAG to tableA FGITEM tag FGITEM The query ran much faster. Does this make sense or am I misunderstanding it? Thank you, Philip Borkholder Vicksburg, MI Old School Yearbook Pics View Class Yearbooks Online Free. Search by School Year. Look Now! http://thirdpartyoffers.netzero.net/TGL3241/558d7928daeb179283574st04duc [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJCBksqkoPDVeUukKN=+z8PF=Oersnp2=4txnm4y0czeqkd...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Rushmore and JOINs
Philip: I agree, what you have observed is strange. The name of tag should not matter, only the expression of what is indexed. Unless the first index had another clause, like UNIQUE or FOR .NOT. DELETED() in the tag creation, the first tag should have worked for you. The other possibility is that the original tag had some corruption, or the index needed to be rebuilt. If the index was created a long time ago, and a lot of records added since then, the index could be bloated, fragmented across a a lot of file fragments, which sometimes a PACK, REINDEX or a clean re-creation would resolve. On Fri, Jun 26, 2015 at 12:50 PM, Philip Borkholder plbor...@netzero.net wrote: Thank you for the responses: What I find strange though, and the reason I posted, is that the JOIN field DID have an index TAG in the CDX with a TAG name that differed from the field, and the Query was slow. Once I added an additional Index tag, on the same field, with a TAG name that equaled the field, the query ran faster... very strange. -Philip On 6/26/2015 12:33 PM, Fred Taylor wrote: Yes, that's exactly correct. For Rushmore to kick in, the left side of your condition must match a tag's fields. The tag name is irrelevant. So in your example, TABLEA must have an index on FGITEM. The tag name does not matter. Fred On Fri, Jun 26, 2015 at 9:08 AM, Philip Borkholder plbor...@netzero.net wrote: Hello all, I know that Rushmore tech comes into play when you JOIN tables using fields that have index tags in their respective tables. However, does the tag have to match the name of the actual field being indexed? Reason I ask, in an app I support, performance was slow when joining two tables together on one key field. Let's say the field is: FGITEM The index tag is just ITEM The Join statement had: SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on tableA.FGITEM = tableB.ITEM Once I added a new index TAG to tableA FGITEM tag FGITEM The query ran much faster. Does this make sense or am I misunderstanding it? Thank you, Philip Borkholder Vicksburg, MI Old School Yearbook Pics View Class Yearbooks Online Free. Search by School Year. Look Now! http://thirdpartyoffers.netzero.net/TGL3241/558d7928daeb179283574st04duc [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cacw6n4ukz+jrmzerbgeiusdu4zzsfwcyel2e64dmy8ruabq...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Rushmore and JOINs
Check out SYS(3054). It can give you more insight into query optimization. -- rk -Original Message- From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Philip Borkholder Sent: Friday, June 26, 2015 12:50 PM To: profoxt...@leafe.com Subject: Re: Rushmore and JOINs Thank you for the responses: What I find strange though, and the reason I posted, is that the JOIN field DID have an index TAG in the CDX with a TAG name that differed from the field, and the Query was slow. Once I added an additional Index tag, on the same field, with a TAG name that equaled the field, the query ran faster... very strange. -Philip ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/bn4pr10mb0913da461d01f932af670912d2...@bn4pr10mb0913.namprd10.prod.outlook.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Rushmore and JOINs
Philip Borkholder wrote on 2015-06-26: Hello all, I know that Rushmore tech comes into play when you JOIN tables using fields that have index tags in their respective tables. However, does the tag have to match the name of the actual field being indexed? Reason I ask, in an app I support, performance was slow when joining two tables together on one key field. Let's say the field is: FGITEM The index tag is just ITEM The Join statement had: SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on tableA.FGITEM = tableB.ITEM Once I added a new index TAG to tableA FGITEM tag FGITEM The query ran much faster. Does this make sense or am I misunderstanding it? Thank you, Philip Borkholder Vicksburg, MI Philip, The join condition needs to match the index. In an old system the child table has a compound key. parentTable (pkey n(5)...) index on pkey tag pkey childTable (fkey n(5), seq n(2)...) index on padr(fkey,5) + padr(seq,2) tag pkey select parentTable.*, childTable.detail ; from parentTable left join childTable ; on padr(fkey,5) + padr(seq,2) = padr(pkey,5) Since parentTable is the primary, it is usually limited in the where clause. The join is geared use the index on the larger side to limit. HTH, Tracy Tracy Pearson PowerChurch Software ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/001001d0b02e$f9ae7a90$ed0b6fb0$@powerchurch.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
