Why does this SQL query take 8 hours to finish?

There is a simple SQL JOIN statement below:

SELECT     
    REC.[BarCode]
   ,REC.[PASSEDPROCESS]
   ,REC.[PASSEDNODE]
   ,REC.[ENABLE]
   ,REC.[ScanTime]
   ,REC.[ID]
   ,REC.[Se_Scanner]
   ,REC.[UserCode]
   ,REC.[aufnr]
   ,REC.[dispatcher]
   ,REC.[matnr]
   ,REC.[unitcount]
   ,REC.[maktx]
   ,REC.[color]
   ,REC.[machinecode]
   ,P.PR_NAME
   ,N.NO_NAME
   ,I.[inventoryID]
   ,I.[status]
   FROM  tbBCScanRec as REC  
       left join TB_R_INVENTORY_BARCODE as R 
         ON    REC.[BarCode] = R.[barcode] 
           AND REC.[PASSEDPROCESS] = R.[process]
           AND REC.[PASSEDNODE] = R.[node]
       left join TB_INVENTORY  as I 
         ON R.[inventid] = I.[id]
       INNER JOIN  TB_NODE as N 
         ON N.NO_ID  =  REC.PASSEDNODE
       INNER JOIN  TB_PROCESS  as P 
         ON P.PR_CODE    = REC.PASSEDPROCESS

The table tbBCScanRec has 556553 records while the table TB_R_INVENTORY_BARCODE开发者_Python百科 has 260513 reccords and the table TB_INVENTORY has 7688. However, the last two tables (TB_NODE and TB_PROCESS) both have fewer than 30 records.

Incredibly, when it runs in SQL Server 2005, it takes 8 hours to return the result set.

Why does it take so much time to execute?

If the two inner joins are removed, it takes just ten seconds to finish running.

What is the matter?

There are at least two UNIQUE NONCLUSTERED INDEXes.

One is IX_INVENTORY_BARCODE_PROCESS_NODE on the table TB_R_INVENTORY_BARCODE, which covers four columns (inventid, barcode, process, and node).

The other is IX_BARCODE_PROCESS_NODE on the table tbBCScanRec, which covers three columns (BarCode, PASSEDPROCESS, and PASSEDNODE).

Well, standard answer to questions like this:

1. Make sure you have all the necessary indexes in place, i.e. indexes on N.NO_ID, REC.PASSEDNODE, P.PR_CODE, REC.PASSEDPROCESS
2. Make sure that the types of the columns you join on are the same, so that no implicit conversion is necessary.

You are working with around (556553 *30 *30) 500 millions of rows. You probably have to add indexes on your tables.

If you are using SQL server, you can watch the plan query to see where you are losing time. See the documentation here : http://msdn.microsoft.com/en-us/library/ms190623(v=sql.90).aspx

The query plan will help you to create indexes.

When you check the indexing, there should be clustered indexes as well - the nonclustered indexes use the clustered index, so not having one would render the nonclustered useless. Out-dated statistics could also be a problem.

However, why do you need to fetch ALL of the data? What is the purpose of that? You should have WHERE clauses restricting the result set to only what you need.