SQL Server Performance

multiple updates combined ?

Discussion in 'T-SQL Performance Tuning for Developers' started by DBVictim, Dec 11, 2005.

  1. DBVictim New Member

    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 Login
    SET 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 .

  2. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. DBVictim New Member

    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 PlayerId=@pid

    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 ?
  4. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. DBVictim New Member

    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 ?
  6. Madhivanan Moderator

  7. DBVictim New Member

    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
  8. Adriaan New Member

    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?
  9. DBVictim New Member

    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 .

  10. Adriaan New Member

    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.
  11. DBVictim New Member

    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 .
  12. merrillaldrich New Member

    Have you tried using the combination Update / From?

    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
  13. merrillaldrich New Member

    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.
  14. vsnreddi New Member

    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



  15. Adriaan New Member

    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
  16. merrillaldrich New Member

    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.
  17. Adriaan New Member

    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.
  18. Hartmut5 New Member

    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

Share This Page