September 22, 2009 at 8:16 am
I have a table in SQL 2000, indexed on a client and Material Number (not Clustered, not unique) that has been around for ages and is about 2 million lines long.
Multiple views in SQL 2000 use this table as a look up with no performance issues.
However, when I try and link to the table from my SQL 2005 server it is either not using the index or something is making it so slow that it times out when run (And I am linking on Client / Material number)
The total rows I am looking up should be only 50ish combinations of the two indexed fields.
Any thoughts
Thanks
Andy
September 23, 2009 at 3:38 am
I suspect that the problem is because all data from the tables is being copied to the SQL 2005 server, then the joins and filters are being applied.
You could try using OPENQUERY to cause the joins/filters to be applied on the SQL 2000 server.
Another option is to create a view or a sproc on the SQL 2000 server and select/execute that instead.
I think that there is a HINT that could be used on you original query - can't remember what it is at present, though 🙁
September 26, 2009 at 5:39 pm
It is REMOTE join hint:
From BOL:
REMOTE
Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.
If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table. If both tables are remote tables from the same data source, REMOTE is not required.
REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.
REMOTE can be used only for INNER JOIN operations.
HTH
Piotr
...and your only reply is slàinte mhath
September 29, 2009 at 3:38 pm
What account is used for the linked server setup, if it does not have the rights to execute DBCC SHOW_STATISTICS then that might be why the index is not chosen by the optimizer, the below article discusses the issue. Just a guess:-)
Andrew
September 29, 2009 at 4:23 pm
Wow, that's a good one Andrew :blink:
Andy, have you tried to run the query with exactly same data for join on the SQL 2000 locally? Would it pick the index? Did you try to apply the REMOTE join hint?
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply