UPDATE if record exists, INSERT if it doesnt

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

SQL Server Development (2000)

UPDATE if record exists, INSERT if it doesnt

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-25 : 09:03:38

Nathan writes "Nathan writes "I've got some code that runs 20 times per page on a website search and I'd like to put it into a stored procedure to optimize performance. Right now it hits the db once to see if a record exists, then if it does exist it updates it, otherwise it adds a new record. To not get into domain-specific details, let's just say it's a page tracker, and it checks


SELECT RecordID from pageCounter WHERE pagename = @pagename

and if that query returns something, it calls an UPDATE statement to increment the hit count, otherwise it calls an INSERT statement with the hit count as 1.

Is there a way to put this logic into a single stored procedure? I've considered just inserting a hit count of 1, and doing the math with count() and sum() when I'm pulling the reports, but I'd rather not do this as it would mean rewriting more code.""

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-25 : 09:25:05

update pageCounter set pageCount = pageCount + 1 where pagename = @pagename
if @@rowcount = 0 and @@error = 0
begin
insert pageCounter select @pagename, 1
end

the only problem now is what if two spids try to do this together - no problem if the page exists as they will both do updates. If it doesn't then they could both do an insert.
If you don't have many inserts (i.e. mostly updates) then I would have a resource table

update pageCounter set pageCount = pageCount + 1 where pagename = @pagename
if @@rowcount = 0 and @@error = 0
begin
begin tran
select * from resource (tablockx, holdlock)
update pageCounter set pageCount = pageCount + 1 where pagename = @pagename
if @@rowcount = 0 and @@error = 0
insert pageCounter select @pagename, 1
end
commit tran
end

The second update is in case the other spid gets to do the insert first.
The resource table means that the table is not locked to other spid updates and has a very short transaction.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nathan
Starting Member

1 Post

Posted - 2002-02-26 : 14:19:40

Thanks for the fast response! Wouldn't it work to just do the transaction every time instead of coding the update and the check twice? I suppose that could be detrimental to performance since most hits are going to be updates.

Could I do the same thing for a Select/Insert?

Edited by - nathan on 02/26/2002 14:27:07

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-26 : 17:53:30

nathan,

NR's code is optimized for speed. Within his transaction, he is using a dummy table called resource. He placed "tablockx" and "holdlock" hints on it. TablockX means place exclusive table lock on the resource, meaning that users have to queue up one at a time. Holdlock means keep this lock for the duration of the transaction. Basically, only one user at a time can execute the transaction, while multiple users can perform the update.

You can do the same thing for select/inserts. Keep in mind though, you trade performance/concurrency for data integrity. Tablockx with holdlock is essentially the same as SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, making your application into a single user application.

HTH

Edited by - lou on 02/26/2002 19:15:31

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources