Zugg Software :: View topic

Posted: Thu Jul 24, 2008 9:43 pm

Today I wrote the conversion between the ADO/MDAC *.MDB mapper file into the new SQLite 3 database file. So far I'm really liking it.

I just converted my large map database for Aardwolf. It has 5640 rooms with 15406 exits. It took 25 seconds to convert this file. The resulting SQLite file was only 6.6MB vs the original 41.2MB of the ADO file.

Now I will start the modifications to the mapper to load this new file. It will be very interesting to see how the speed of loading rooms/exits compares. I know that I've been having a lot of trouble with MDAC/ADO on my Vista system being very slow lately. So I'm looking forward to some possible speed increases.

Btw, good news for Rorso...I compared using the Delphi TDataSet Append/Post method for creating records with doing the SQL UPDATE/INSERT commands directly, and using the SQL had a *huge* speed boost. So I'm going to modify the Package Library to use this method to see if that will speed up the updates for you.

Edited: Hmm, looks like my MDB file just needed to be compacted. After compacting, the original MDB file was only 5.89 MB. So the SQLite file is actually slightly larger. It will be interesting to see if the SQLite database has the same "compaction" problems that ADO/MDAC does and if this file size increases over time with lots of use. Not sure if SQLite has any sort of "compact" command.

SubAdmin Joined: 18 Nov 2001 Posts: 5182

I will refer you to old post I can't be bothered to dig up. The command is VACUUM, and I am pretty sure it supported in SQLite 3.x. If I understand how the command actually works then what it was doing was recording all the current records into a new file, deleting the old file, renaming the new file, and finally attaching that new file as the active database file. I seem to recall this process left the index key numbers intact which means it should work with an in memory cache that is coordinated to the file by that means. Check at http://www.sqlite.org/lang.html for details.

Wizard Joined: 14 Oct 2000 Posts: 1368

Adept Joined: 04 Feb 2005 Posts: 246

Not that I really like ADO/MDAC... but it was nice to have an interface that could do some bulk updating that the mapper didn't support (well, perhaps it was the case that I really didn't know how to drive the zScript/COM for the mapper to do what I needed--Access I just know, and I think I did what I needed with a simple find/replace, or maybe direct copy/paste into the datasheet view of the table).

Posted: Sat Jul 26, 2008 4:31 am

Zhiroc,

There's plenty of database managers for SQLite. My favorite is SQLite Administrator. http://sqliteadmin.orbmu2k.de/

Posted: Thu Jul 31, 2008 12:09 am

As I posted in the main forum, I was able to test the new mapper database format on a very large map sent from a player.

The old file was 25.5 MB and the new SQLite file is 44.5 MB. I think this might be because I was able to use a "compress" option with ADO for memo/text fields that isn't available in SQLite. The map takes about 2 minutes to convert to the new format. The load time to open the map was 8 seconds in the old CMUD and is about 9 seconds in the new CMUD. So that's pretty comparable. For my own smaller maps, the new mapper loads the new files faster than before, but for large maps it looks like it's about the same.

So, this is good news so far. This is before any sort of optimizations (and after removing all of the ADO-specific optimizations). So I might be able to improve this more in the future.

The big difference in loading the map is that with ADO I was using a Server cursor that preventing loading the entire database when the database is opened. Then an SQL statement is used to load the internal cache from the database.

With SQLite, the entire database is read when the various tables are opened and then SQL statements are used to load the internal cache. So essentially SQLite is loading the database twice. The Dataset tables used to update the various tables have SQL statements such as "SELECT * FROM ExitTb" assigned to them, so when they are opened, they execute the SQL statement. Then there is a complex JOIN SQL statement used to load the exits for the rooms on the map.

I don't see any way to prevent the initial "SELECT * FROM ..." statements from executing unless I abandon the use of the Delphi Dataset completely and convert everything to raw SQL statements. I don't really want to do that if I can avoid it. Also, the Room Properties currently uses these Datasets to populate the controls on the screen.

Anyway, even with this "double-loading" it's still the same speed as ADO. In ADO the second set of SQL queries took longer than SQLite, so the overall speed ends up the same. So I'm not going to go crazy trying to optimize this until I think more about it.

The really good news for people who have read this far is that I think I have the basic mapper working now with SQLite. It was a much faster conversion than I expected (mostly because I'm still using the Delphi Dataset and my own cache for most of the code). Since I'm going to be gone all next week at the World SciFi Convention in Denver, I'm going to try and release a "preview" of the new mapper tomorrow so that people can play with it and try to load all of their map files while I'm gone.

Posted: Thu Jul 31, 2008 2:27 am

This might be a good time to start using indexes. There several of us that do database stuff for our day jobs, and we would love to help you optimize the database.

Have fun at the World SciFi Convention!

Posted: Thu Jul 31, 2008 3:08 am

Oh, the database already has tons of indexes. Those were needed even with ADO. Without indexes the mapper would be a complete dog.

When I get to my work computer tomorrow, I'll post the JOIN that I am using for the main part of the mapper for the database experts to look at.

Wanderer Joined: 02 Sep 2005 Posts: 85

sorry for bumping, but are we still looking at a compact map database option for the new SQL databases.. I mean, the forums tell me to search existing, and I may have used the wrong keywords for the other searches, but, anyway, this is one of the reasons I wanted to post, the compact map db option for cmud? :)

Posted: Fri Mar 06, 2009 8:07 pm

It's still on the to-do list, sorry. In the meantime, there are various SQLite utilities you can download from the Internet that will also do this. You'll have to do some google searching for them though, or see some of the links mentioned above.