Zugg Software :: View topic

Apprentice Joined: 18 Feb 2007 Posts: 190

even indexing portal table didnt helped, iteration over 65 portals takes ~6seconds Sad

unbearable, this and #map "portal" queuing fluke makes this version unusable

Posted: Sat Nov 22, 2008 4:44 pm

Please send me your map file along with detailed instructions on exactly how to test this. I don't have any good portal test files here, and honestly, I haven't touched the portal code (so I have no idea why it's slower).

If you can provide the script to test the portal "queuing fluke" too, that would be a big help.

Apprentice Joined: 18 Feb 2007 Posts: 190

well, while i overcome portalenable sluggishness (doubling yet another cmud build in function, additionally i got 5+ fold speed increase over 2.37 engine - it takes 80ms for them portals... <3 lua c api & sqlite)
cmud fails to acknowledge changes made to db, unless i open portals ui window Rolling Eyes

this is getting rather frustrating, as i planned to do lot of db editing directly

Apprentice Joined: 18 Feb 2007 Posts: 190

sure, but this gonna take a moment, i have to rip some lua scripts for standalone work

Posted: Mon Nov 24, 2008 9:59 pm

I got your files and started playing around with this.

I narrowed the speed issue down to just the raw speed of the SQLite database. Now, on my computer your "runme" script prints out values more like 400-600ms and not 6 seconds. But maybe that's just a computer speed issue. But the statement that is taking all the time is:

Posted: Mon Nov 24, 2008 11:39 pm

OK, I think I have made some good progress on this. It will be interesting to see if this ends up causing any data corruption issues.

First, this is all related to a post by Rorso (which happens to be on page one of a Google search of "SQLite update speed"...heh).

The issue is that SQLite always flushes changes to disk right away. Their designer made the decision that data integrity was always more important than speed optimization. In all INSERT and UPDATE cases, SQLite is slower than most other databases.

In the above post from Rorso, I talked about trying to use Transactions to speed things up. So I tried this in the mapper and didn't have much luck. Apparently even when in the middle of a transaction it is still writing updates to the disk somehow. I don't really understand this...doesn't make any sense to me. But the timings of the "runme" script that intoK sent me didn't change when using transactions.

So then I started looking more into the "PRAGMA synchronous" setting for SQLite. SQLite 3.x changes the default to "FULL" whereas SQLite 2.x had the default of "NORMAL". I tried changing this to NORMAL and also didn't see any real change in the speed results.

Then I changed the "synchronous" flag to "OFF" (0) and that made a *huge* difference. Of course, now the database wasn't being saved to disk. I added the Transaction code back, and then set the "synchronous" flag to FULL before doing the Commit. That worked. Changes are saved to the database.

And with this change, the results of the "runme" speed test is about 60-70ms instead of 400-600ms! So this is a factor of 10X speed improvement.

What this means is that the mapper now works like the rest of CMUD with-regard-to the database changes. The changes are only written to disk on a background timer of once every minute or so. I have set the mapper to 64 seconds instead of 60 so that it remains out-of-sync with the 60 second timer on the normal package settings. The #SAVE command will also force the mapper to flush it's data to disk, and closing a map will flush the changes to disk.

Since I'm still using transactions, there shouldn't be any problem with data corruption. The worst that could happen if the power went out suddenly or your computer crashed is that you'd lose the changes to your map made in the past 64 seconds. The previous mapper using ADO/MDAC would have had similar problems, although I don't know what the timer was in making changes to the disk file. My experience with the old mapper was that crashing in the middle of updating the database would actually cause a corrupted database (because it wasn't using transactions).

This should hopefully a good compromise between data loss and speed. I can also easily change this timer to a different value if you think it needs to be different. Or, you can create your own #ALARM trigger to execute the #SAVE command as often as you want.

Apprentice Joined: 18 Feb 2007 Posts: 190

Posted: Tue Nov 25, 2008 4:10 pm

No, it has nothing to do with the Delphi SQLite wrapper. When I was doing this test, I was issuing raw SQL statements to the SQLite driver itself. Take a look at http://theopensourcery.com/sqlite2.htm and you'll see some additional benchmarking that shows SQLite is very slow at doing database updates. It's optimized for queries, not for insert/update. It's a well-known issue with SQLite.

As I said above, I had a simple "UPDATE PortalTbl SET Enable=1 WHERE PortalId=1" SQL statement. If this statement was commented out, my code ran in 5ms. With this line enabled, it ran in 500ms. This statement was being sent directly to the SQLite DLL. All of the rest of the Delphi code for searching for the Portal, updating the internal cache, etc was all enabled. It was just the SQL UPDATE statement that made the huge speed difference.

Posted: Tue Nov 25, 2008 5:04 pm

I just used this same technique for both the Help database loading and the Package Library loading and saw improved performance from both. My method doesn't seem to prevent the file from saving...it saves fine. The trick is to start a transaction, then set "synchronous=0" and then perform all of the normal insert/updates. Then when the updates are finished, set "synchronous=2" and then finally call Commit. Using a transaction ensures that the full set of updates are written to the file just once at the very end.

For example, on the full Get Updates for the Help system, this change gave a factor of X2 in performance. And that's using the Delphi TQuery wrapper for SQLite (using Delphi Dataset.Append, Dataset.Post commands instead of direct SQL statements).

So, I'm going to go with this method for now. It should solve many of your performance issues so that you don't need to keep trying to do everything at a low level from within Lua instead.

But also keep in mind that this is the first BETA version and I typically don't do performance tweaking at this early phase. So it might get better in the future, but I need to get back to making sure that the basic functionality is working before I put a lot of time into performance optimizations.

Apprentice Joined: 18 Feb 2007 Posts: 190

cheers!
but early beta _is_ the place to raise those issues - while underlying mechanics can be easily changed Very Happy

Posted: Tue Nov 25, 2008 9:54 pm

No, that's actually not true. You *always* get basic functionality working first and *then* do the optimization. It doesn't make any sense to spend time optimizing something that isn't even working yet. We aren't talking about underlying architecture changes here...we are talking about database-specific optimization tweaks. For example, the old zMUD mapper has tons of ADO/MDAC specific optimization tweaks. Things like the "synchronous=0" trick is a SQLite-specific tweak.

Yes, things like how and what to cache, what to index, etc, are design-type optimizations that need to be designed before any implementation. But we are well past that point now.

Early beta testing is about identifying which functions and routines are completely broke, crash, etc.