Select Statement with NOT IN statment

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 2012 Forums

Transact-SQL (2012)

Select Statement with NOT IN statment

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 11:15:49

I have a query that I am running on a table with 1.4 million qualified rows. The query compares a value in one table to the non-existance of that field in the other table. This query is painfully slow. ANyone have any ideas.

Here is the code:

INSERT INTO [DataPump_Staging].[dbo].[Elite_Matters]
Select [wa_mattvalue].[yr] as Bill_Year,
[wa_mattvalue].[mo] as Bill_Month,
[wa_mattvalue].[period] as Bill_Period,
[wa_mattvalue].[clsort] as Sort,
[wa_mattvalue].[mmatter] as Matter,
[wa_mattvalue].[mdesc1] as matter_name,
[wa_mattvalue].[clnum] as Client,
[wa_mattvalue].[crelated] as master_client,
([wa_mattvalue].[fee_bills_ytd] +
[wa_mattvalue].[hcost_bills_ytd] +
[wa_mattvalue].[scost_bills_ytd] +
[wa_imattvalue].[oaf_bills_ytd] +
[wa_imattvalue].[oac_bills_ytd] +
[wa_imattvalue].[oao_bills_ytd]) as Billed_YTD

from [HOSTBIDB].[son_db_bi].[dbo].[wa_mattvalue]

Join [HOSTBIDB].[son_db_bi].[dbo].[wa_imattvalue] ON
([wa_imattvalue].[mmatter] = [wa_mattvalue].[mmatter])
AND ([wa_imattvalue].[period] = [wa_mattvalue].[period])

where [wa_mattvalue].[yr] >= 2010
AND [wa_mattvalue].[mmatter] NOT IN
(select cpdc_matternum
collate latin1_general_ci_as
from [Hostcrm1].[ReznickGroup_MSCRM].dbo.[CPDC_matter])

order by [wa_mattvalue].[yr],[wa_mattvalue].[period],[wa_mattvalue].[mmatter]

Thanks in advance

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 11:42:04

First look at the query plan to see what is taking up the time and resources. If you have indexes on mmatter, period that would help. Also, if you have index on cpdc_matternum, you might consider changing the NOT IN clause to a NOT EXISTS clause like this:

....
AND NOT EXISTS 
(
	SELECT * FROM [Hostcrm1].[ReznickGroup_MSCRM].dbo.[CPDC_matter]
	WHERE cpdc_matternum COLLATE latin1_general_ci_as = [wa_mattvalue].[mmatter]
)

But to be honest, these are just educated guesses on my part. You really need to look at the execution plan to see what is slowing it down.

Also, you might want to make sure that the statistics are upto date and that the indexes if any that are being used are not fragmented.

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 12:35:26

Thanks, I ended up using a left join and a IS NULL statment that reduced it by 200%

Bryan Holmstrom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources