Why is my query taking over 4 minutes ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why is my query taking over 4 minutes ??

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2008-07-25 : 11:08:27

I have 3 tables
dbo.MCAT_Recruit_Staging - 12,000 rows
som_online_app.CurrentApplicationYear - 1 row [has current year]
som_online_app.MCAT_Recruiting_Data - 13,000 rows.

This query takes roughly 4:53 minutes to complete:
SELECT RTRIM(A.legal_state_cd)
FROM dbo.MCAT_Recruit_Staging A,
som_online_app.CurrentApplicationYear B
WHERE ( A.aamc_id + '_' + B.curr_app_year ) NOT IN (
SELECT ( C.aamc_id + '_' + CAST(app_year AS NVARCHAR) )
FROM som_online_app.MCAT_Recruiting_Data C )

Any clue as to what is slowing this down??

thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 11:11:03

change it from NOT IN to NOT EXISTS


WHERE NOT EXISTS
(
    SELECT *
    FROM   som_online_app.MCAT_Recruiting_Data C
    WHERE  C.aamc_id = A.aamc_id
    AND    B.curr_app_year = CAST(C.app_year AS NVARCHAR)
)

KH
[spoiler]Time is always against us[/spoiler]

bogey
Posting Yak Master

166 Posts

Posted - 2008-07-25 : 11:37:19

Wow -- Buy why? What was I doing wrong?

thanks

quote:
Originally posted by khtan

change it from NOT IN to NOT EXISTS

WHERE NOT EXISTS
(
SELECT *
FROM som_online_app.MCAT_Recruiting_Data C
WHERE C.aamc_id = A.aamc_id
AND B.curr_app_year = CAST(C.app_year AS NVARCHAR)
)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:49:49

see this

http://www.sql-server-performance.com/tips/t_sql_where_p3.aspx

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-25 : 16:02:38

Here's another approach (I just used a tmp table to keep it looking cleaner, you can do it w/o)



SELECT RTRIM(A.legal_state_cd) as a.Legal_State_CD,A.aamc_id + '_' + B.curr_app_year as Link
into #Tmp
FROM dbo.MCAT_Recruit_Staging A
Cross Join
som_online_app.CurrentApplicationYear B


Select a.Legal_State_CD
from 
#Tmp a
Left Join
som_online_app.MCAT_Recruiting_Data b
on a.Link = b.aamc_id + '_' + CAST(b.app_year AS NVARCHAR) 
where 
b.aamc_ID is null

drop table #Tmp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-25 : 16:19:15

Here is another way withot using NOT EXISTS or NOT IN (which I think produces teh same results):

SELECT 
	RTRIM(A.legal_state_cd)
FROM 
	dbo.MCAT_Recruit_Staging AS A
INNER JOIN
	som_online_app.CurrentApplicationYear AS B
	ON A.aamc_id = B.aamc_id
LEFT OUTER JOIN
	som_online_app.MCAT_Recruiting_Data AS C
	ON C.aamc_id = A.aamc_id
    AND B.curr_app_year = CAST(C.app_year AS NVARCHAR(4))
WHERE
	C.aamc_id IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources