Dear Friends
in my sql query without where clause performance is perfect. but whenever
i increase my filter criteria sql server take very much time to give result .
how can i get fast result
my query is
SELECT BagNo, OrderPcs, PendingPCs, OrderCrts, MemoPendingPcs, MemoPendingCrts, a.Quot_Order_No, PartyName, KarigerName, WorkOrderPendingPcs,
WorkOrderPendingCrts, Estimated_Receipt_date, Quot_Order_Date, Order_Manager, SHAPE_NAME, SIZE_NAME, COLOR_NAME, CLARITY_NAME,
STONE_NAME, SIZE_ORDERNO, ISNULL(B.Packet_Cts, 0) AS Packet_Cts, ISNULL(CompPercent, 0) AS CompPercent
FROM JWL_SMALL_STONE_V2 AS A LEFT OUTER JOIN
JWL_QUOT_ORDER_PENDING_Group AS D ON a.Quot_Order_No = D.Quot_Order_No LEFT OUTER JOIN
(SELECT SUM(ISNULL(Packet_Cts, 0)) AS Packet_Cts, Stone_id, Shape_Id, Size_Id, Clarity_Id, Color_Id
FROM (SELECT ISNULL(Packet_Cts, 0) AS Packet_Cts, Stone_id, Shape_Id, Size_Id, Clarity_Id, Color_Id
FROM JWL_SMALL_STONE_ACTIVITY_MST_V
UNION ALL
SELECT ISNULL(carats, 0) AS Packet_Cts, Stone_id, Shape_Id, Size_Id, Clarity_Id, Color_Id
FROM JWL_LARGE_STONE_INVENTORY_T) AS a1
GROUP BY Stone_id, Shape_Id, Size_Id, Clarity_Id, Color_Id) AS B ON a.Stone_Id = B.Stone_id AND a.Shape_Id = B.Shape_Id AND
a.Size_Id = B.Size_Id AND a.Color_Id = B.Color_Id AND a.Clarity_Id = B.Clarity_Id
see this is where criteria
WHERE (1 = 1)
AND UPPER(ISNULL(Stone_NAME,' ')) IN ( 'DIA') ---'EM')--, 'RB', , 'PEARL', 'CLST', 'BRL.'
AND UPPER(ISNULL(Size_NAME,' ')) IN ( '1/3') --, '1/9', '1/4', '1/5'