July 7, 2015 at 12:30 am
Hi,
I have some Partitioned Views and on all queries using a table for the in clause, table elimination isn't happening.
Check Constraint is on the oid column
This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( '05231416529481', '06201479586431' )
This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE oid IN ( '05231416529481', '06201479586431' ) )
This is checking all tables (headingnames are unique), ive tried this for the last 3 hours on many different tables containing the oid column.
Unless I write the oid as in the above queries it just doesn't work.
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE headingname = 'TestSystem' )
If anyone could help me out with this it would be much appreciated.
Best Regards
Warren
July 7, 2015 at 5:09 am
What's the clustered index on the tables? I'll bet it's the OID column. If so, it can eliminate partitions. If the other column in use has no indexes or only a nonclustered index, then it must go to each table to identify the values before it can eliminate any tables from the operation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2015 at 8:33 pm
Hi, There is no clustered index on the owners table, the oid is a non-unique non-clustered index.
the partitioned tables clustered index is on the appointmentdate, non-unique but the oid is non-unique and non-clustered as well
I did put a clustered index on the owners.oid just as a test and it didnt change anything.
I didnt think the indexes came into play for eliminating?
July 8, 2015 at 1:41 am
I'm not understanding all your structures. In order for the partitions to be eliminated, you have to be able to filter on them. You're saying that partitions are eliminated in the first two queries, but not in the third, but, that the partitioning key is not OID, but some other column, not even a part of the filter criteria of the first to queries, but, that it still does partition elimination. That's not how it ought to work, so I'm absolutely missing something going on in your structures, your queries, your indexes.
Check the execution plan for the first two queries. Compare it to the third query. That will show you which structures are being referenced and how. That may lead you to a solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2015 at 8:45 pm
Just so you know, I looked you up, I didnt realise i was talking to someone who has given me so much help over the years (from you website). Thanks
This is a Partitioned View that uses union all on a whole bunch of tables with the same columns, not a true partioned table.
The check constraint in on the oid column of the OAP tables.
The check constraint as far as i know is what makes table elimination possible and if it works for the first 2 queries then why not the last?
I just restored my db to SQL 2014 with the same results. so i guess its not an sql 2008 bug.
The first 2 query plans only show the tables which match the OIDs, the last one shows every table with 1 execution on each
July 8, 2015 at 9:37 pm
wa220 (7/8/2015)
Just so you know, I looked you up, I didnt realise i was talking to someone who has given me so much help over the years (from you website). ThanksThis is a Partitioned View that uses union all on a whole bunch of tables with the same columns, not a true partioned table.
The check constraint in on the oid column of the OAP tables.
The check constraint as far as i know is what makes table elimination possible and if it works for the first 2 queries then why not the last?
I just restored my db to SQL 2014 with the same results. so i guess its not an sql 2008 bug.
The first 2 query plans only show the tables which match the OIDs, the last one shows every table with 1 execution on each
Here is my guess:
The first two queries have the oid values hard-coded. The last is using a subquery to identify the oids to be checked.
July 9, 2015 at 2:38 am
It must have something to do with parameter sniffing. The hard coded values are going to get specifics from the OID column. The query is open to interpretation. In other words, Lynn is probably right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply