Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
index usage with t(edb)dataset.filter |
Tue, Nov 4 2014 9:59 AM | Permanent Link |
Gruetzmacher | hello,
edb 2.18b1. i have a multicolumn-index (3 columns) which is used correctly in an sql query if i use "row values": select * from mytable where (col1, col2, col3)=(val1, val2, val3) however when i work with a tedbtable and use its filter-property i can not use row values. the query optimizer this time selects the wrong index which results in slow performance. has anyone found a workaround for this? it is difficult to use an tedbquery in my scenario ... help is greatly appreciated thank you |
Tue, Nov 4 2014 10:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gruetzmacher
>i have a multicolumn-index (3 columns) which is used correctly in an sql query if i use "row values": >select * from mytable where (col1, col2, col3)=(val1, val2, val3) > >however when i work with a tedbtable and use its filter-property i can not use row values. the query optimizer this time selects the wrong index which results in slow performance. has anyone found a workaround for this? it is difficult to use an tedbquery in my scenario ... It used to be that compound indices in queries were only for sorting not for filtering, whereas tables used them in filtering that may have changed, or my memory may be totally gone. I just tried here with a table and compound index and that filter was faster to my eyeballs than the query. How are you setting the filter? What is the index? Roy Lambert |
Tue, Nov 4 2014 10:42 AM | Permanent Link |
Gruetzmacher | hello roy,
thank you for the quick response. the index is col1, col2, col3 actually the complete where-condition is (col1, col2, col3)=(val1, val2, val3) and col4=val4 there is a single column index on col4 which actually is choose in the filter i tried to set the filter this way: edbTable.Filter := '(col1, col2, col3)=(val1, val2, val3) and col4=val4 but this is not working - only: edbTable.Filter := col1=val1 and col2=val2 and col3=val3 and col4=val4 works ... unfortunately the optimizer chooses not in my favour ... Roy Lambert wrote: Gruetzmacher >i have a multicolumn-index (3 columns) which is used correctly in an sql query if i use "row values": >select * from mytable where (col1, col2, col3)=(val1, val2, val3) > >however when i work with a tedbtable and use its filter-property i can not use row values. the query optimizer this time selects the wrong index which results in slow performance. has anyone found a workaround for this? it is difficult to use an tedbquery in my scenario ... It used to be that compound indices in queries were only for sorting not for filtering, whereas tables used them in filtering that may have changed, or my memory may be totally gone. I just tried here with a table and compound index and that filter was faster to my eyeballs than the query. How are you setting the filter? What is the index? Roy Lambert |
Tue, Nov 4 2014 11:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gruetzmacher
>the index is col1, col2, col3 >actually the complete where-condition is (col1, col2, col3)=(val1, val2, val3) and col4=val4 >there is a single column index on col4 which actually is choose in the filter > >i tried to set the filter this way: >edbTable.Filter := '(col1, col2, col3)=(val1, val2, val3) and col4=val4 >but this is not working - only: >edbTable.Filter := col1=val1 and col2=val2 and col3=val3 and col4=val4 works ... unfortunately the optimizer chooses not in my favour ... I'm guessing here; with the query it may be applying col4 = val4 first and then the row test second and with the filter its just doing them all. Unfortunately I don't know a way to get an execution plan for a filter. Try edbTable.Filter := col4=val4 and (col1=val1 and col2=val2 and col3=val3) and see what that does for you. Can you also post the execution plan for the query. Roy Lambert |
Wed, Nov 5 2014 1:43 AM | Permanent Link |
Gruetzmacher | roy,
the query optimizer choses the index for col4 for an index scan and unfortunately does the row scan for the majority of rows ... that's exactly the problem. if the filtering could use the row value feature this would be the solution. i hope for edb3 that the query optimizer becomes more clever ... Roy Lambert wrote: Gruetzmacher >the index is col1, col2, col3 >actually the complete where-condition is (col1, col2, col3)=(val1, val2, val3) and col4=val4 >there is a single column index on col4 which actually is choose in the filter > >i tried to set the filter this way: >edbTable.Filter := '(col1, col2, col3)=(val1, val2, val3) and col4=val4 >but this is not working - only: >edbTable.Filter := col1=val1 and col2=val2 and col3=val3 and col4=val4 works ... unfortunately the optimizer chooses not in my favour ... I'm guessing here; with the query it may be applying col4 = val4 first and then the row test second and with the filter its just doing them all. Unfortunately I don't know a way to get an execution plan for a filter. Try edbTable.Filter := col4=val4 and (col1=val1 and col2=val2 and col3=val3) and see what that does for you. Can you also post the execution plan for the query. Roy Lambert |
Wed, Nov 5 2014 3:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gruetzmacher
>roy, >the query optimizer choses the index for col4 for an index scan and unfortunately does the row scan for the majority of rows ... that's exactly the problem. >if the filtering could use the row value feature this would be the solution. If you have a way of seeing this similar to the execution plan in EDBManager can you share it since I think it would be useful Apart from raising a support ticket to see if Tim can suggest anything I have one other idea. Create a compound field holding the data for col1,col2 & col3 and create an index on that. Roy Lambert |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |