On 7 Aug 2014, at 4:51am, Paul Dillon <paul.dil...@gmail.com> wrote:
> 1. Will moving these 8 query fields to a smaller table improve query > performance when joined to the larger table? My logic is that this small > table would only be about 5% the size of the full table, so the full table > scan to service the query might be faster. This depends on how big the smaller table would be compared to how much cache your system uses for your database file. It's impossible for us to answer because it depends a great deal on your specific hardware, OS, storage drivers, and background processes. Not only is it difficult to test under realistic conditions, but you may upgrade your computer later this year and find the same test gets the other result because relative sizes of rows, pages and caches have changed. > 2. Would it be worthwhile having that smaller table in a separate .db file, > so that the data is closer together on my SSD drive? Still difficult to say, for the same reasons as listed above. What you might find increases your speed is to make sure that those 8 columns are the first 8 columns listed in your table definition (after the primary key, if any). Each time you have SQLite read the columns for a row it has to read the whole row up to the last column it needs. So if all the unwanted columns are after these it will only need to read these 8 columns from the file. This change involves no extra programming and no extra storage, so it's very "cheap". > 3. Would the data loading performance be heavily impacted if I had to > insert into two tables instead of one? I use "INSERT OR REPLACE" for my > loading, with a single index. This would be the drawback which might stop me from making the change. The other would be that your programming would be more complicated, which takes time and would make debugging harder. Lastly, do not fall into the trap of premature optimization. You should not be looking for your program to run "as fast as possible". You should be looking for it to run "acceptably fast". You can spend 20 hours of programming to improve runtime by 1 minute -- a minute that your users wouldn't care about because they always go make a cup of coffee during a run anyway. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
