April 23, 2012 at 10:26 am
We recently decided to partition a table in one database that is referenced in another. The current reference is a view. Will the view take advantage of the partitioning? Would a synonym?
Thanks.
April 23, 2012 at 10:49 am
That really depends upon the type of partitioning, and what the data is partitioned on.
I would assume that either access method would perform similar if the partitioning was done to optimize the performance of the queries used.
For the record, the two methods are very different. A view is essentially a predefined query that can be accessed at any time.
A Synonym is a local reference to a database object. It can be used to give a friendly name to a local object, or to an object in another database or on another server.
April 23, 2012 at 10:56 am
In simple words, if you have a table partitioned by Year and you query view / synonyms, both will behave the same way because SQL Server replaces synonyms with actual table names and views with query which can ultimately take advantage of partitioning, if queried upon partitioning key (Year in this case).
April 23, 2012 at 10:57 am
The synonym... Yes. It is simply another name for the object. EDIT: removed some info that I realized was the wrong path of thinking for this...
Jared
CE - Microsoft
April 23, 2012 at 11:07 am
SQL Server rewrites the query to optimize it so the predicate in the outer query (for view) can be moved to inner query (view definition). It’s very much dependent on Optimizer, where it finds the predicate to be suitable. In few cases say joins, join predicates can also be added to view query or vice a versa.
April 23, 2012 at 11:12 am
Dev (4/23/2012)
SQL Server rewrites the query to optimize it so the predicate in the outer query (for view) can be moved to inner query (view definition). It’s very much dependent on Optimizer, where it finds the predicate to be suitable. In few cases say joins, join predicates can also be added to view query or vice a versa.
True, and I would assume that partitioning wouldn't change it. I suppose the important thing here is writing the query to properly use the partition scheme.
Jared
CE - Microsoft
April 23, 2012 at 11:30 am
Precisely. I already clarified it in last post ‘if queried upon partitioning key’.
April 23, 2012 at 11:56 am
Thanks All.
I can add a little clarification, if this will make a difference. The partitioning on the original table was done on Year/Month. It was done mainly for the benefit on the database it belongs to. In this database the table spans 5+ years of data and contains close to a billion rows, meaning that there were a lot of index pages that needed to be look at in order to work through the tree. By partitioning we are expecting that the traversal of the tree will be much faster because in will be working through only 1 month's worth of data, in a simple case. There are several other databases need access to information in that table. In the current setup a view exists in each of these dependant databases simply selecting from the table. Usually the Year/Month values are part of the query. So, I think my question is 'When using the view approach will the query engine be aware of the underlying table's partitioned nature and know that it only needs to look at 1 month's worth of data to find the needed record, or will it treat the table as though it were not partitioned?' I expecting that in the synonym approach, the query engine is aware of the tables partitioned nature and it will take advantage of it. Making this the better approach.
I'm obviously trying to avoid having to do a lot of benchmarking if there is an available pool of knowledge.
Thanks again.
April 23, 2012 at 12:05 pm
It’s simple. Run a query against the view and pull the execution plan. If you find a predicate on partition id, it indicates partitioning is helping to your query.
More: http://msdn.microsoft.com/en-US/library/ms345599(v=sql.105).aspx
April 23, 2012 at 1:50 pm
The execution plans were identical using the view and the synonym. Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply