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

Question

I am trying to optimize the below query in SQL Server 2000. Every table has more than 3 Lakh (300,000) rows except the Prod_Lot_Stage_Wise_Detl table which contains around 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.

Any solutions regarding this problem are most welcome... Thanks in advance.

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
 FROM 
 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

Answer 1

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).

Answer 2

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

Answer 3

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.