Good morning all, A poster to digg mentioned some sql performance tuning tips. I've checked one of them out to see what difference it makes. My results are below, but first here's the poster's tuning tip list:
// SQL tuning tips // // Avoid using the following: // // - Boolean operators >, =, <=, is null, is not null // // - Not in, != // // - Like '%pattern', not exists // // - Calculations on unindexed columns or (use union instead) // // - Having (use a WHERE clause instead) // // // Do use the following: // // - Enable aliases to prefix all columns // // - Place indexed columns higher in the WHERE clause // // - Use SQL Joins instead of using sub-queries // // - Make the table with the least number of rows the driving table by making it first in the FROM clause // I checked out the last one: "Make the table with the least number of rows the driving table by making it first in the FROM clause" I compared the execution times of these two statements: SELECT one.test1, two.test2 FROM one INNER JOIN two ON one.id = two.id SELECT one.test1, two.test2 FROM two INNER JOIN one ON one.id = two.id Table one has a large number of records in it, and two has only 128. I ran each statement 10 times to see what the results were: Completed in 14 seconds Completed in 12 seconds Completed in 14 seconds Completed in 13 seconds Completed in 13 seconds Completed in 14 seconds Completed in 13 seconds Completed in 14 seconds Completed in 14 seconds Completed in 14 seconds Average is 13 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Completed in 0 seconds Average is 0 seconds Nice! I'll post the C++ source for the test to my support page if anyone wants it. Stay tuned for more, same bat time, same bat channel! -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
