my query taking too much time.

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

my query taking too much time.

Author

Topic

mmmundhra
Starting Member

4 Posts

Posted - 2009-09-10 : 07:00:27

Hi,

Please look into following query.
Its taking more then 15 min. at client location.

EFFEF100 and EFFEF10B table have more then 300000 records.
All tables have proper index defined in all possible join columns.

-- DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS
Declare @JobCardID varchar(40)

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Set @JobCardID = ''

Select
EFFEF100.ID REFID,
isnull(EFFEF100.Document_series_number,'') as Challan_Number,
EFFEF100.Effective_date As Challan_Date,
EFFEF100.JobCard JobCard_Number,
EFFEF100.Estimate Estimate_Number,
isnull(EFFEF100.ActualArea,0) As ActualArea,
isnull(EFFEF100.ActualLength,0) As ActualLength,
isnull(EFFEF100.ActualWidth,0) As ActualWidth,
isnull(EFFEF100.Amount,0) As Amount,
isnull(Effef100.LengthSize,0) As LengthSize,
isnull(Effef100.LengthSize_Inches,0) As LengthSize_Inches,
isnull(Effef100.PiecesPerPrint,0) As PiecesPerPrint,
isnull(Effef100.SizeDescription,'') as SizeDescription,
isnull(Effef100.TotalSize,0) As TotalSize,
isnull(Effef100.WidthSize,0) As WidthSize,
isnull(Effef100.WidthSIze_Inches,0) As WidthSIze_Inches,
isnull(EFFEF100.Product,'') as Product,
isnull((Select description from CTGAM050 where id=EFFEF100.product),'') as ProductName,
isnull(EFFEF100.ProductDescription,'') as ProductDescription,
isnull(EFFEF100.DisplayLocation,'') as Location,
isnull(EFFEF100.Facing,'') as Facing,
isnull(EFFEF100.Visual_NBS,'') as VisualNBS,
isnull(EFFEF100.MediaUsed,'') as MediaID,
isnull((Select description from CTGAM050 where id=EFFEF100.MediaUsed),'') as MediaUsed,
isnull(EFFEF100.Qty1,0) As Qty1,
isnull(EFFEF100.QTY1-ISNULL((Select SUM(QTY1) FROM EFFEF100 EF100 WHERE EF100.REF_EFFECT_ID=EFFEF100.ID),0),0) As BalanceQty1,
isnull(EFFEF100.Qty2,0) As NoOfPrints,
isnull(EFFEF100.QTY2-ISNULL((Select SUM(QTY2) FROM EFFEF100 EF100 WHERE EF100.REF_EFFECT_ID=EFFEF100.ID),0),0) As BalanceQty2,
Isnull(EFFEF100.TotalLoopQty,0) As TotalLoopQty,
Isnull(EFFEF100.Machine,'') As Machine,
Isnull(Effef100.ratebasis,'') as RateBasis,
Isnull(Effef100.rate,0) As Rate,
Effef100.Base_Srl,
--Isnull(EFFEF100.remark_Row,'') as Remark_Row
convert(varchar(7999),EFFEF100.remark_Row) As Remark_Row

