Update query takes hours, Similarly structured SELECT takes seconds

Transact-SQL
1 
Hi, I cannot figure out why this query takes an hour or more:

UPDATE BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP]
    set Provider_Type = 
        CASE
            when con.providerparstatus is Null then Null
            when con.providerparstatus = 'Y' then 'CP'
            else 'NCP'
        END
FROM 
    BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP]
    inner join dwpresentation_aahpprod.dbo.providers p 
        on BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP].Provider_Type = p.providernbr
    inner join dwpresentation_aahpprod.dbo.contracts con on p.providernbr = con.ProviderNbr

But this code (the same thing basically, just a select), takes about 3 seconds: (and, incidentally, or maybe not incidentally, returns zero results)

select

BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP].*,
p.ProviderNbr,
con.ProviderNbr
from

BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP]
inner join dwpresentation_aahpprod.dbo.providers p
    on BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP].Provider_Type = p.providernbr
inner join dwpresentation_aahpprod.dbo.contracts con on p.providernbr = con.ProviderNbr
2

The explanation would be in the execution plan. My guess? The CASE statement in the update. If providerparstatus is not indexed, you're probably getting a table scan. but that's just a guess

3

To get a fair comparison, you would need to add con.providerparstatus to the SELECT. As written, your select statement never has to access the con table.

Also, make sure SQL "understands" that only one use of the BI table is intended:

UPDATE bi
    set Provider_Type = 
        CASE
            when con.providerparstatus is Null then Null
            when con.providerparstatus = 'Y' then 'CP'
            else 'NCP'
        END
FROM 
    BIAnalytics.[dbo].[CMS_ORG_DETERMINATIONS_CLAIMS_InternalJuly2015_AAHP] bi
    inner join dwpresentation_aahpprod.dbo.providers p 
        on bi.Provider_Type = p.providernbr
    inner join dwpresentation_aahpprod.dbo.contracts con on p.providernbr = con.ProviderNbr
4

OK, I see what you mean about the comparison - thanks everyone.