SQL Server Performance Tuning notes (iv)

When the system is finished, performance problems are often an important issue in warranty and later maintenance.

These days, the customer has to reflect that there is a very slow query. This query uses a primary key query, because the primary key is a clustered index and is fragmented. should be very fast. But the phenomenon of seeing is very slow (10 seconds or so, the worst is more than 18 seconds). Eliminates the problem of hardware, resource locking, etc., and does not have to reach the tunning level at the database end. The basic judgments are related to SQL, and you want to look at the execution plan for the SQL text.

First, find the SQL.

SELECT A.awb_no,
A.bwb_no,
A.cwb_no,
A.origin,
A.dest,
A.MODIFY_ON,
A.CREATED_ON,
A.consignor_customer_code,
A.consignor_code,
A.consignor_name,
A.consignee_name,
A.cwb_status,
A.cwb_type,
ISNULL (a.pcs, 0) as PCS,
A.bwblist,
B.pweight
From TB_CWB as a
Left JOIN Tb_cwbweight as B
On a.cwb_no = B.cwb_no
and b.available = ' Y '
WHERE a.available = ' Y '
and (A.cwb_no = @CWB_NO OR
((@CWB_NO is NULL)
and (A.awb_no = @AWB_NO OR @AWB_NO is NULL)
and (A.bwb_no = @BWB_NO OR @BWB_NO is NULL)
and (A.ie_type = @IE_TYPE OR @IE_TYPE is NULL)
and (a.created_on >= @DateFrom OR @DateFrom is NULL)
and (a.created_on <= @DateTo OR @DateTo is NULL)
and (a.payment = @PAYMENT OR @PAYMENT is NULL)
and (A.origin = @ORIGIN OR @ORIGIN is NULL)
and (a.dest = @DEST OR @DEST is NULL)
and (A.consignor_customer_code = @CONSIGNOR_CUSTOMER_CODE OR @CONSIGNOR_CUSTOMER_CODE is NULL)
and (A.consignor_name like '% ' + @CONSIGNOR_NAME + '% ' OR @CONSIGNOR_NAME is NULL)
and (A.consignee_name like '% ' + @CONSIGNEE_NAME + '% ' OR @CONSIGNEE_NAME is NULL)
and (A.cwb_type = @CWB_TYPE OR @CWB_TYPE is NULL))