Reduce clustered index scans cost in SQL Server

Last Reply 15 days ago By varun.p

Posted 16 days ago

i want to reduce clustered index scan cost which is 35 %.

My object is

[Mahalaxmi].[dbo].[Item_Qty_Wise_PackingValue].[IDX_Packing] [d]

Output list is :

[Mahalaxmi].[dbo].[Item_Qty_Wise_PackingValue].ItemWiseId, [Mahalaxmi].[dbo].[Item_Qty_Wise_PackingValue].Field_SR_No

My Dynamic Query

DECLARE @pivottablecol AS VARCHAR(MAX)
DECLARE @DynamicPIVOT AS VARCHAR(MAX)

SELECT @pivottablecol = 
    COALESCE(@pivottablecol + ', ', '') + QUOTENAME(field_name)
    FROM (SELECT distinct  c.field_name,convert(int,c.SR_No) as sr_no from ItemWise_PackingDetails a,Parameter_Packing_Type b,Parameter_Packing_Type_Field_Mast c, Item_Qty_Wise_PackingValue d
where a.Packing_URN_NO=b.urn_no and b.urn_no=c.URN_No and a.Id=d.ItemWiseId and c.SR_No=d.Field_SR_No
and  a.co_code='GEN00A000001'  --and a.location_code=@locationcode and a.Doc_Date>=@fromdate and a.Doc_Date<=@todate --and a.fyear=@fyear
-- and a.it_code=@it_code
and (a.Packing_URN_NO='P0200A000012' )

) as  packing 
order by SR_No

print(@pivottablecol) 


SELECT @DynamicPIVOT = 'SELECT Urn_no,Doc_Date,IT_NAME,serialno,perpackqty,Type,Name''Location'',' + @pivottablecol +
' FROM (
	select a.urn_no,convert(nvarchar(10),a.Doc_Date,103) as Doc_Date,e.IT_NAME,a.serialno,a.perpackqty,a.Type,f.Name,d.fieldvalue,c.field_name 
	from ItemWise_PackingDetails a,Parameter_Packing_Type b,Parameter_Packing_Type_Field_Mast c, Item_Qty_Wise_PackingValue d,ItemMaster e,Parameter_Location f
where a.Packing_URN_NO=b.urn_no and b.urn_no=c.URN_No and a.Id=d.ItemWiseId and c.SR_No=d.Field_SR_No and a.IT_CODE=e.IT_CODE and f.urn_no=a.Location_code
and a.co_code='''+'GEN00A000001'  
+'''  and a.Packing_URN_NO='''+'P0200A000012'+''' 
and a.type='''+'in'+'''   
and a.serialno not in  (select SerialNo from ItemWise_PackingDetails Where Packing_URN_NO='''+'P0200A000012'+'''  and Type='''+'out'+''')
and a.Doc_Date<=''' + convert(nvarchar(10),@todate,101) + ''' 

) packing
PIVOT (
    max(fieldvalue) FOR field_name IN (' + @pivottablecol + ')
) Result;'
print(@DynamicPIVOT)
EXEC (@DynamicPIVOT)
You are viewing reply posted by: varun.p 15 days ago.
Posted 15 days ago

I have solved this.

not use "in" clause. you must be use not exists query. i got 40,000 rows in just 9 seconds only. before it take time 3 minutes.

SELECT @DynamicPIVOT = 'SELECT Urn_no,Doc_Date,IT_NAME,serialno,perpackqty,Type,Name''Location'',' + @pivottablecol +
' FROM (
    select a.urn_no,convert(nvarchar(10),a.Doc_Date,103) as Doc_Date,e.IT_NAME,a.serialno,a.perpackqty,a.Type,f.Name,d.fieldvalue,c.field_name
    from ItemWise_PackingDetails a,Parameter_Packing_Type b,Parameter_Packing_Type_Field_Mast c, Item_Qty_Wise_PackingValue d,ItemMaster e,Parameter_Location f
    where a.Packing_URN_NO=b.urn_no and b.urn_no=c.URN_No and a.Id=d.ItemWiseId and c.SR_No=d.Field_SR_No and a.IT_CODE=e.IT_CODE and f.urn_no=a.Location_code
    and a.co_code='''+'GEN00A000001'
    +'''  and a.Packing_URN_NO='''+'P0200A000012'+'''
    and a.type='''+'in'+'''  
    and  not exists (select SerialNo from ItemWise_PackingDetails Where Packing_URN_NO='''+'P0200A000012'+'''  and Type='''+'out'+''' and serialno = a.serialno)
    and a.Doc_Date<=''' + convert(nvarchar(10),@todate,101) + '''
) packing
PIVOT (
    max(fieldvalue) FOR field_name IN (' + @pivottablecol + ')
) Result;'