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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-24 : 08:20:48
|
| Michael writes "Hi,Hope this hasn't been answered before -- I searched around the site and the net altogether but couldn't really manage to find an answer I felt confident in. I'm running SQL Server 2000 SP3 on Windows Server 2003.My problem is pretty simple really, I have a stats table that holds various values on a per-"service" and per-day basis, so that a row exists in the table for unique values of service/day, like this:row 1: service=1, day=2004-2-20, valA = 1, valB = 5row 2: service=2, day=2004-2-20, valA = 6, valB = 2When I want to set the value of valA or valB, I need to check if the row exists for that service and today's date. If it does, I just do an update, if it doesn't I do an insert.This ought to be pretty straightforward:if exists (select * from stats where service=1 and day='2004-2-20') update ...else insert ...But won't I run into concurrency issues if two threads run the exists clause at the same time? To try and avoid this I'm currently trying to select the PK of the stats table, holding a table lock until I've done the insert/update, but that sounds like overkill for what I'm trying to do, and can't help performance. What's the best approach in a situation like this?thanks for your help!" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-24 : 08:43:38
|
| Two obvious methods.Pre populate the table in a batch job if you know the possible records - then just to an update.Add an identity and always do inserts - get the latest value when you query.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mob
Starting Member
2 Posts |
Posted - 2004-02-24 : 09:57:11
|
| As it turns out I'm not sure if I can use either of those:There are around 50,000 "services" and preloading the table beforehand would probably be too costly since only 50-100 rows would be used.Inserting a row every time (if I've understood you correctly) doesn't really seem appropriate -- this is a summary table that I'm using because I don't want to count individual rows in the source table (i.e. one row in the Stats table represents a few thousand in the source tbl), so an insert each time would mean the stats query might as well reference the source table, not the stats one (if that's clear!).I also thought about defining the service/date thing as a PK and catching and always inserting, but wasn't sure how to prevent the pk violation error from throwing an exception in my application code... Is there some way to do this (I'm using c#)?thanks again! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-24 : 10:49:29
|
| Well you only need to lock for the insert not the update so create a resource tablecreate table stats_insert (i int)goinsert stats_insert select 1Now the update becomesif exists (select * from stats where ... update stats ...else-- insert rowbegin begin tran update stats_insert set i = 1 (tablockx, holdlock) if exists (select * from stats where ... begin update stats end else begin insert stats ... end commit tranendThis will only hold up inserts and not affect updates.Play around with the locking on the resource table if you are worried about one insert blocking another.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mob
Starting Member
2 Posts |
Posted - 2004-02-24 : 10:54:32
|
| That looks great, I'll give it a try!thanks for your help |
|
|
|
|
|
|
|
|
Preventing duplicate inserts without table locks?