Performance On View

Hey there,

I have a view on 5 tables.
The first [tblOrder] 62000 records
The second joined on the first [tblOrderRegel] 2140000 records
The third joined on the second [tblAssortimentSamenstelling] 5800 records
The fourth [tblArtikelVerpakking] 1650 records
And the last also joined on the third [tblAssortiment] 30 records

When I use the next query
SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder in(SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6)
GO

It will take more than 60 seconds before the resuld is there

The sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' returns a list of ID's. When I make the same select with this ID's instead of the sub select the result returns in 1 second

SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder IN( 4296, 4374, 4433, 9261, 9714, 9732, 9876, 10182, 10223, 17677, 18117, 18393, 28116, 28125, 28246, 31263, 32384, 33482, 33605, 33721, 33792, 36838, 38000, 40579, 40920, 50205, 52292, 52884)
GO

In the first select I have on all tables en index SCAN on the primairy Index with Hash Match/Inner Join to match.

In the second select I have on all tables en index SEEK on the primairy Index with Nested loop/Inner Join to match.

For the sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' is not the primairy key used but an other Unique index Containing three columns.
[IDOrder] (also the primairy Key),
[IDStatus] (1, 2 or 3. 90% = 3),
and a date field.

In the view on the subselect is an UNION of three selects.
SELECT IDOrder, Date_1
From tblOrder
WHERE IDStatus = 1
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 2
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 3

Thats the reason he usses the Unique Index

Is using this index the reason that he does not SEEK on the first table (also tblOrder) of the view in my select???
In other words: Does SQL-server can use only 1 index on a table for a SEEK?

I hope somebody can give me an answer on this complex story.

Thanks Jaap

By the way. I have already tried selects with a INNNER JOIN in stead of a sub query and also a corralated sub query.