Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Location
    The Netherlands
    Posts
    20

    Question Unanswered: Too many indexes on table

    Hello,

    We have a table with 37 columns.
    On most of the columns can be searched and ordered from a website.

    The table has about 22.000.000 records.

    We have a lot of indexes to improve the sql statements.
    But we have the problem that the searching and ordering is so dynamic, we can't have an index for all the statements.

    We have an id to go from the 37 columns table to a table with a few columns and add a table with property and value columns.

    For example we have a table with columns id,c1,c2,c3,c4,c5,...
    We make a table1 with columns id,c1,c2,c3 and a table2 with columns idt1,property, value.
    And property gets value c4,c5,... and value gets the value of that property.

    We now only need een index on table2 for the two columns.

    Sql statements change from :
    Code:
    select * from table where c4=? and c5=? order by c6
    to:
    Code:
    select * from table1 left join table2 on idt1=id and property='c6' 
    where exists (select 1 from table2 where property='c4' and value=? and idt1=id) 
    and exists (select 1 from table2 where property='c5' and value=? and idt1=id) 
    order by table2.value
    We are wondering if this is a good solution and/or if there are better solutions...

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    Couple of questions.
    1. Normally there is something that limits the results in this type of process, I see in your explanation that there is an "id" column. Is this value used to limit the result set? If so how well does it filter the data? Or does the user have the potential to bring back all 22 million records?
    2. Assuming the user has the potential to build a result with 22 million records, how often are these tables updated? My thinking is you could pre-build the join results in a MQT. This MQT would contain nulls where there is not a match and values where this is one. The MQT could be fairly large, but for select access you would have really good results.
    3. Also, since this is for OLTP (assuming since a website), make sure you include FETCH FIRST n ROWS ONLY, this will encourage the optimizer to use nested loop joins, rather than hash joins.

    Hope this did not confuse things even more :-)

  3. #3
    Join Date
    Nov 2010
    Location
    The Netherlands
    Posts
    20
    Quote Originally Posted by azready View Post
    Couple of questions.
    1. Normally there is something that limits the results in this type of process, I see in your explanation that there is an "id" column. Is this value used to limit the result set? If so how well does it filter the data? Or does the user have the potential to bring back all 22 million records?
    2. Assuming the user has the potential to build a result with 22 million records, how often are these tables updated? My thinking is you could pre-build the join results in a MQT. This MQT would contain nulls where there is not a match and values where this is one. The MQT could be fairly large, but for select access you would have really good results.
    3. Also, since this is for OLTP (assuming since a website), make sure you include FETCH FIRST n ROWS ONLY, this will encourage the optimizer to use nested loop joins, rather than hash joins.

    Hope this did not confuse things even more :-)
    1. The user sees a page with maximum x numbers (25-500) of records. These records are selected with a sql statement like
    Code:
    select * from ( select rownumber() over(order by TIMESTAMP_RECEIVED desc) as rownumber_, ... from TABLE where c1=x and c2=y order by TIMESTAMP_RECEIVED desc ) where rownumber_ <= ?
    2. Te table is continously updated. We've used MQT's before but stopped using them. I think it was because of performance issues. But we could look into it again.

    3. See my answer in point 1.

    But do you think our solution can improve the performance? And is it a good solution?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What you are proposing is commonly called an EAV (entity-attribute-value) model. Folks who implement it for other reasons, e.g. flexibility of adding arbitrary attributes without changing the data model, often complain about poor performance afterwards, so it is unlikely that in your situation it will perform better.

    However, in the time you've been waiting for an answer you could have actually tested it yourself.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2010
    Location
    The Netherlands
    Posts
    20
    Quote Originally Posted by n_i View Post
    However, in the time you've been waiting for an answer you could have actually tested it yourself.
    If i had the time, i could indeed...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •