Fastest way to determine existence of record w given PK

[Bob]

I'm trying to determine whether a given record already exists in a
database table. Each record is rather short, about 10 fields, approx
250 chars or so, but there are quite a few of them. I'm updating the
table by going through a long list of new records, pretesting to see
if the older record exists (possibly clumsily), and noticing that the
test itself seems to be chewing up CPU.

It occurred to me that I might preload the old, existing records, and
build a hashtable of their PKs in C#-space. Then test against the
hashtable rather than going to the DB. Is this likely to speed things
up? Is there a better way?

 

[William Vaughn \(MVP\)]

In SQL Server 2008 you can use the new "upsert" Merge statement that does
this automatically. In pre- I would verify that you have a suitable index
created for the Primary Key (PK) on the row. SS2K8, I would also perform the
operation in a SP that takes the PK and the new row values and in a single
operation tests for the existing value and adds it if it's not there or
updates it if it is.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________