3 added 604 characters in body
source | link

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

EDIT: Given that FORCE ORDER didn't work, have you thought about doing something like this:

WHERE T1.Id IN (SELECT Id FROM #tmpIds)

2nd EDIT: One more try, this one is a bit complex though.

Can you do something like this:

On the remote server create a permanent "temporary" table

CREATE TABLE tmpTable1 (Id INT)

Then (still on the remote server) create a view

CREATE VIEW queryView AS
SELECT Table1.* 
FROM Table1
JOIN tmpTable1
    ON Table1.Id = tmpTable1.Id

Then in your process on your "home" instance

DELETE FROM Server.Database.dbo.tmpTable1
INSERT INTO Server.Database.dbo.tmpTable1 VALUES
SELECT * FROM #tmpIds

Then in your query join to Server.Database.dbo.queryView

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

EDIT: Given that FORCE ORDER didn't work, have you thought about doing something like this:

WHERE T1.Id IN (SELECT Id FROM #tmpIds)

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

EDIT: Given that FORCE ORDER didn't work, have you thought about doing something like this:

WHERE T1.Id IN (SELECT Id FROM #tmpIds)

2nd EDIT: One more try, this one is a bit complex though.

Can you do something like this:

On the remote server create a permanent "temporary" table

CREATE TABLE tmpTable1 (Id INT)

Then (still on the remote server) create a view

CREATE VIEW queryView AS
SELECT Table1.* 
FROM Table1
JOIN tmpTable1
    ON Table1.Id = tmpTable1.Id

Then in your process on your "home" instance

DELETE FROM Server.Database.dbo.tmpTable1
INSERT INTO Server.Database.dbo.tmpTable1 VALUES
SELECT * FROM #tmpIds

Then in your query join to Server.Database.dbo.queryView

2 added 148 characters in body
source | link

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

EDIT: Given that FORCE ORDER didn't work, have you thought about doing something like this:

WHERE T1.Id IN (SELECT Id FROM #tmpIds)

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)

EDIT: Given that FORCE ORDER didn't work, have you thought about doing something like this:

WHERE T1.Id IN (SELECT Id FROM #tmpIds)
1
source | link

Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id
OPTION (FORCE ORDER)