I'm doing some development of a view using existing views. In my test query, I'm creating a temp table and I want to join it to a second view. So, I'm doing this:
DECLARE @TroutStamps TABLE (
INSERT INTO @TroutStamps (CustomerID, TypeID, IssuedDate)
SELECT CustomerID, TypeID, IssuedDate
WHERE (TypeID = 150) AND Void = 0
print 'Filled @TroutStamps at ' + CAST(getdate() AS varchar(50))
SELECT * FROM @TroutStamps
FROM ALSReplicator.vw_ALS_License_ORA li
INNER JOIN vw_ALS_LifetimeTransaction_ORA lt on lt.CustomerID = li.CustomerID -- Oracle
LEFT JOIN @TroutStamps sTrout ON li.CustomerID = sTrout.CustomerID AND lt.IssuedDate = sTrout.IssuedDate
WHERE li.TypeID = 180 and li.void = 0
The full query, with the join takes over 5 minutes.
If I do the full query, but drop the join, it takes 30 seconds.
So, I feel I should be able to do a more efficient join. Any ideas? What am I doing wrong?