Many thanks for the advice Phil, I'll follow it. Ian
On 19/01/2011 13:06, Philip Graham Willoughby wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
