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)
