Hullo everyone, I'm relatively new to sqlite. I have an optimization problem regarding an sql query.
Background: I have a database with two tables one with -say- 12k rows of data, and the other with more. The first table (lets calls it A) has the following columns: source_id, x_pos, y_pos, magnitude, time_stamp, bin_num (source_id) is unique and I have indexes on all columns. The second table (let's call it B) has the following columns: source_id, time_stamp, bin_num No column is unique and I have indexes on all columns I create/update the database without a problem using, sql_prepare, sql_bind and sql_step. I use begin/commit to bundle transactions for the updates. The updating has decent timing. I query the database with a query to extract x_pos, y_pos from table A for instances (rows) that match a particular bin_num(s) in B provided the source_ids are the same in both tables. The query take ~30.00 seconds when run about 7k times in a loop. This is not acceptable. Obviously the query is inefficient and/or the database isn't organized optimally etc. etc. Here's the query: select * from A a, B b where b.bin=? and a.soruce_id=b.source_id or elect * from A a, B b where b.bin in (?, ?, ?, ?, ?) and a.soruce_id=b.source_id the "?" is filled in by a bind. (Similar to the updates I use sql_prepare, sql_bind and sql_step) to run the query. I then loop through the resulting rows I retrieve from the database. Doing this say 7k times (and repeatedly) is hugely costly. How can I optimize my query/database for better performance? * I've researched placing the query (several of them) in a transaction, however I don't see an option (in prepare, bind, step) to provide for callbacks as one can in sql_exec. In the absence of a callback I don't see how I can process the retuned data via transactions??? * Should I sacrifice prepare, bind and step for sql_exec? (i.e. install a callback and bundle several queries in a single transaction) * Should I organize the database differently? * Is the query sub-optimal? Any thoughts/ideas will be appreciated, rosemary. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
