Left outer join on remote sql server hangs
-
I have a join which is matching parts in a local table with parts in a linked server's table. If I do an inner join between the local table and the remote table, results appear very quickly. If I do an outer join, results take a very long time. In both cases, the parts are known to exist in both tables. Looking at the query plan - I was looking for a way to attach this via a word doc but couldn't find out how to do it - it appears that the inner join is sending the part number to the remote server and executing a query like: select blah from blah where part = part from local table. This is quick. On the other hand, the outer join is sending a query like: select part from blah order by part-num asc. The rowcount for this query is 31,948 vs 1 for the inner join. I suspect this is why the query is slow, and don't understand why the inner join query should be any different than the outer join query. Any ideas?
-
It most likely boils down index decisions. For example if the clustered index is on the column you are joining to then the query manager may decide to use the clustered index. However if you do a left join the index decision may not apply and it will look to the where clause to find a sutible index otherwise it will scan the table if something in the where limits the data and no suitable index exists. Also, the left join may just pull on the data across and not utilize any indexes then scan the table when it comes over so the load is on the local machine and subject to that machines abilities. When using joins to remote you will sometimes find using openquery to the remote data with a select to pick the data related to that server will sometimes prevail.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 09/06/2002 6:58:31 PM
-
At the risk of having my eyes taped in place as I roll them
...I don't understand why the query would be optimized any differently if it were an inner or a left outer join. (Now full outer join is a different story). The remote table still needs to be looked up by part number, only in the left-outer case a null would be returned instead of some data. I think this is a bug.
I've found a workaround suggested by Peter Borchers in the http://www.sqlmag.com forum, and that is to make space in my local table for data values from the remote table, then do an update localtable set blah = blah from localtable inner remote join remotetable on blah = blah. (My join happens to inner join 2 tables on the remote server, you might be able to get away without the remote keyword with a single inner join, but check the estimated query plan to be sure) The optimizer seems to be happy with inner remote joins and doesn't return the whole contents of the remote tables.
-
Linked servers can behave differently depending on the query
The UK SSUG has a nice example page on it
(http://www.sqlserverfaq.com/)
Search for the word 'linked', and pick the article called 'Linked Servers: The example queries'
Steven
-
Sorry without your code and the fact you never stated which was the left table I incorrectly assumed it was the remote table not the local. However, with a linked server involved it still may have opted not to use the ON portion of you code and went looking in the where for what it thought was suitable. Linked server just do not work the same as all local.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
...Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply