Why does this JOIN take so long?

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 (
 CustomerID int,
 TypeID int,
 IssuedDate datetime
)

INSERT INTO @TroutStamps (CustomerID, TypeID, IssuedDate)
SELECT CustomerID, TypeID, IssuedDate
FROM ALSReplicator.vw_ALS_License_ORA
WHERE (TypeID = 150) AND Void = 0
print 'Filled @TroutStamps at ' + CAST(getdate() AS varchar(50))

SELECT * FROM @TroutStamps

SELECT
 li.CustomerID,
 lt.TypeID,
 'L' CardType,
 li.IssuedDate,
 lt.IssuedDate OriginalIssuedDate
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 table variable gets just under 600k rows and takes about 30 seconds.

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?

Put an index on your temp table. While your actually in thinking mode think about whether you actually need a temp table.

foreachdev said:

Put an index on your temp table. While your actually in thinking mode think about whether you actually need a temp table.

I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

So, my result set will have 3 more columns than what is shown:
HasType150, HasType151, HasType152

I tried doing a function to return those columns, but that took hours.

qwert231 said:

foreachdev said:

*snip*

I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

So, my result set will have 3 more columns than what is shown:
HasType150, HasType151, HasType152

I tried doing a function to return those columns, but that took hours.

So instead of making a primary key, create a unique index based upon all three columns.  A primary key is not a requirement but I will admit normally a good idea.

qwert231 said:

foreachdev said:

*snip*

I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

So, my result set will have 3 more columns than what is shown:
HasType150, HasType151, HasType152

I tried doing a function to return those columns, but that took hours.

A primary key is not necessarily a single column. You can create a primary key composed of the 3 columns, if you want to.

TommyCarlier said:

qwert231 said:

*snip*

A primary key is not necessarily a single column. You can create a primary key composed of the 3 columns, if you want to.

You can also have as many different indexes as you need. Only one will be clustered because that means they are sorted by the column(s) on insert. (By Default in MSSS the primary key is, but doesn't have to be and usually is a waste) DateTime fields, quantities, and other columns that would be used in a where clause and result in multi row returns are good clustered key canidates.

Is this MSSQL ? or someother SQL Engine ? I am just curious as to which engine you are using.