June 15, 2006 at 11:12 am
We need to pull data from one db server into another.
We setup a linked server, but then realized that a couple of the fields we text and
we had to make some modificaitons to our joins.
So we modified our joins, but are now having performance issues.
I know why we are having performance issues, just want to know if there is anything else I can do to speed it up...
I've included the workaround below.
CAUSE
This behavior is by design because FULL OUTER JOINs do not allow you to access a TEXT column through a linked server.
Back to the top
WORKAROUND
Use a UNION ALL statement with a LEFT OUTER JOIN and RIGHT OUTER JOIN. The following example may be helpful: SELECT * FROM s LEFT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i
UNION ALL
SELECT * FROM s RIGHT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i
If you want to eliminate duplicate rows, use a combination of LEFT and RIGHT OUTER JOINs with a UNION statement and you can use the system defined function SUBSTRING. For example: SELECT s.i, s.j, SUBSTRING(s.t, 1,200), t.i, t.j, SUBSTRING(t.t, 1, 200) FROM s LEFT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i
UNION
SELECT s.i, s.j, SUBSTRING(s.t, 1,200), t.i, t.j, SUBSTRING(t.t, 1, 200) FROM s RIGHT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i
For information about how the tables and linked servers in the preceding queries are set up, refer to the "More Information" section of this article.
Back to the top
June 19, 2006 at 8:00 am
This was removed by the editor as SPAM
June 19, 2006 at 11:15 am
This is unequivocally better than the solution those MS types came up with:
all
s.*,t.* from linked_server1.pubs.dbo.t t left join s on s.i= t.i
s.i is null
BTW, can someone tell them that 'known limitation' is not really the same as 'by design'?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply