CODEWITHSUNDEEP
sqlsql-serverperformancewhere-clause
user9166017
user9166017

Reputation: 3

SQL Server : is there a better way to write this logic using a where clause to improve performance

I'm still a newbie to writing SQL queries, I wonder if there is a better way to write the where clause in the following query to improve performance in filtering the results?

Query:

SELECT    
    B.col1, A.col1, A.col2   
FROM 
    tblB B  
INNER JOIN 
    tblA A ON B.ID = A.ID   
WHERE   
    ((B.T_No NOT LIKE '123%' AND b.StartDate < '2001-01-01') 
     OR
     (B.T_No NOT LIKE '234%' AND b.StartDate < '2005-01-01')
    )
    AND B.BName = 'sth'
    AND B.c_Val = 33

Note: I should not use primary keys to filter these results.

Can anyone help me?

Upvotes: 0

Views: 88

Answers (2)

Tomer Shay
Tomer Shay

Reputation: 801

I would go for these two changes to optimize this query:

  1. First, I would split the query to two parts, combined with a UNION (preferably a UNION ALL if you don't care about removing duplicates), but also possible with UNION DISTINCT. This split will allow MySQL to better index the WHERE filters, as it can't properly search in the indexes when using an OR condition.
  2. Add proper indexes.

So let's look at the optimized query:

(SELECT 
    B.col1, A.col1, A.col2
FROM
    tblB B
        INNER JOIN
    tblA A ON B.ID = A.ID
WHERE
    ((B.T_No NOT LIKE '123%'
        AND B.StartDate < '2001-01-01'))
        AND B.BName = 'sth'
        AND B.c_Val = 33) UNION DISTINCT (SELECT 
    B.col1, A.col1, A.col2
FROM
    tblB B
        INNER JOIN
    tblA A ON B.ID = A.ID
WHERE
    ((B.T_No NOT LIKE '234%'
        AND B.StartDate < '2005-01-01'))
        AND B.BName = 'sth'
        AND B.c_Val = 33)

Regarding indexes: MySQL has a lot of factors to determine whether to start joining with tblA or tblA. Therefore, it's hard to say with the information you provided (without schema and explain plan) which table will be first. Therefore, you can also try to add these two options, one at a time, and see which performs better: Option A:

ALTER TABLE `tblA` ADD INDEX `tblA_index_1` (`col1`, `col2`);
ALTER TABLE `tblB` ADD INDEX `tblB_index_1` (`BName`, `c_Val`, `ID`, `StartDate`, `col1`);

Option B:

ALTER TABLE `tblA` ADD INDEX `tblA_index_1` (`ID`, `col1`, `col2`);
ALTER TABLE `tblB` ADD INDEX `tblB_index_1` (`BName`, `c_Val`, `StartDate`, `col1`);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is your query:

SELECT B.col1, A.col1, A.col2   
FROM tblB B INNER JOIN
     tblA A
     ON B.ID = A.ID   
WHERE ((B.T_No NOT LIKE '123%' AND b.StartDate < '2001-01-01') OR
       (B.T_No NOT LIKE '234%' AND b.StartDate < '2005-01-01')
      ) AND
      B.BName = 'sth' AND
      B.c_Val = 33;

The where clause will not make a big difference to performance. What does make a difference are indexes. For this, I would suggest tblB(BName, c_Val, StartDate, t_no, id, col). You also want an index on tblA(id) (if that is not already declared unique or the primary key).

This is a covering index for the query, meaning that all the columns used in tblB are in the index. That means that the index can satisfy the query, without referring to the data pages.

Upvotes: 1

Related Questions