I'm user of a Python application that has poorly indexed tables and was wondering if it's possible to improve performance by converting the SQLite database into an in-memory database upon application startup. My thinking is that it would minimize the issue of full table scans, especially since SQLite might be creating autoindexes, as the documentation says that is enabled by default. How can this be accomplished using the SQLAlchemy ORM (that is what the application uses)?
1
-
You said you are a non-programmer, but to me this sounds very much like a programming-heavy job. But rather than change SQLite to work in-memory I would instead simply add indexes to the existing SQLite database. That may get the performance within your requirements without touching the application code. Search for "SQLite database editor".wberry– wberry2011-12-19 16:02:47 +00:00Commented Dec 19, 2011 at 16:02
Add a comment
|
2 Answers
At the start of the program, move the database file to a ramdisk, point SQLAlchemy to it and do your processing, and then move the SQLite file back to non-volatile storage.
It's not a great solution, but it'll help you determine whether caching your database in memory is worthwhile.
Comments
Whenever you set a variable in Python you are instantiating an object. This means you are allocating memory for it.
When you query sqlite you are simply reading information off the disk into memory.
sqlalchemy is simply an abstraction. You read the data from disk into memory in the same way, by querying the database and setting the returned data to a variable.
5 Comments
Josh Rosen
I think that the OP wants to avoid disk access costs by caching the database in memory.
user1104069
Correct, I want to minimize disk access.
Derek Litz
Well that would involve querying the db for all data, establishing a session with a supported in memory db, creating the tables and inserting the queried data. The sqlalchemy tutorial contains all the information necessary to do this.
user1104069
When I said I was a user, I should have further said I am a non-programmer. I can cut and paste solutions, but beyond that... Is there a specific recipe for doing what I want or do I have to read the entire sqlalchemy tutorial (and learn to program) as well?
Derek Litz
It's certainly best you get some basic understanding before undertaking a task like this. The quickest way to accomplish the task, yourself, certainly would be the SQLAlchemy tutorial. There, however, are other problems. If your database is too large for memory you'll have to choose what you cache at startup, or even modify the cache based on usage. This can get complicated and a "good" solution is largely determined by your specific use case. That's all if this is a good solution for your performance problems. Determining this also requires domain knowledge and profiling of the code base.