Query Executing Problem

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 
开发者_Go百科            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?

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.

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.