How to improve the performance from joining to an openquery?

I got the query:

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where B = 21

This query is to slow; even the following query is faster.

SELECT *

FROM OpenQuery(ObjName, 'SELECT * FROM A')

The performance from the following is better:

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''Val'')')) as D

FROM [Test]

where B = 21

but how can I do make it dinamic? I mean this does not work!

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''’ + ID + ’'')')) as D

FROM [Test]

where B = 21

Has someone experience with this?

How does this perform?

Code Snippet

SELECT *

FROM [Test]

left join ( SELECT top 100 PERCENT* FROM OpenQuery(ObjName, 'SELECT * FROM A') as subA ) AS A

on ID = A.ID

where B = 21

|||Can you please tell which table the column B belongs? Please use aliases in your query for readability. There are many ways to optimize the query. For example, retrieve only the columns you need from the remote table. Why are you using SELECT * ? Is it necessary. If column B belongs to A then move that inside the pass-through query. Depending on the remote data source (looks like AS400 here) and the driver, SQL Server may not be able to push predicates to the remote server. There are many interfaces in the OLEDB provider for AS400 that needs to be implemented for SQL Server to do this. Also, if B belongs to table A then why do you need to the LEFT JOIN. You should doing inner join which is what will happen. If these suggestions doesn't work then simply dump the openquery results into a temporary table first and then join with the temporary table instead.|||

B belongs to test and I will end up with a teporal table thanks!

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where Test.B = 21

|||That will work but I can not restrict just the first records!