Ian, On 18 Jan 2011, at 16:40, Ian Hardingham wrote:
> In general, my server is too slow. It has to run many operations a > second, and many DB operations, so the exact definition of "too slow" is > a little arbitrary. Nothing in that paragraph implies that the DB speed is a problem, or that this specific query is relevant. For your own sake, measure the performance precisely and accurately so that you know how much time is spent in each area of your application. If all you know is that the overall performance is slow, you don't know enough. You also need to give yourself a target speed that you regard as fast enough, not least so you know when to stop working on this and do something else. Once you know that it is the database that's using most of the time and you have identified which queries are using most of that time you can use EXPLAIN QUERY PLAN to ask SQLite how it will execute your worst queries; you can use that information to make a more informed decision about how best to optimise. If any table in the query lacks an appropriate index, it would benefit you to add one for instance. It is generally cheaper to run a CREATE INDEX command than to redesign your tables/queries/application logic. And make sure you have issued ANALYZE at least once since your database took the size/shape it has now. Chapter 2 of O'Reilly's High Performance MySQL (Finding Bottlenecks: Benchmarking and Profiling) is a good introduction to this field. > I'm intruiged that you feel a pre-computed table is not a good idea. Is > there an expectation that that would not be especially faster than my > current method? It would be faster at query-time than your current method. It would be much slower at insert/update time, and more likely to wind up out-of-step with the original table and leave inaccurate results. It would consume vast amounts of extra space making it less likely that your database will fit in caches (because you haven't normalised your schema at all, see below). Since you have not shown us your schema there is a limited amount anyone on this list can comment on: we do not know what indexes you already have, and you have not shown us the output of EXPLAIN QUERY PLAN or EXPLAIN for either of the queries which concern you. >From what we can see in the query you posted it seems you have not normalised >your database at all. If you stored a table mapping INTEGER PRIMARY KEY >playerID numbers to player names with a UNIQUE INDEX over the player names and >used the IDs instead of the names in multiturnTable your query would go much >faster, even if it required a full-table scan or two. This is because the >engine would need to read and compare fewer bytes of data per row to determine >if the row was a match. You would get more rows per page and therefore less >I/O would be required. The entire database would also be smaller and therefore >more likely to fit in the OS and hardware caches in the machine you're using. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
