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.
精彩评论