Performance reduce( sql server take very much time) when increase filter criteria

Posted by Ermahesh2009 under Sql Server on 7/13/2011 | Points: 10 | Views : 1345 | Status : [Member] | Replies : 2
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'




Responses

Posted by: PandianS on: 7/13/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Try to find/follow the following analysis based on your query ...

1. Try to Seperate the "Derived Table" Query using CTE and then JOIN or LEFT JOIN further with others.
2. Direct TRUE condition should not be used in WHERE clause. i.e: (1 = 1), This expression always return TRUE and you trying to combine with additional crireria....
3. Functions should not be used in WHERE clause (Are you using any Column level COLLATION on "Stone_NAME", "Size_NAME" columns ?, If NOT then, Remove the UPPER function)
4. Try to use ("Read Uncommitted" isolation on Table level) using NOLOCK hint
5. You really needed "OUTER JOIN" ? (If NOT then, you can use "JOIN")
6. Is there any Large data type columns involved in this query ? (If so, Kindly check, The columns is really needed in this query ?)
7. Normally, Performance should be fine when you increase the condition in WHERE clause (So, you will have low I/O)
8. Try to validate, Any Indexes required here for the Tables participated in this query.... ? (Missing Index)
9. Identity the Fragmentation Level & Index Statistics and Try to Update that.
10. Try to put ALAIS name for the tables and Don''t forget to use with column name...

I hope, You would get little bit idea... :)


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
HI,

1) USE NORMALIZATION CONCEPT.
2) USE INDEX IN YOUR TABLE

REGARDS


sriram

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response