Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
msrs
32 Posts |
 Posted - 2008-05-01 : 10:08:24
|
| Dear all, The below query take 7 min to execute so i want optimize the query.please any suggestions..........SELECT DISTINCT VC.O_Id C_Id, VC.Name C_Name,VB.Org_Id B_Id, VB.code S_Code,VB.Name S_Name, mt12.COLUMN003 M_D_Code, mt12.COLUMN004 M_D_Name,CQ.COLUMN004 R_Code,CQ.COLUMN005 R_Date, CQ.COLUMN006 Ser,CQ.COLUMN008 R_Nature,CQ.COLUMN011 E_Date,mt26.COLUMN003 W_Code, mt26.COLUMN004 W_Name,mt17.COLUMN005 V_Code,mt17.COLUMN006 V_Name, mt19.column002 I_Code,mt19.column003 I_Name, mt19.COLUMN0001 R_I_No,mt92.COLUMN001 B_Id,mt92.COLUMN005 B_No, CASE mt92.COLUMN006 WHEN '0' THEN 'Ser' WHEN '1' THEN 'Un-Ser' WHEN '2' THEN 'Ret' WHEN '3' THEN 'Retd' WHEN '4' THEN 'Rep' WHEN '5' THEN 'Repd' WHEN '6' THEN 'Con' WHEN '7' THEN 'Cond' ELSE mt92.COLUMN006 END S_C_Type,mt20.COLUMN003 T_G_Code,mt20.COLUMN004 T_G_Name, V.U_Code,V.U_Name,mt19.column005 I_Quantity,mt20.COLUMN003 T_Code, mt20.COLUMN004 T_Name,mt59.COLUMN005 T_Price,VR.code C_L_Code, VR.Name C_L_Name FROM tab90 CQ INNER JOIN tab91 mt19 ON mt19.COLUMN002 = CQ.COLUMN001 LEFT JOIN tab92 mt92 ON mt92.COLUMN002 = CQ.COLUMN001 LEFT JOIN tab93 mt93 ON mt93.COLUMN004 = CQ.COLUMN001 INNER JOIN tab12 mt12 ON mt12.COLUMN001 = CQ.COLUMN003 LEFT JOIN tab26 mt26 ON mt26.COLUMN001 = CQ.COLUMN009 LEFT JOIN tab20 mt20 ON mt20.COLUMN001 = mt93.COLUMN005 LEFT JOIN tab59 mt59 ON mt59.COLUMN002=mt20.COLUMN001 LEFT JOIN tab17 mt17 ON mt17.COLUMN001 = CQ.COLUMN010 INNER JOIN VM V ON V.UOM_ID = mt19.COLUMN004 INNER JOIN tab19 mt19 ON mt19.COLUMN001 = mt19.COLUMN003 INNER JOIN vOrg VR ON CQ.COLUMN007 = VR.Org_Id INNER JOIN vOr VB ON CQ.COLUMN002 = VB.Org_Id INNER JOIN vOr VC ON VB.Top_Parent = VC.Org_Id WHERE CQ.COLUMN005 Between '02/01/2007' and '08/25/2008' And VC.O_Id in ('fb243e92-ee74-4278-a2fe-8395214ed54b') Thanks&Regards,Msrs |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-05-01 : 10:39:53
|
| Are your tables really called tab91, tab92, tab93 etc? And your columns COLUMN001, COLUMN002, COLUMN003 etc?Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-02 : 03:22:53
|
| You have 13 joins! And I seriously hope you table/column names are changed before posting here...but the names doesn't really affect performance.What is your index situation? And how big are these tables? Indexes on CQ.COLUMN005 and VC.O_Id along with the joined columns would help but where does one start on a 13-table select...I guess at the top and work your way down.--Lumbago |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-05-02 : 04:49:59
|
quote:
names doesn't really affect performance
Although it can indicate structural problems, which can hugely affect performance, of course.Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-02 : 05:09:12
|
quote:
Although it can indicate structural problems
And it really does!! I'm very happy I'm not working with a system like this...--Lumbago |
|
|
|
|
|
|
|
|
Query optimization