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?

Looks to be a missing from clause for the initial select statement posted? — Andrew
– Andrew, Commented Oct 13, 2009 at 11:06

Guffa · Accepted Answer · 2009-10-14 19:19:56Z

2

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.

edited Oct 14, 2009 at 19:19

answered Oct 13, 2009 at 11:28

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gopal Over a year ago

My table name is tmp_cardevent3, Before you can put from tmp_cardevent3. I executed, but am getting error. Please can you help me

Guffa Over a year ago

No wonder that you get an error if you try to put a table name in the field list... I put it in the correct position in the query above.

tvanfosson · Accepted Answer · 2009-10-13 11:19:02Z

0

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.

answered Oct 13, 2009 at 11:19

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Collectives™ on Stack Overflow

Query Executing Problem

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related