Performance tuning select query with OR condition in JOIN

Question

I have large SELECT query to find out the product related details.

Some part of the query is taking too much time due to OR condition in the JOIN.

Sample code:

Existing query:

--Time Taken: 00:00:04
--Records   : 179384
SELECT * FROM ProductMaster pm
WHERE (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType'));

As we can see here the condition (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType')) is using OR, due which I'm getting 1min of time to execute main query for 4500 records.

If I modify it with the following it execute within second but no result displayed.

--Time Taken: 00:00:01
--Records   : 0
SELECT * FROM ProductMaster pm
WHERE pm.prdname IN (NULL,'EV') AND pm.prdtype = 'EType';

I have also tried with this approach but it took 00:01:45 time and also records are not matching with the existing query.

--Time Taken: 00:01:45
--Records   : 4,525,052
SELECT * FROM ProductMaster pm
WHERE (pm.prdname IN (NULL,'EV') OR pm.prdtype = 'EType');

Note: The JOIN in main SELECT statement looks like this:

left outer join ProductMaster pm on Pcode=pm.Pcode and SaleDate=pm.SaleDate and prdscode=pm.prdscode
AND (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType'))

Answer 1

I would try something like this, removing the filtering expression from the ON clause.

left outer join
(
 select * from ProductMaster
 where prdname is null or (prdname = 'EV' and prdtype = 'EType')
) pm
using (Pcode, SaleDate, prdscode);

or

left outer join ProductMaster pm using (Pcode, SaleDate, prdscode)
where pm.prdname is null or (pm.prdname = 'EV' and pm.prdtype = 'EType')

BTW your first example prdname is null or (prdname = 'EV' AND prdtype = 'EType') is not equivalent to the second and third ones.