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 |
|
NifflerX
Starting Member
29 Posts |
 Posted - 2011-06-15 : 14:58:35
|
Hello,I have a database sitting on a SQL Server 2000 VM. This database has two types of views. The first type is a view that just pulls data from a physical SQL Server 2000 machine, using a linked server. So the view looks like this:SELECT FIELD1, FIELD2 FROM Linked_Server.catalog.schema.table_name The second type of views are ones that take the above views and combine them via joins.I am trying to migrate to a new SQL Server 2008 R2 installation so I backed up my database from my VM and restored it on my new SQL Server 2008 R2 installation. Then I created a linked server that is a replica of the one that exists of my 2000 VM and updated statistics and rebuilt the indexes. After all that the views that just pull data via the linked server worked fine. Very fast and no issues. However, the views that took this data and tried to join it starting running extremely slowly.I thought the issue was network overhead so I create yet another linked server on my 2008 server. This linked server connected back to my original 2000 VM server. I figured that running queries this way would take a very long time given that I was now going through 2 linked servers (one to get to my original VM and one to get to the physical server with the data on it). However, this way actually was much faster running against the linked server on my SQL 2008 box.Does anyone know what configuration settings I should look at or places I can check to try to get the views running quickly on my 2008 server? I can only assume that the issue is the way in which the 2008 engine handles these queries versus the 2000 engine. Thank you so much.-NifflerX |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-06-17 : 13:41:47
|
Try using open query, it made my stuff faster...SELECT * FROM OPENQUERY(Linked_Server,'SELECT FIELD1, FIELD2 FROM catalog.schema.table_name') |
 |
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-06-19 : 23:10:17
|
quote:
Originally posted by denis_the_thief
Try using open query, it made my stuff faster...SELECT * FROM OPENQUERY(Linked_Server,'SELECT FIELD1, FIELD2 FROM catalog.schema.table_name')
Thanks so much for the reply. I've tried open query, and it makes things much faster but I've run into a few snags. I've got some functions and views defined on the local 2008 VM that don't exist on the remote machine, the physical 2000 server. In my old setup it was no issue to just bring back the views I needed from the physical 2000 server and combine them with my local functions and views, but now those joins are taking a long time, hence this thread. My next idea is going to be to try to wrap the views that pull data directly from the 2000 physical box, the ones that are running quickly, around a function that caches the data pulled into a temp table with indexes. |
|
|
|
|
|
|
|
|
Linked server on 2008 slower than on 2000