Why Select Query slow down when i use an integer column in select clause

I am trying to optimize the below query in SQL Server 2000. Every table has more than 300 000 rows except the Prod_Lot_Stage_Wise_Detl table which contains 2 million records.

When I execute the query, below, without including the LotStgWs.Finished_Pcs field (which belongs to the Prod_Lot_Stage_Wise_Detl table), the result comes within 20 seconds.

But, if I include the LotStgWs.Finished_Pcs field the execution time goes beyond 2 minutes. I don't understand what is going wrong here and how SQL Server 2000 deals with integer fields. The indexes are defined properly for each table.

SELECT 
 JangRecv.Jangad_Ref_Code,         
 LotRecv.Total_Pcs Org_Pcs,      
 LotRecv.Total_Cts Org_Cts,
 LotStgWs.Finished_Pcs,
        (Isnull(ReEst.Exp_Wt,LotRecv.Exp_Yield)) Exp_Yield
 FRO开发者_StackOverflowM 
 Prod_Jangad_Receive_Tran  JangRecv With(NoLock) Inner Join 
 Prod_Lot_receive_Tran   LotRecv  With(NoLock) On      (JangRecv.Jangad_Seq_No = LotRecv.Jangad_Seq_No) Inner Join
 Prod_Lot_Stage_Wise_Detl  LotStgWs With(NoLock) On (
          LotStgWs.Jangad_Seq_No = LotRecv.Jangad_Seq_No  And 
          LotStgWs.Lot_Seq_No = LotRecv.Lot_Seq_No   And
          LotStgWs.Stage_Seq_No  = ISNULL(
                     (SELECT  MAX(Delv_Stage_Seq_No) 
               FROM  Prod_Lot_Delivery_Tran      
               WHERE  Jangad_Seq_No = JangRecv.Jangad_Seq_No
               ), JangRecv.Exp_Delv_Stage_Seq_No
              )
             ) Left Outer Join
 PPCS_QMA_Diamond_RE_Estimation  ReEst    With(NoLock) On (LotStgWs.Jangad_Seq_No = ReEst.Jangad_Seq_No And LotStgWs.Lot_Seq_No = ReEst.Lot_Seq_No And LotStgWs.Stage_Seq_No = ReEst.Stage_Seq_No)
Where 
 LotRecv.Lot_Recv_Div_Seq_No = 1
ORDER BY  JangRecv.Jangad_Ref_Code, LotRecv.Lot_Num_Alias

Make sure you have indexes on everything you join on, especially on that large table. The difference is nontrivial: Searching (or joining) on a non-index column requires a full table scan, which is of linear complexity (i.e., for two million records, you need to perform two million comparisons worst-case). On an indexed column, the database engine can use a binary search, which has logarithmic complexity (i.e., for two million records, you need 21 comparisons worst-case).

If I am understanding the problem correctly then the only thing you are changing in the query is the select of LotStgWs.Finished_Pcs... If that is right I have had this similar issue before in SQL Server 2000... Throw an index on LotStgWs.Finished_Pcs. This has worked for me before. It doesn't seem to make a whole lot of sense but it works. Best of luck

You could also try to rebuild the indexes: http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx.

But anyway, If I were you, I would try to improve my database structure, because it's not a good tecnique to use such complex sub-select when joining those big tables.