That seems more or less as expected and I see nothing there to suggest difficulty with any of the solutions. Let us know more about your setup, OS, Platform, DB size, memory size, drive type, speed and size, etc, and we can suggest some good tweaks for setting up both. I'd say some testing with your actual data is the next step, to see query performance for both scenarios. Lastly, regardless of all the above, if your memory is significantly larger, and do only read-only access, the all-in-memory option will work very well, but is more effort to set up and more code to maintain. This should be tested before making a design decision. If your memory is significantly larger than the DB (4 times+) and do only read-only access, the all-in-memory option will certainly be very fast, I'm just not sure how much faster than an on-disk DB (on SSD) with all that memory servicing as cache. Lots of ways to make a read-only (or mostly reads) DB very fast. You can switch journaling modes, if you anyway going to only read from it, plus remove the need to sync data all the time. If the Data you want to "host" in memory is quite large, so that it occupies more than 50% of the computer's memory, SQLite still needs memory for doing the actual queries, which can easily amount to needing an amount as large as the DB (ok that's an extreme case, but possible) and between the query needs and working memory and journaling needs, it might choke and actually be slower than using it from disk and having all of that memory for the working memory and page-cache. SQLite working from a drive (SSD) with a sufficiently large cache memory allocated will be extremely fast, faster than you may think, and to the point where it might become hard to tell the difference between that and an in-memory DB. There are a few things I'd like to point out: Ok, that's a perfectly logical reasoning and it might work well. What exact problem are you trying to solve? There may already be known best practices or even example code. The only valid reason to do this is if you expect to lose the on-disk DB at some point, such as a flash drive that will be removed, but in such a case, copying the DB to an actual drive will be much much safer, and speed difference negligible for anything that can fit in working memory. May I ask why you so desperately want the attached DB to be forced into memory? If the request is for speed, then as long as you give it enough cache, the effective speed should be more or less the same. The DB name might be different and the speed may be slightly different, but for practical purposes it would be much of the same. The one has the full DB as it was on disk now in memory, the other has an already in-memory DB with now the same tables from the on-disk database replicated to it.īoth require establishing a memory DB and copying data across. Yes, there is "any" difference, but it is slight and might not matter. if you test only with a couple operations the fetch from disk into memory will be a larger cost than the accumulated time for thousands of accesses of that data. SQLITE_THREADSAFE=0 disable memory locks (if you only use single threads)īut mostly the file system is cached anyway, between the OS caching the file and sqlite caching pages that are already loaded which if sqlite finds it in cache, it's approaching memory.SQLITE_ENABLE_LOCKING_STYLE=0 disable locks. ![]() Working that way is probably still like working with a file, there are a few things that can make files much faster ![]() ![]() if that's something the java connector gives you maybe? I don't know what method you used to get the mmap mode. ![]() TL DR just use the file having extra hoops is just going to cause future you to regret increasing the maintenance cost. Is this right way to load disk file to memory? If yes why can't I see spike in heap class RestoreService ", e.getMessage()) db file into memory, because after running this code when I check heap-memory using Jconsole there is no much spike in it, heap memory is increased by only 10-20 MB, but my. I'm using restore command to load my database file (.db) from disk to main memory using below codeīut looks like "restore" command is not fully loading. My use case is to run queries faster by loading SQLite into memory. Implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.36.0.3' I'm working with SQLite in JAVA using JDBC driver
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |