>10000x speed increase for DB.query inserts/updates using BEGIN, COMMIT

[Pazyryk]

This is an update and correction (a retraction?! goodness me!) for this thread. Strangely, and embarrassingly, I have not been able to reproduce my results below using BEGIN and COMMIT. Not sure why... but after many hours on this I'm pretty sure that BEGIN and COMMIT don't do anything and I was doing something wrong in my time tests.

The good news, however, is that the concept of transactions is correct, just my implementation was wrong. See post #5 to see how to do it.

I tried this with 99% certainty that it wouldn't work. But it did!

So my mod uses SavedGameDB a lot, with 60 or more inserts/updates per turn. This was taking about 10 seconds, which was making me think twice about my whole approach. However, I read that SQLite should be able to do 50000 or so inserts per second, but could only do a few dozen "transactions" per second (although my results indicated 6 transactions per second in a running civ5 game). Each insert, update or deletion is a separate transaction -- unless you wrap them with BEGIN and COMMIT, which makes them all one transaction.

So this takes 10 seconds for roughly 60 updates:

		local SavedDB = Modding.OpenSaveData()
		...
		for i = 1, countUpdates do
			local id = markedForUpdate[i]
			local quaryStr = "UPDATE "..table_name.." SET "
			local bFirst = true
			for key, value in pairs(target_table[id]) do
				if bFirst then
					quaryStr = quaryStr..key.."='"..value.."'"
					bFirst = false
				else
					quaryStr = quaryStr..", "..key.."='"..value.."'"
				end
			end
			local quaryStr = quaryStr.." WHERE ID='"..id.."'"
			Dprint (quaryStr)
	
			for row in SavedDB.Query(quaryStr) do end
		end

But this takes between 0 and 0.001 seconds to do about 60 updates:

		local SavedDB = Modding.OpenSaveData()
		...
		for row in SavedDB.Query("BEGIN") do end
		for i = 1, countUpdates do
			local id = markedForUpdate[i]
			local quaryStr = "UPDATE "..table_name.." SET "
			local bFirst = true
			for key, value in pairs(target_table[id]) do
				if bFirst then
					quaryStr = quaryStr..key.."='"..value.."'"
					bFirst = false
				else
					quaryStr = quaryStr..", "..key.."='"..value.."'"
				end
			end
			local quaryStr = quaryStr.." WHERE ID='"..id.."'"
			Dprint (quaryStr)
	
			for row in SavedDB.Query(quaryStr) do end
		end
		for row in SavedDB.Query("COMMIT") do end

The code above just writes data from a Lua table (target_table) to a DB table in exactly the same structure.

(I'll write a tutorial on SavedGameDB eventually...)

 

[Sneaks]

Awesome discovery. I will absolutely need to implement this. Cannot wait to start seeing bits of whatever it is you are working on behind the scenes.

 

[Pazyryk]

I'm tempted to put my mod concept out as a developer diary. But on the other hand, I have a bad habit of biting off more than I can chew. So I'm holding off until I'm very confident about finishing and have a clear idea about timeline. Just as a teaser, it's shaping up pretty close to this post from Apr 2010 (yes, I already have civilian units that can stack, enter rival or even an enemy city, or enter tiles with rival units --civ5 Lua/xml/sql modding is that powerful -- and a tiny bit of not-very-good-yet AI function for this).

In the meantime, I'm hoping to put out two generic functions sometime soon (#1 perhaps in the next 2 weeks if the Civ5 cache doesn't defeat me). These are:

1. A generic TableSaverLoader system. It builds globals automatically from a set of SavedGameDB tables (you just have to provide names of tables) and moves data in/out of SavedGameDB as needed. It's built up from Gedemon's mod but generic, and has it's own DB/global integrity checking (--which I'm using now to understand and hopefully workaround Civ5 cache issues). This system has made it pretty easy for me to implement about 10 tables specific to my mod.
   
   
2. A very generic RuleChanger system. This allows you to create and apply "rule changes" as sql or xml rows. A rule could be anything accessible to Lua: block a tech for someone or everyone, change happiness from a building, change a tiletype yield, change a unitType combat strength, fire a Lua function, etc.). The rule can be fired from Lua directly or fired automatically based on it's own prereqs (CivType, hasTrait, knowsTech, adoptedPolicy, LuaReq, and so on ... also hasRuleChange so these can be chained). One example in my mod is that when one civ researches a particular tech, it blocks that tech for all others. Now, of course you could do this yourself through Lua pretty easily. But RuleChanger allows you to add this as a single row in the RuleChange table (setting only 4 of the roughly 30 fields for this particular example). Since I'm adding 100s of these kind of effects, I wanted to avoid specific Lua programming/bughunting for each one.

 

[robk]

Oooh, awesome find! I wish I would have thought about transactions a while ago, though. Would have saved me some pain.

 

[Pazyryk]

Can't. Reproduce. Results. Anymore. Arghhhhhh!!!! s#!^ h^%7 f*$^ d@^*!!!! Beat head against wall for a while. Move on...

Well, the concept is correct according to this http://www.sqlite.org/faq.html#q19. Also, I just can't live with only 10 updates per second. Won't work for my mod project.

Unfortunately, you can't cram BEGIN and COMMIT into a single DB.Query. If you run DBChange("do A; do B; do C") (note: see my little DBChange function at the bottom of the post) then A happens but B and C don't, and no error is given. So the challenge is to get a lot of inserts or updates into a single transaction without using ";".

Ah ha! It can be done! 500 inserts in 0.08 seconds! 1600 updates in 0.31 seconds! That puts us back in 2011 rather than 1950's computer technology.

Here's the solution for inserts:

insert into MyTable (colX, colY, colZ)
select 'x1','y1','z1' union all
select 'x2','y2','z2' union all
select 'x3','y3','z3'		-- returns shown for clarity; these are spaces in my string construction

Using the the syntax above, I've succeeded in inserting up to 500 rows at a time. 600 gives me a query error. Fire Tuner won't print a line that long, by the way, but the code runs and 500 rows are added to the DB table. This runs in a little less than 0.1 sec. On my computer, this takes about 50 seconds as separate insert statements (i.e., as 500 transactions).

The solution for updates is a little more complicated:

update MyTable set
colX=case when ID=1 then 'x1' when ID=2 then 'x2' when ID=3 then 'x3' end,
colY=case when ID=1 then 'y1' when ID=2 then 'y2' when ID=3 then 'y3' end,
colZ=case when ID=1 then 'z1' when ID=2 then 'z2' when ID=3 then 'z3' end
where ID in (1,2,3)

Remarkably, I've concatenated strings with up to 1600 updates using the syntax above (all as one line). Again, the Fire Tuner just silently refuses to print my query string, but the DB updates correctly according to my command. This took 0.3 seconds.

As a side note: I've had no problem loading up a table with 10000 lines and then saving/loading. Howerver, I haven't put this through all the permutations of load-from-within-game, etc., yet.


Here's my DBChange function ('cause it drives me crazy using an iterator when I don't want iteration):

function DBChange(str)
	--for single DB commands when you don't want a row iteration value
	local DBQuery = Modding.OpenSaveData().Query
	if bVerboseDB then print(str) end
	for _ in DBQuery(str) do end
end