Query Executing Problem

Question

Using SQL 2005: “Taking too much time to execute”

I want to filter the date, the date should not display in holidays, and I am using three tables with Inner Join

When I run the below query, It taking too much time to execute, because I filter the cardeventdate with three table.

Query

SELECT 
   PERSONID, CardEventDate tmp_cardevent3 
WHERE (CardEventDate NOT IN 
           (SELECT T_CARDEVENT.CARDEVENTDATE 
            FROM T_PERSON 
            INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID 
            INNER JOIN DUAL_PRO_II_TAS.dbo.T_WORKINOUTTIME ON T_CARDEVENT.CARDEVENTDAY = DUAL_PRO_II_TAS.dbo.T_WORKINOUTTIME.DAYCODE 
                   AND T_PERSON.TACODE = DUAL_PRO_II_TAS.dbo.T_WORKINOUTTIME.TACODE
            WHERE (DUAL_PRO_II_TAS.dbo.T_WORKINOUTTIME.HOLIDAY = 'true')
           )
      ) 
ORDER BY PERSONID, CardEventDate DESC

For the above mentioned Query, there is any other way to do date filter.

Expecting alternative queries for my query?

Answer 1

I'm pretty sure that it's not the joined tables that is the problem, but rather the "not in" that makes it slow.

Try to use a join instead:

select m.PERSONID, m.CardEventDate
from T_PERSON p
inner join T_CARDEVENT c on p.PERSONID = c.PERSONID
inner join DUAL_PRO_II_TAS.dbo.T_WORKINOUTTIME w
   on c.CARDEVENTDAY = w.DAYCODE 
   and p.TACODE = w.TACODE
   and w.HOLIDAY = 'true'
right join tmp_cardevent3 m on m.CardEventDate = c.CardEventDate
where c.CardEventDate is null
order by m.PERSONID, m.CardEventDate desc

(There is a from clause missing from your query, so I don't know what table you are trying to get the data from.)

Edit:
Put tmp_cardevent3 in the correct place.

Answer 2

Have you created indices on all of the columns that you are using to do the joins? In particular, I'd consider indices on PERSONID in T_CARDEVENT, TACODE in both T_PERSON and T_WORKINOUTTIME, and HOLIDAY in T_WORKINOUTTIME.