I have a SQL Server query on a large table (>17 million rows) that sometimes does not populate all the predicates before performing an index seek, which results in not filtering out enough rows and slowing the query down significantly (hours).
Microsoft SQL Server 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 an 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 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 within the given date range are 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 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.
Is it 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?
