Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a sql query on a large table (>17 million rows) that some times does not populate all the predicates before performing index seek, which results in not filtering out enough rows and slowing the query down significantly (hours). MS SQL 2005 database is used and this SQL query is generated by Hibernate.

Here is how the simplified query looks like:

SELECT MAIN_TABLE.FIELD_1,
       MAIN_TABLE.FIELD_2,
       MAIN_TABLE.FIELD_3,
       MAIN_TABLE.FIELD_4,
       TABLE_1.FIELD_1,
       TABLE_1.FIELD_2,
       TABLE_2.FIELD_1,
       TABLE_2.FIELD_2,
       TABLE_3.FIELD_1
FROM MAIN_TABLE
     INNER JOIN TABLE_1 ON MAIN_TABLE.TABLE_1_ID = TABLE_1.ID
     INNER JOIN TABLE_2 ON MAIN_TABLE.TABLE_2_ID = TABLE_2.ID
     LEFT OUTER JOIN TABLE_3 ON MAIN_TABLE.TABLE_3_ID = TABLE_3.ID
WHERE MAIN_TABLE.SOME_ID IN (SELECT SOME_ID FROM PARTITION_VIEW 
                             WHERE PARTITION_VIEW.SOME_FIELD_1 = [VALUE_1]
                             AND PARTITION_VIEW.SOME_FIELD_2 = [VALUE_2]
                             AND PARTITION_VIEW.SOME_FIELD_3 = [VALUE_3])
AND MAIN_TABLE.DATE BETWEEN [DATE_RANGE_1] AND [DATE_RANGE_2]
AND TABLE_1.STATE = [STATE]

The PARTITION_VIEW is a UNION of materialized views that all have index on columns SOME_FIELD_1, SOME_FIELD_2, and SOME_FIELD_3.

In the MAIN_TABLE, we have index on columns DATE and SOME_ID and this is the index that gets utilized which is correct. When I checked the query plan from the SQL server management studio, I found out that in some cases such as when the date range is large (one year), the predicate used is the DATE column only. Then only after all the rows are found within given date range is found, the result set is filtered out by the given SOME_IDs. It seems like the query optimizer did not pick the best query plan available. When the exact query is directly executed from the SQL server management studio, everything performs correctly. This suboptimal query plan is used only when the query is executed from the application through Hibernate.

One thing I tried was to create actual table PARTITION_TABLE which copied over all the data from the PARTITION_VIEW and retrieved SOME_IDs from the PARTITION_TABLE, then the index on MAIN_TABLE was using all the predicates (SOME_ID, DATE). I want to keep using the view if possible since it automatically reflects the changes.

I am curious whether it is possible to keep using the union of materialized views and ensure that the index on the MAIN_TABLE will always retrieve the SOME_ID on the where clause first so that it can be used as part of the predicate in the index.

Any suggestions?

Thank you guys in advance!

share|improve this question

migrated from stackoverflow.com Oct 3 '12 at 23:51

This question came from our site for professional and enthusiast programmers.

    
hello, how many rows you get form SELECT SOME_ID FROM PARTITION_VIEW WHERE PARTITION_VIEW.SOME_FIELD_1 = [VALUE_1] AND PARTITION_VIEW.SOME_FIELD_2 = [VALUE_2] AND PARTITION_VIEW.SOME_FIELD_3 = [VALUE_3] ? –  Florin Ghita Dec 7 '11 at 9:03
    
PARTITION_VIEW estimates about 5000 rows where actual rows returned is couple hundreds at most, usually under 50. The PARTITION_TABLE estimates about 400 rows. Thanks! –  user941479 Dec 8 '11 at 22:37
1  
ok, I think you should try an index on tuple (some_id, date) in this order. –  Florin Ghita Dec 9 '11 at 6:54
    
Could you please explain why the index on tuple (date, some_id) won't be used? Is it because the date predicate is a range, not a equality predicate? –  user941479 Dec 15 '11 at 22:35
    
hmmm... it is almost the same thing. Do you have an index on this tuple despite the order? –  Florin Ghita Dec 16 '11 at 8:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.