How do I optimize query where searching for 'is not null' on either of two linked tables

Question

I have a query like this:

SELECT * FROM my_first_table a   -- > 1 million records
INNER JOIN my_second_table b     -- > 1 million records
    on a.id = b.a_id
LEFT OUTER JOIN my_third_table c -- > 1 million records
    on a.id = c.a_id
WHERE   b.my_column_x IS NOT NULL
OR      c.my_column_y IS NOT NULL
AND     b.start_date  > isnull(@startdate, '17530101')
AND     b.end_date    < isnull(@enddate, '99991231') 

my_column_x and my_column_y are both indexed, however as I'm doing an OR IS NOT NULL across both of these columns, neither index is being used, and the query is extremely slow.

I'm also doing a date range check. b.start_date and b.end_date are both datetime columns, and are indexed. The query accepts inputs where both the start and end dates can be null or a specified datetime.

How can I optimize the query? I'm thinking there must be a way I can do some kind of separate sub-selects for IS NOT NULL on my_second_table.my_column_x and my_third_table.my_column_y then combine those sub-selects in order to be able to join in the way I'm trying to do in the original query, so that the indexes on b.my_column_x and b.my_column_y are used.

Answer 1

Biggest mistake is you are using * in Select.

Do you require column from all three table in your resultset ?

Once you should try this,

if(@startdate is null)
set @startdate='17530101'

if(@enddate is null)
set @enddate='99991231'

SELECT * FROM dbo.my_first_table a   -- > 1 million records
INNER JOIN dbo.my_second_table b     -- > 1 million records
    on a.id = b.a_id and b.my_column_x IS NOT NULL
LEFT OUTER JOIN dbo.my_third_table c -- > 1 million records
    on a.id = c.a_id and c.my_column_y IS NOT NULL
WHERE   
 b.[start_date]  > @startdate
AND     b.[end_date]    < @enddate