SQL Server 2005 - Join does not terminate - Speed issue?

does anybody have an idea why this join 开发者_Go百科operation does not terminate:

SELECT * FROM
(SELECT LOWER([Computer Name]) AS 'cname' FROM table1)
AS export,
(SELECT AssetID AS 'CCID', Hostname AS 'cname' FROM table2 WHERE LastVersionFlag=1)
AS known

WHERE export.cname = LEFT(known.cname, LEN(export.cname));

The problem which I want to solve is:

• Table1 column "cname" contains: "pc1"
• Table2 column "hostname" contains: "pc1.domain.com"
• Table2 also contains column "CCID": 123123

As result of the query I need to receive the CCID of each "cname" from table1.

EDIT:

The query is running for >15minutes now and produces no output, nor terminates.

• Table1 contains about 100.000rows
• Table2 contains about 200.000rows

Any ideas how to speed up the query?!

EDIT2:

Indices are put on Table1 cname and Table2 cname

The task schedule looks like this: http://i55.tinypic.com/1z3o0t4.png

Is this what you're looking for, a basic join?

SELECT known.CCID, export.[Computer Name] AS cname 
FROM
table1 AS export
INNER JOIN table2 AS known ON LOWER(export.[Computer Name]) = LOWER(LEFT(known.cname, LEN(export.[Computer Name])))
WHERE LastVersionFlag=1 

Edit: Here are some suggestions for speeding up the query:

Firstly, the string operation in the join are very costly. You can avoid having to call lower() if you force a case insensitive collation for the join.

e.g. ...ON known.cname = export.[Computed Column] collate SQL_Latin1_General_Cp437_CI_AS_KI_WI

Your best move would be to create a persisted computed column on table2 that just contains the cname using your formula from the join LEFT(known.cname, LEN(export.[Computer Name])) , then you can put an index on the computed column as well as the cname column of table1. Once done, your join will be a simple equality instead of a heavily manipulated comparison. This will allow the query engine to make a more optimized plan.

Just a side note: SQL Server is working in case insensitive mode by default (if you didn't do any changes), so you dont have to care about upper/lower-case normally. Using functions like LOWER prevents the usage of indexes.

And to speed up the last WHERE, you could rewrite it with a LIKE that will take advantage of an index on the involved columns (if you got one). That would be something like (as in devio's answer):

known.cname LIKE (export.cname + '%')

Bringing all suggestions together would give you following query.
With proper indexing and the number of records you mention, it shouldn't take minutes to return the results.

SELECT  cname = t1.[Computer Name] 
        , CCID = k.AssetID
        , Hostname = k.cname
FROM    table1 t1
        INNER JOIN known k ON k.Hostname LIKE t1.[Computer Name]  + '%'
WHERE   k.LastVersionFlag = 1        

My guess is your query does not perform because of the size of the data.

It needs to generate the Cartesian product of 100.000 * 200.000 rows, and then apply the WHERE conditions.

SQL Server comparison is case-insensitive if the column's collation is set to case-insensitive (defaulted by database and server collations).

To speed up the join operation, add (unique?) indexes on table1.[Computer Name] and table2.Hostname.

Modify your query to

SELECT known.CCID, export.[Computer Name] AS cname 
FROM table1 AS export
INNER JOIN table2 AS known 
    ON known.Hostname LIKE export.[Computer Name] + '%' 
    AND LastVersionFlag=1 

If all your hostnames use dotted cname notation, use '.%'.