Timeline for Optimizing nested SQLite query
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 30, 2021 at 1:23 | history | edited | J.D. | CC BY-SA 4.0 |
added 375 characters in body
|
| Mar 29, 2021 at 23:43 | vote | accept | duhaime | ||
| Mar 29, 2021 at 23:43 | comment | added | duhaime | Ah thanks for the heads up! I think we'll quickly bump our heads against that 10 GB limit in this project, but this is good to know about for future projects! | |
| Mar 29, 2021 at 22:18 | comment | added | J.D. | @duhaime Btw if you do choose to switch to another database system eventually, depending on how you're developing your application itself, you might be able to bundle SQL Server Express Edition with the installer, which should be a little bit more powerful than SQLite (though has a 10 GB limit per database, since it's the free edition). | |
| Mar 29, 2021 at 22:13 | comment | added | J.D. | @duhaime Best of luck! Btw I'd be curious what your before and after looks like, in terms of average runtime if you care to share. (I'm a nerd when it comes to optimization metrics, especially for larger sized data. :) Also, with the proper optimizations, SQLite should be able to handle 100 million rows OK enough on actual computers (as opposed to a mobile device) so you should be able to get this to work sufficiently. | |
| Mar 29, 2021 at 20:56 | comment | added | duhaime | lol ah yes of course | |
| Mar 29, 2021 at 15:54 | comment | added | J.D. |
@duhaime Don't think you'll be able to create a primary key on the hashband field since it's not unique. But you'll want to create an index on the hashband field regardless if you use the default ROWID or not, which will be dependent on if you cluster on hashband. You can read more on that in the SQLite docs but my recommendation is start small and just add a nonclustered index on (hashband) or (hashband, file_id) (you should test your use case both ways to see which one is more performant). Then you can see if clustering on it helps.
|
|
| Mar 29, 2021 at 15:38 | comment | added | duhaime | Thank you for these comments! This DB is for identifying text reuse and will live on the personal machines of those who use my lab's little software package for identifying text reuse. Ultimately we'll make the DB configurable so that users who are capable of installing & managing a more robust db will be able to do so, but those who are new to programming will be able to use the hassle-free sqlite db. Should I remove the default ROWID and use hashband as the primary key on the hashband table? Seems like that should help... | |
| Mar 29, 2021 at 15:20 | history | answered | J.D. | CC BY-SA 4.0 |