FROM (Select
Challan.ID,
Challan.Document_series_number,
Jc.Document_series_number JobCard,
Estimate.Document_Series_Number Estimate,
Challan.Effective_date,
Challan.ActualArea,
Challan.ActualLength,
Challan.ActualWidth,
Challan.Amount,
Challan.LengthSize,
Challan.LengthSize_Inches,
Challan.PiecesPerPrint,
Challan.SizeDescription,
Challan.TotalSize,
Challan.WidthSize,
Challan.WidthSIze_Inches,
Challan.product,
Challan.ProductDescription,
Challan.DisplayLocation,
Challan.Facing,
Challan.Visual_NBS,
Challan.MediaUsed,
Challan.Qty1,
Challan.Qty2,
Challan.REF_EVENT_ID,
Challan.TotalLoopQty,
Challan.Machine,
Challan.ProductonType,
Challan.ratebasis,
--EFFEF10B.rate,
EstimateRate.Rate,
EFFEF10B.remark_Row,
Challan.Base_Srl,
Challan.ORIGINAL_ID

FROM EFFEF100 Challan

LEFT JOIN (Select BASIC_ID,Rate,PARTY,remark_Row FROM EFFEF10B) EFFEF10B On EFFEF10B.BASIC_ID =Challan.ID
LEFT JOIN (Select REF_EVENT_ID,ref_effect_id,DR_CR FROM EFFEF100)Challan_C On (Challan_C.ref_event_ID=Challan.ref_event_ID And Challan_C.DR_CR='C')

--2.. Fetch the Corresponding Job Card Row
--LEFT OUTER JOIN (Select original_id,Id,dr_cr,Foreign_flag,ref_event_id,Document_series_number FROM EFFEF100)Jc On Jc.Id = challan.original_id And Jc.dr_cr ='D' AND Jc.Foreign_flag=1
LEFT OUTER JOIN (Select original_id,Id,dr_cr,Foreign_flag,ref_event_id,Document_series_number FROM EFFEF100)Jc On (Jc.Id = Challan_C.Ref_Effect_id And Jc.dr_cr ='D' AND Jc.Foreign_flag=1)

--3.. Fetch JC_with_Est row
LEFT OUTER JOIN(Select id,ref_event_id,Foreign_flag,Dr_cr,ref_effect_id FROM EFFEF100)JC_with_Est
On (Jc.ref_event_id = JC_with_Est.ref_event_id And JC_with_Est.Dr_cr ='C' AND JC_with_Est.Foreign_flag is null)

--4.. Fetch Jc_without_Est row
LEFT OUTER JOIN( Select id,ref_event_id,DR_CR,Foreign_flag FROM EFFEF100)JC_without_Est
On (JC.ref_event_id = JC_without_Est.ref_event_id
And JC_without_Est.DR_CR='D' AND JC_without_Est.Foreign_flag IS NULL)

--5.. Fetch Estimate row
Join (Select id,ref_effect_id,ref_event_id,document_series_number FROM EFFEF100) Estimate On (JC_with_Est.ref_effect_id = Estimate.id Or Estimate.ref_effect_id = JC_without_Est.id)

--6.. Fetch Estimate rows Rate
Join (Select Basic_id,Rate FROM EFFEF10B) EstimateRate On (EstimateRate.Basic_id = Estimate.id)

--7. Fetch Job Estimate Event Row
JOIN (Select ID,BASIC_ID FROM EVTST02A)EVTST02A On EVTST02A.ID= Estimate.ref_event_id
JOIN (Select ID FROM EVTST020)EVTST020 On EVTST020.ID=EVTST02A.BASIC_ID

--8.MultiTag_Data_EstimateConsent

JOIN (Select Ref_Id,value FROM MultiTag_Data) MultiTag_Data_EstimateConsent On (EVTST020.ID = MultiTag_Data_EstimateConsent.value)

--9. Fetch EstimateConsent row

JOIN (Select id,Estimate,PO_required FROM EVTST040) EstimateConsent On (MultiTag_Data_EstimateConsent.Ref_Id= EstimateConsent.Estimate)

--10.MultiTag_Data_PO

LEFT JOIN (Select Ref_Id,value FROM MultiTag_Data) MultiTag_Data_PO On (EstimateConsent.id = MultiTag_Data_PO.value)

--11. Fetch PO row
Left Join (Select ID,Estimate_Consent FROM EVTST050) PO On (PO.Estimate_Consent=MultiTag_Data_PO.Ref_Id)

------Courire Despatch/Acknowledgement checking
/* -- Check for "Acknowledge" removed, now all challan data will come. Changes done as per task id <A0525> <SC2> #25.11.2008#
--1. Fetch Courier Required Row
LEFT JOIN (Select ID,Event_screen_id,Doc_number,DR_CR FROM EFFEF070) Courier_Reqd On (Challan.Event_screen_id = Courier_Reqd.Event_screen_id
And Challan.Doc_number = Courier_Reqd.Doc_number And Courier_Reqd.DR_CR='D')
--2. Fetch Courier Despatched Row
LEFT JOIN (Select ID,DR_CR,original_id FROM EFFEF070) Courier_Despatched On (Courier_Despatched.original_id = Courier_Reqd.id
And Courier_Despatched.DR_CR='D')
--3. Fetch CourierAcknowldgement row
LEFT JOIN (Select ID,ref_effect_id,original_id,DR_CR FROM EFFEF070) CourierAcknowldgement On (CourierAcknowldgement.ref_effect_id = Courier_Despatched.id)
*/

WHERE Challan.DR_Cr='D'
And Challan.Effective_date <= dbo.char_to_date('09/09/2009')
And Challan.foreign_flag = 4
And Challan.ProductonType = 'P'
And EFFEF10B.party='2c850103_8679_4ed4_9ecb_6907cad1c34d'
And Estimate.ID Is Not NULL
And EstimateConsent.Estimate Is Not NULL
And (isnull(EstimateConsent.PO_Required,0)=0 Or (EstimateConsent.PO_Required=1 And PO.ID Is Not NULL))
--Changes done as per task id <A0525> <SC2> #25.11.2008#
--And (Courier_Reqd.ID Is NULL Or CourierAcknowldgement.ID Is Not NULL)
And ((@JobCardID='')
Or
(@JobCardID<>''
And @JobCardID In (Select JobCard.Basic_id
FROM EVTST01A JobCard
WHERE JobCard.ID = JC.Ref_event_id)))
)EFFEF100
WHERE
EFFEF100.QTY2-ISNULL((Select SUM(QTY2) FROM EFFEF100 EF100 WHERE EF100.REF_EFFECT_ID=EFFEF100.ID),0)>0
And EFFEF100.QTY1-ISNULL((Select SUM(QTY1) FROM EFFEF100 EF100 WHERE EF100.REF_EFFECT_ID=EFFEF100.ID),0)>=0
--And (Select count(id) from effef100 Challan where Challan.Dr_Cr ='C' and Challan.Ref_effect_id = EFFEF100.ID) > 0

Group by EFFEF100.ID ,
isnull(EFFEF100.Document_series_number,'') ,
EFFEF100.Effective_date,
EFFEF100.JobCard ,
EFFEF100.Estimate ,
isnull(EFFEF100.ActualArea,0) ,
isnull(EFFEF100.ActualLength,0) ,
isnull(EFFEF100.ActualWidth,0) ,
isnull(EFFEF100.Amount,0) ,
isnull(Effef100.LengthSize,0) ,
isnull(Effef100.LengthSize_Inches,0) ,
isnull(Effef100.PiecesPerPrint,0) ,
isnull(Effef100.SizeDescription,'') ,
isnull(Effef100.TotalSize,0) ,
isnull(Effef100.WidthSize,0) ,
isnull(Effef100.WidthSIze_Inches,0) ,
isnull(EFFEF100.Product,'') ,
isnull(EFFEF100.ProductDescription,'') ,
isnull(EFFEF100.DisplayLocation,'') ,
isnull(EFFEF100.Facing,'') ,
isnull(EFFEF100.Visual_NBS,'') ,
isnull(EFFEF100.MediaUsed,'') ,
isnull(EFFEF100.Qty1,0) ,
isnull(EFFEF100.Qty2,0) ,
Isnull(EFFEF100.TotalLoopQty,0) ,
Isnull(EFFEF100.Machine,'') ,
Isnull(Effef100.ratebasis,'') ,
Isnull(Effef100.rate,0) ,
convert(varchar(7999),EFFEF100.remark_Row),
EFFEF100.Product,
EFFEF100.MediaUsed,
EFFEF100.Qty1,
EFFEF100.Qty2,
EFFEF100.Base_Srl

Order By EFFEF100.Base_Srl

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-10 : 08:39:09

"I don't get it...my car can do AT LEAST 200mph, and now that I loaded it up with all my furniture and all my neighbors furniture it only does 10mph. What can possibly be wrong here?? Everything is properly attached and everything..."

- Lumbago

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-10 : 10:10:53

Once I had such a car...
Now I am driving a SMART and no one is expecting that I could load some furniture and it runs fine!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources