Hi Jeff,
the best solution is to attach the file based db to the in memory db
and then do a cross insert/select, like:
INSERT INTO fileDB.test1 select * from test1
This is a fully working example (RB2006 syntax):
Dim memDB As new REALSQLDatabase
Dim db As new REALSQLDatabase
Dim f As FolderItem = GetFolderItem("").Child("test.rsd")
Dim createTableSQL As String = "CREATE TABLE test1 (field1 text,
field2 text, field3 text)"
// Create in memory db
if not memDB.Connect then
MsgBox "Error while creating in memory db"
return
end if
// Create in memorty table
memDB.SQLExecute(createTableSQL)
if (memDB.Error) then
MsgBox memDB.ErrorMessage
Return
end if
// Insert some dummy values
memDB.SQLExecute("INSERT INTO test1 (field1, field2, field3)
VALUES ('test1', 'test2', 'test3')")
memDB.SQLExecute("INSERT INTO test1 (field1, field2, field3)
VALUES ('test1', 'test2', 'test3')")
memDB.SQLExecute("INSERT INTO test1 (field1, field2, field3)
VALUES ('test1', 'test2', 'test3')")
memDB.SQLExecute("INSERT INTO test1 (field1, field2, field3)
VALUES ('test1', 'test2', 'test3')")
memDB.SQLExecute("INSERT INTO test1 (field1, field2, field3)
VALUES ('test1', 'test2', 'test3')")
// Create file based db
db.DatabaseFile = f
if (not db.CreateDatabaseFile) then
MsgBox "Error while creating file db"
return
end if
// Create file based table
db.SQLExecute(createTableSQL)
if (db.Error) then
MsgBox db.ErrorMessage
Return
end if
// Close all pending transactions or attach fails
db.Commit
memDB.Commit
if not memDB.AttachDatabase(f, "fileDB") then
MsgBox memDB.ErrorMessage
return
end if
// Dump all records from memory to disk
memDB.SQLExecute("BEGIN TRANSACTION")
memDB.SQLExecute("INSERT INTO fileDB.test1 select * from test1")
memDB.Commit
memDB.DetachDatabase("myFileDB")
// Close db(s)
memDB.Close
db.Close
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
On Feb 14, 2006, at 11:44 AM, Jeff Ayling wrote:
I've added the BEGIN TRANSACTION but it hasn't really made a change
to the speed.
I'm using a New DatabaseRecord to add each row from the in-memory
db - would INSERT INTO be quicker? Or are there any other tricks to
saving an in-memory db to a file? I really need to speed this up
dramatically, any ideas would be great thanks.
This is close to my code but there are about 20 columns in my live
code.
rs = memdb.SQLSelect("SELECT * from trax")
db.SQLExecute("BEGIN TRANSACTION")
While Not rs.eof
sdr= New DatabaseRecord
sdr.Column("id") =rs.Field("id").StringValue
sdr.Column("Name") =rs.Field("Name").StringValue
sdr.Column("Album") =rs.Field("Album").StringValue
sdr.Column("Composer") =rs.Field("Composer").StringValue
f=getfolderitem(rs.Field("Location").StringValue)
sdr.Column("Location") =f.name
db.InsertRecord("trax",sdr)
rs.MoveNext
Wend
db.Commit
Thanks
Jeff
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
