SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select Statement with NOT IN statment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 06/20/2013 :  11:15:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 06/20/2013 :  11:42:04  Show Profile  Reply with Quote
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.
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 06/20/2013 :  12:35:26  Show Profile  Reply with Quote
Thanks, I ended up using a left join and a IS NULL statment that reduced it by 200%

Bryan Holmstrom
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000