Hey guys,
I’d like to take and copy a single table from my disk based database to an in-memory database.
What’s the best way to do that? Right now I’ve tried selecting all the records in the table and then looping through the recordset and creating new database records and inserting them into the new in memory database. But that is taking a LONG time.
So I’m looking for some suggestions…
Sqlite?
Sorry. Yes. SQLite.
Make new database, attach old to new.
Make a create table with a select statement to copy from one table to a new one.
So you are saying the easiest way is to basically copy the entire database into memory. I don’t need to do that - just need to copy one table…
That’s not what he said. Attach one database to the other.
You attach which allows you to use SQL statements to work on both databases.
And than you can just create a table in the memory database filling it with result of a select statement in other table.
[quote=182125:@Christian Schmitz]You attach which allows you to use SQL statements to work on both databases.
And than you can just create a table in the memory database filling it with result of a select statement in other table.[/quote]
Oh. I didn’t know you could do that. Let me investigate!
[quote=182125:@Christian Schmitz]You attach which allows you to use SQL statements to work on both databases.
And than you can just create a table in the memory database filling it with result of a select statement in other table.[/quote]
Christian - Thank you. This works absolutely amazingly!
And it’s incredible how much faster an in-memory database really is even over a flash drive.
of course. But normally it’s just enough to increase sqlite cache size to keep big chunks of the database in memory.
But according to the Xojo docs, once the first table is built, you can’t change the cache size:
You can also increase the page size that SQLite uses by changing the page_size setting using the PRAGMA command. The default page_size is 1024, but a larger size can help reduce I/O. To change the setting, give it a new value after you create a database but before you create the first table. The page_size is permanently retained for the database after it is set:So that’s great for a new database. But what about databases already out with users where you now realize that you didn’t have enough cache so now you want to increase it. No way to do that…
Well, I suppose you could create a new database file, set the cache size, attach the old database, copy all the tables over, then delete the old database and rename the new.
But beyond that is there a way?
the page size is not the cache size!!!
The cache increase pragma works fine for all of my apps.
Ah. Thank you.
For the sake of completeness, you can change the page size on an existing db in conjunction with vacuum:
sqlite> create table t1(f1 numeric);
sqlite> insert into t1(f1) values (8888);
sqlite> pragma page_size;
1024
sqlite> pragma page_size = 512;
sqlite> vacuum;
sqlite> insert into t1(f1) values (9999);
sqlite> pragma page_size;
512
Thanks, Scott.
So would it be wise to increase BOTH the page size and the cache size?
page size avoids too many read requests to disk.
e.g. for a CD application some years ago performance increased a lot when we used 32 KBytes page size as the CD reading was not cached by Windows XP.
Cache should always be increased to at least 20 MB.
[quote=182198:@Scott Griffitts]For the sake of completeness, you can change the page size on an existing db in conjunction with vacuum:
sqlite> create table t1(f1 numeric);
sqlite> insert into t1(f1) values (8888);
sqlite> pragma page_size;
1024
sqlite> pragma page_size = 512;
sqlite> vacuum;
sqlite> insert into t1(f1) values (9999);
sqlite> pragma page_size;
512[/quote]
Well, Scott, it looks like you can do this in SQLite but unfortunately, not in Xojo’s implementation of it. I’ve verified what you stated by setting page_size in a database editor and it works. It reports the correct value. However, in Xojo, the page size doesn’t change. Not sure why but it isn’t.
the code above would reduce the page size?
Bad idea in my opinion.
[quote=182287:@Christian Schmitz]the code above would reduce the page size?
Bad idea in my opinion.[/quote]
I agree. I tried increasing the page size to 4096. It didn’t work. Then I read you needed WAL (multi-user mode) turned off. So I tried that. Still didn’t work. Yet it works fine executing the commands in Valentina Studio Pro. The page size increases.
Anyhow, increasing the cache size to the size of the database or slightly larger completely solved all my issues. Now it’s lightning fast.
I’m setting the page size to 4096 on any new databases for new users or new installs of my app. But existing will just have to stay at 1024 I guess…