SQL Server Performance Forum – Threads Archive
multiple updates combined ?
I would like to know is there a way I can do multiple updates to different rows of a table with one single execution of storeprocedure providing dynamic data each time from a VC++ application which uses exec . UPDATE LoginSET CurrentAmt=CurrentAmt + @mAmtInHand ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt
WHERE PlayerId=1 UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand2 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt2
WHERE PlayerId=2
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHan3 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt3
WHERE PlayerId=3 There are more than 1000 such rows which are needed to be updated. VC++ application calls the SP that has the query mentioned above and the sp is called by VC++ for each playerID each time . I would like to know if there is a way which would allow the VC++ application to call SP just once with all the payers and their amount and it updates all the row of each player with its amount in one go using one query or command .
You can use a CASE statement to make it one UPDATE statement. Lookup CASE in Books Online. Give it a try. We can then help you. Also, if you want extensive help, please post DDL and DML statements so we have a sample table and data to work with. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
If I am not wrong , doesnt the case works similar to if condition . In our case there are thoudsands of user with ther unique row Table name Login with following colums:-
PlayerId , CurrentAmt, Avail_CurrentAmt Aater each round of the game each paleyer’s currentAmt and Avail_CurrentAmt colum is update in other words all rows of all the players who were playing the game are updated with their unique amount which they have lost or won in the game . so the update data is unique for each player=row and all that needs to be update by sp . Currently we have the sp with the following =
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt
WHERE [email protected] which is called by the application for each player by providing the payerid , and amount to be updated . the main reason we want to do this is to reduced the number of queries hit by application as I read many acticles that mentioned such updates should be done by one query to increase preformance and stop sql to use too many resources I hope its right please let me know about this as well ?
If there’s a max amount of users who can play the game, create a temp table with UNION ALL clauses of each players values. You can then run one update statement. How many players are in each game? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
each games has 9 players but there are 1000 concurrent games , the each game updates each user’s money status after every round of game when winners and loosers are declared .
what kind of temp table should I make and how do i update the actual real table on one go ? could u please give some examples for it ?
Do you need something like this?
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=11824 Madhivanan Failing to plan is Planning to fail
our prob is not similar to what you ve mentioned. What we need is to update multiple rows independently of a single table with unique data each for each row. We want some kind of method so that stored procedure can take variable number of arguments to update all the rows with single update (all the update queries bundled into one and sent to Stored procedure) Here is the little picture of what we are doing To update a single row we are calling a Stored procedure with some data to update in the row .So to update 9 rows we have to call the stored procedure 9 times
how can we put all the 9(variable) updates data in one call of stored procedure
From where do you want to call the procedure? Anyway, can you not just issue those UPDATE statements as a single T-SQL batch, or even go one-by-one? This doesn’t strike me as an actual problem, or am I missing something?
Adriaan the SP will be called by the application server using exec command , we already call a SP for each update again and again with single row data . we want a way that the sp can update multiple rows with the unique data for each row with one sp call or if possible with single update query designed in SP .
Is your problem perhaps that you need to make sure that all updates are executed together, whichever number of rows get updated? You also mention "unique data for each row", perhaps meaning that you want to add those unique values without having to worry about a parallel process claiming the same values. I would start looking at using a transaction in the procedure – this should take care of both requirements.
already using begin transaction and there is no issue of making sure of all updates executed together . basicly there the application executes SP for each row it wants to update , the data is unique so there is no issue of the paraller process . what we want is to make a sq which would update all 100 records to its respective rows which would be 100 rows for 100 records, one row each for each record data . so we need help to know how to update 100 unique records to 100 rows by single query using SP so that our application server does not have to call the SP 100 times to update each row which are 100 total. We want to reduce the number of exectutions of SP from our application to recude network traffic and also want to reduce the number of queries at server end . As per my knowledge I would write a SP with 100 update mentioned for example:-
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand1 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt1
WHERE PlayerId=1
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand2 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt2
WHERE PlayerId=2
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand3 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt3
WHERE PlayerId=3 ……. so on to 100
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand100 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt100
WHERE PlayerId=100 but what I want is some thing which only uses the " UPDATE Login " and update all the rows data which will be given our application server who will send all the data to sp while calling it . Since the DB is not online or live as we are developing it as a project for some company so we wold also want the information how to tune sql server at our client end when it gives live with them .
Have you tried using the combination Update / From? Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
Here’s a demonstration:
Create table mupdate (
uname varchar(30) primary key,
points int )
insert into mupdate
select ‘foo’ as uname, 0 as points
union all
select ‘bar’ as uname, 0 as points
union all
select ‘alf’ as uname, 0 as points
union all
select ‘tigger’ as uname, 0 as points select * from mupdate update mupdate
set mupdate.points = inp.points
from mupdate
inner join (
select ‘bar’ as uname, 10 as points
union all
select ‘tigger’ as uname, 15 as points) inp
on mupdate.uname = inp.uname select * from mupdate drop table mupdate
Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
Try with case statements once.. Case when Playerid=1 then
UPDATE Login
SET CurrentAmt=CurrentAmt + @mAmtInHand1 ,
Avail_CurrentAmt = Avail_CurrentAmt + @mmamt1
WHERE PlayerId=1
SURYA
Surya:
Have you tested your script? I’m not sure this syntax will compile without error, but even if it does it’s not what was asked: doing 100 updates in one go. DBVictim:
Your client app needs to upload the parameters into a staging table on SQL Server: CREATE TABLE dbo.Staging (PlayerId INT PRIMARY KEY, AmtInHand FLOAT, AvailAmt FLOAT) If there can be multiple processes doing the same thing in the same staging table at the same time, then you have to add a column to distinguish between the processes. UPDATE dbo.Login
SET dbo.Login.CurrentAmt = dbo.Login.CurrentAmt + dbo.Staging.AmtInHand,
dbo.Login.Avail_CurrentAmt = dbo.Login.Avail_CurrentAmt + dbo.Staging.AvailAmt
FROM dbo.Login INNER JOIN dbo.Staging ON dbo.Login.PlayerId = dbo.Staging.PlayerId DELETE FROM dbo.Staging … and for both queries, add this if there can be multiple processes accessing the staging table: WHERE dbo.Staging.ProcessId = @ProcessId
Or perhaps use a table variable or temp table so that the staging table is already isolate by it’s scope. Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
Problem with uploading to temporary tables is that connection must remain intact for longer than striclty necessary. With a staging table you can upload the data on-line, then start the data processing off-line.
I think what we’re really looking for here is a way to pass an array to a stored proc, which SQL Server doesn’t support. The closest thing to arrays is variable length character or binary fields. If network traffic is your main concern, then you can pass a formatted binary or character string to the stored procedure, which then parses the string into a temp table or table variable, and then performs the update. There are various routines out there for parsing string (based on either comma-delimited or fixed-width formats) that perform reasonably well. In particular, you may want to have a table-valued function that takes a string and returns a table of the individual values. In the end, you would have a statement like this:
update Login set
–select l.PlayerId,
CurrentAmt = l.CurrentAmt + t.mAmtInHand
, Avail_CurrentAmt = l.Avail_CurrentAmt + t.mmamt
from Login l
inner join dbo.fn_TableFromStrings( @p1, @p2, @p3 ) t
on l.PlayerId = t.PlayerId
where @p1 is a string of the player id’s, @p2 is a string of the mAmtInHand’s, and @p3 is a string of the mmamt’s. This allows you to pass the values directly to the SP using RPC (depending on your data access technology) in one shot. If this is the effect you’re looking for and you need help building the table-valued function, let us know.
-Hartmut5
]]>