Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2008-04-25 : 18:34:08
|
| Hi,I have a SQL server: SQL20052 databasesdb_repl: contains replicated data from mainframedb_my: my databasedb_my.vwRepl_WorkOrders: Select 1,2,3 from db_repl.dbo.WorkOrdersdb_my.vwWorkOrdersOpen: select 1,2,3 from db_my.vwRepl_WorkOrders where Status=1which approach is more optimal to do in order to find open work orders from Texas:[select 1,2,3 from db_repl.dbo.workOrders where Status=1 and State=1orselect 1,2,3 from db_my.vwWorkOrdersOpenwhere where State=1] Is it best practice to use 'subviews' that acess db_repl directly or acess views in my_db that acess db_repl (2 hops or more if views call other views that call other views) Answer might be the obvious but just in case I am missing something here .Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-25 : 18:36:10
|
You have to examine the query plans.I think SQL Server will be able to "see through" all "hops" and make an optimzed query.But then again, it might depend on the view definitions.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-26 : 09:34:52
|
| A view is just a convenience for the query writer, the execution plan will be based on the underlying tables.Use Peso's advice to examine the query plans of either technique to see if my point is proved. The plans may be different but I don't believe you will see the view in refereneced in the plans.Be One with the OptimizerTG |
|
|
|
|
|
|
views on views