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

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

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

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

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

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