Update statement taking too long (7 minutes)

Question

This is SQL code im running on SSMS 2008 R2. It's taking over 10 minutes to run (it runs against 90,000 records)..

I'm trying to update all unique records in #tmp_hic_final where [Claim Adjustment Type Code] is 0 and [Claim Type Code] is not 10. I'm also doing the update based on the select subquery which checks to make sure there isn't another record that has a [Claim Adjustment Type Code] of 1 in the table.

Although I don't know much on analyzing it, here's the execution plan: http://snag.gy/TLRsZ.jpg

is there a better way to optimize it?

update  PAHT
set [Marked Final] = 'Y'
from #tmp_hic_final PAHT
join
(
    select [HIC #],
                [Claim Type Code] , 
                [Provider Oscar #], 
                [Claim From Date] ,
                 [Claim Thru Date]
    from #tmp_hic_final
    where [Claim Adjustment Type Code] = 0
    and [Claim Type Code] <> 10
    group by [HIC #],
                [Claim Type Code] , 
                [Provider Oscar #], 
                [Claim From Date] ,
                [Claim Thru Date] 
                --,[Claim Adjustment Type Code]
    having count(*) = 1
) as PAHT_2
on PAHT.[HIC #] = PAHT_2.[HIC #] and
                                        PAHT.[Claim Type Code] = PAHT_2.[Claim Type Code] and 
                                        PAHT.[Provider Oscar #] = PAHT_2.[Provider Oscar #] and
                                        PAHT.[Claim From Date] = PAHT_2.[Claim From Date]  and
                                        PAHT.[Claim Thru Date] = PAHT_2.[Claim Thru Date] 

where PAHT.[Claim Adjustment Type Code] = 0
and PAHT.[Claim Type Code] <> 10
and NOT EXISTS (select 
                    [Claim Adjustment Type Code] 
                from [ACO].[dbo].[PA_Header_Temp]
                where 
                    [HIC #] = PAHT.[HIC #]
                    and [Provider Oscar #] = PAHT.[Provider Oscar #]
                    and [Claim Type Code] = PAHT.[Claim Type Code]
                    and [Claim From Date] = PAHT.[Claim From Date]
                    and [Claim Thru Date] = PAHT.[Claim Thru Date]
                    and [Claim Adjustment Type Code]  = 1)

Table definition and index on PA_Header_Temp:

/****** Object:  Table [dbo].['PA_Header']    Script Date: 06/02/2015 2:32:33 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[PA_Header_Temp](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Current ClaimID] [float] NULL,
    [Provider OSCAR #] [nvarchar](255) NULL,
    [HIC #] [nvarchar](255) NULL,
    [Claim Type Code] [float] NULL,
    [Claim From Date] [datetime] NULL,
    [Claim Thru Date] [datetime] NULL,
    [Claim Bill Facility Type Code] [float] NULL,
    [Claim Bill Classification Code] [float] NULL,
    [Principal Diagnosis Code] [nvarchar](255) NULL,
    [Admitting Diagnosis Code] [nvarchar](255) NULL,
    [Claim Medicare Non Payment Reason Code] [nvarchar](255) NULL,
    [Claim Payment Amount] [float] NULL,
    [Claim NCH Primary Payer Code] [nvarchar](255) NULL,
    [FIPS state Code] [float] NULL,
    [Bene Patient Status Code] [float] NULL,
    [Diagnosis Related Group Code] [float] NULL,
    [Claim Outpatient Service Type Code] [nvarchar](255) NULL,
    [Facility Provider NPI #] [float] NULL,
    [Operating Provider NPI #] [nvarchar](255) NULL,
    [Attending provider NPI #] [float] NULL,
    [Other Provider NPI #] [nvarchar](255) NULL,
    [Claim Adjustment Type Code] [float] NULL,
    [Claim Effective Date] [datetime] NULL,
    [Claim IDR Load Date] [datetime] NULL,
    [Bene Equitable BIC HICN #] [nvarchar](255) NULL,
    [Claim Admission Type Code] [nvarchar](255) NULL,
    [Claim Admission Source Code] [nvarchar](255) NULL,
    [Claim Bill Frequency Code] [nvarchar](255) NULL,
    [Claim Query Code] [float] NULL,
    [Marked Final] [nvarchar](255) NULL,
    [Load Date] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Answer 1

I suggest this approach from both a readability and performance standpoint.

update yourTable
set field2 = someValue
where whatever
and field1 in 
(select field1
from yourTable
where whatever
except
select field1
from yourTable
where whatever
and somethingElse)

where whatever should be the same every time.

Answer 2

So, I took ther advice of @Dan Bracuk and converted my code using his format and it knocked down my execution time from 6:57 to 2:30 (if my math is right thats a 65% increase in performance)

update PAHT  
set PAHT.[Marked Final] = 'Y'
from #tmp_hic_final PAHT
where [Claim Adjustment Type Code] = 0
    and [Claim Type Code] <> 10
and [Claim Adjustment Type Code] in 
(select 
                    [Claim Adjustment Type Code] 
                from [ACO].[dbo].[PA_Header_Temp]
                where 
                    [HIC #] = PAHT.[HIC #]
                    and [Provider Oscar #] = PAHT.[Provider Oscar #]
                    and [Claim Type Code] = PAHT.[Claim Type Code]
                    and [Claim From Date] = PAHT.[Claim From Date]
                    and [Claim Thru Date] = PAHT.[Claim Thru Date]
except
select 
                    [Claim Adjustment Type Code] 
                from [ACO].[dbo].[PA_Header_Temp]
                where 
                    [HIC #] = PAHT.[HIC #]
                    and [Provider Oscar #] = PAHT.[Provider Oscar #]
                    and [Claim Type Code] = PAHT.[Claim Type Code]
                    and [Claim From Date] = PAHT.[Claim From Date]
                    and [Claim Thru Date] = PAHT.[Claim Thru Date]
                    and [Claim Adjustment Type Code]  = 1)

Answer 3

First thing I would check is indexes in the tables (normal and temp.). Especially the clustered index scan of PA_Header_Temp in the nested loop looks really bad. Depending on the columns and data (data types, selectivity, row count) you should probably create index with either some or all of the columns, either as normal or included fields.

It might be a good idea to create clustered indexes for the temp. tables too, probably on the columns used for joining and for #tmp_hic_final you should also consider the fields used as where criteria in the update.

Edit: Have you tried populating PAHT_2 into a separate temp. table before running the update (+ indexing it) -- that might help too.