Updating multiple sql values in one query?

So, I’ve got this code to update the levels of people at the end of a round. It works great but when there’s 20+ players on at once, the updating causes a second of server lag where everything just stops.



    // This code is called once for every player which has received xp during the round.

        local q = db:query( "UPDATE leveling SET db_level = " .. level .. ", db_xp = " .. xp .. " WHERE db_steamid = \"" .. db:escape(ply:SteamID()) .. "\";" )
        q.onSuccess = function() MsgAll("Level and xp updated.") end
        q.onError = function( _, err, sqlstr ) MsgAll( "(SQL) ERROR: " .. err  .. " (" .. sqlstr .. ")" ) end
        q:start()


Anyone know any ways to update all the players in one query or some other form of optimization?

You can do this:
[LUA]
local escapedSteamIDs = {}
for k,v in pairs(player.GetAll()) do
table.insert(escapedSteamIDs, SQLString(v:SteamID())
end
local whereList = table.Concat(escapedSteamIDs, “,”)
local q = db:query("UPDATE leveling SET db_xp = db_xp + " … XPToAdd … “WHERE db_steamid in (” … whereList … “)”)

[/LUA]
XPToAdd is the amount of xp every player gets at the end of the round. This only works if the xp players get is the same for everyone though.
I personally don’t think that you should store the level at all since that is redundant information (the level can be directly inferred from the xp)

Are you using async queries? I’m updating all my players at the end of a round without any issues.
Show how your sql function is running.

The players don’t all get the same amount of xp per round. Is my way the only way to do it with varying xp?

[editline]14th August 2015[/editline]



function tb_update_stats(ply)
	if !ply:IsValid() then return end
	local level = ply:GetNWInt("level")
	local xp = ply:GetNWInt("xp")
	local q = db:query( "UPDATE leveling SET db_level = " .. level .. ", db_xp = " .. xp .. " WHERE db_steamid = \"" .. db:escape(ply:SteamID()) .. "\";" )
	q.onSuccess = function() MsgAll("Level and xp updated.") end
	q.onError = function( _, err, sqlstr ) MsgAll( "(SQL) ERROR: " .. err  .. " (" .. sqlstr .. ")" ) end
	q:start()
end


Are you sure it’s this code causing the lag?
It doesn’t look like it would cause any issues o.O

Can you not Concatenate the inserts?


local tab = {}

--in your function
table.insert( tab, Query )

--after you are done
table.concat( tab, ";" ) -- ";" Is to close the query and possibly start a new one. (Can also be ";
" or "; " if you want to make it pretty print)(This is what you want to query by literally doing q:query( table.concat( tab, ";" ) ) )
tab = {} -- clean table


Otherwise try calling (with calling I mean query them)


BEGIN TRANSACTION and COMMIT TRANSACTION

Before and after you start inserting. (Obviously begin before and commit after.)

Can’t you just run multiple updates in the same query?



f = "UPDATE leveling SET db_xp = %d, db_level = %d WHERE db_steamid = %q;
"

q = ""

for each player
  q = q .. f:format( xp, level, db:escape( steamid ) )
end

db:query( q )

It’d be weird if you couldn’t do transactions or anything more complicated than INSERT, DELETE, or UPDATE.

I’ll give this a go when I get the chance. Thanks everyone for the help :slight_smile: