As I saw that ooSQLite was updated in incubator, regarding the SQLite backup API,I downloaded the source and compiled it under windows 7 64bit. From the sample /testing/loadDatabase.rex, I made my own database loading rex. The program creates a sqlite database in memory, with 6 columns, read and parse a 181Mb file and creates SQL insert statements, then inserts the resulting records (101657 records) into the memory database and last step save the in memory database to disc. This programs elapsed time was around 61-65 seconds( including copy to disc) , compared to the previous ~17 MINUTES when using a disc based database only, with same data. Great improvment. By the way I also downloaded the latest SQLite source 3.7.13 and used that in the compile above. All works very well. /hex
------------------------- Ursprungligt Meddelande: Från: hakan <[email protected]> Till: Mark Miesfeld <[email protected]> Kopia: Datum: fredag, 08 juni 2012 22:20 Ämne: Re: [Oorexx-devel] ooSQLite usage Well, tried with BEGIN TRANSACTION..... COMMIT and cut the time by around 5 minutes, so when initial loading is done, it seems that inmemory database is much quickier, because of hardisc speed, if a possibility to save the inmemory database to harddisc is available. Looking forward to your implementation of the .backup (.dot ) commands in ooSQLite. Keep up your good work (as a side note I am very satisfied with ooDialog 4.2 excellent work, I love it, maybe as I now understand how it works (at least to some point) and how to use it together with ooRexx, OO is hard for an old mainframer :-) ) /hex ------------------------- Ursprungligt Meddelande: Från: Mark Miesfeld <[email protected]> Till: [email protected], Open Object Rexx Developer Mailing List <[email protected]> Kopia: Datum: fredag, 08 juni 2012 21:13 Ämne: Re: [Oorexx-devel] ooSQLite usage Very cool. On Fri, Jun 8, 2012 at 11:07 AM, hakan <[email protected]> wrote: > After trying the ooSQLite package, I must say I am very impressed (well I am > not a DB person, so the learning curve is step) but I find it useful for > some of my ooREXX programs. > The samples have been very helpful, I am now able to create, load and query > a database/tables in ooREXX. > One thing is performance, when creating a new database, I have read/parsed a > file and then loaded it (92 000 rows) to a database/table , took around 17 > minutes on a i3 2.13Ghz ! From my reading (but I'm also not a DB person) this is a problem with INSERTS with not using an explicit BEGIN TRANSACTION COMMIT. As I understand it, for every individual SQL statement executed, SQLite will implicitly add the BEGIN TRANSACTION / COMMIT if it is not already within a transaction. The SQLite engine only does about 4 or 5 inserts a second under this condition. If you add the explicit BEGIN TRANSACTION before the first insert and then end with COMMIT the speed should be more normal. Something like this: BEGIN TRANSACTION; INSERT INTO "episodes" VALUES(0, NULL, 'Good News Bad News'); INSERT INTO "episodes" VALUES(1, 1, 'Male Unbonding'); INSERT INTO "episodes" VALUES(2, 1, 'The Stake Out'); INSERT INTO "episodes" VALUES(3, 1, 'The Robbery'); ... INSERT INTO "food_types" VALUES(13, 'Seafood'); INSERT INTO "food_types" VALUES(14, 'Soup'); INSERT INTO "food_types" VALUES(15, 'Vegetables'); COMMIT; I guess it is COMMIT not END TRANSACTION. I first thought it was END TRANSACTION. The above is the raw SQL, I'm assuming you are wrapping the raw SQL in a prepared statement. > I then tried to to create a ooSQLite database in memory instead of a file > (ooSQLite supports this, I used :memory: instead of a filename) then loading > the same amount of data took around 15-20 seconds, > but there is one problem I can't save the in memory database to a file with > ooSQLite, as I understand from SQLite website there exist an backup API that > let you save an in memory database to disc. > I have tried it with the ooSQLITE3.exe (cli interface) doing the same as the > ooRexx program( except backup) above does, by opening a :memory: database > then create a table, then use .read file with SQL inserts and last use the > .backup file CLI command. The result is that with the same amont of rows > (inserts) it took around 30 seconds to have the database copied to disc > including the SQl inserts. > So, Question is is it possible to implement the backup api (to be able to > copy a in memory database to disc) in ooSQLite, maybe also to make inserts > into a database from a file (like the .read file CLi command). ? > I may have missed something regarding this! Yes, both of those things are next on my list. Or at least very near the top. I intend to support the backup API as the next coding I do. In addition, I've planned from the start to support the "dot" commands present in the command line shell in ooSQLite classes. The most important ones like .dump, .read, .schema, etc., first. Maybe not all of them, I'm not sure they all make sense. But .dump and .read are very useful. Right now, my plan is to add them to the .ooSQLite class. So, something like: .ooSQLite~read(dbObj, fileName) would read the SQL statements in fileName and execute them on the database connection dbObj. .ooSQLite~dump(dbObj, fileName) would dump the data base to fileName. Although both of those commands could be added to .ooSQLiteConnection instead, makeing it: dbConn~dump(fileName) dbConn~read(fileName) I haven't though out the details completely yet, I just intend to add them as soon as possible. For the backup API, the plan is to have a ooSQLiteBackup class.: obj = .ooSQLiteBackup(dbConnObj) then methods on the class to do the backup. I haven't tried the BEGIN TRANSACTION / COMMIT yet. But I think everything is in place to do it. Just like using :memory for the file name. I hadn't tried it yet, but I knew it should work. Let me know if you run into any bugs. You can open them in Tracker, probably best, so I don't forget them. For other people reading this, and general FYI, because I haven't mentioned it. In the bin directory is also an executable, ooSQLite3.exe on Windows, oosqlite3 on Linux. This executable is a command line shell used to work with any SQLite database. C:\work.ooRexx\wc\incubator\ooSQLite\testing>ooSQLite3 ooFoods.rdbx SQLite version 3.7.12.1 2012-05-22 02:45:53 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE episodes ( id integer primary key, season int, name text ); CREATE TABLE food_types( id integer primary key, name text ); CREATE TABLE foods( id integer primary key, type_id integer, name text ); CREATE TABLE foods_episodes( food_id integer, episode_id integer ); sqlite> Type the ".help" to see commands. This is the exact command line shell provided by SQLite, just compiled during the ooSQLite build, named to ooSQLite.exe, and added to the package. Currently there is no change to the source code so it should work exactly like the SQLite command line shell. The reason I hadn't mentioned it yet is that the name change is not designed to conceal the source of the executable. It is included for convenience to ooRexx programmers and to 'fit' with the naming conventions of the package. The doc will clearly state that, and I wasn't going to mention the executable until I had the statement in the doc. I don't want any one thinking I wrote ooSQLite.exe myself. -- Mark Miesfeld ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Oorexx-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/oorexx-devel
