Re: [sqlite] Multilple tables with the same schema and prepare query performance

2010-10-29 Thread john Papier
No I haven't yet measured it.. I was only in the process of designing the database layout... Given that my queries are very simple, it may be fine to do the prepare_query every time.. I will do some perf testing and reply back. On Fri, Oct 29, 2010 at 9:40 AM, Pavel Ivanov wrote: > > order of 1

Re: [sqlite] Multilple tables with the same schema and prepare query performance

2010-10-29 Thread Pavel Ivanov
> order of 10 to 100 of these tables. When doing operations on these tables, I > want to avoid having to do a prepare_query every time for performance > reasons. Did you measure your performance and find that prepare_query is a bottleneck? > Since the tables have exactly the same schema, in theor

Re: [sqlite] Multilple tables with the same schema and prepare query performance

2010-10-29 Thread john Papier
I know the order of rows is possible and efficient if your query plan is using the special ROWID: SELECT ROWID, col1, col2 from Table ORDER BY ROWID. the ORDER BY is a no-op in this case (see http://www.sqlite.org/queryplanner.html) Say you iterate 10 rows, and later want to continue where you le

Re: [sqlite] Multilple tables with the same schema and prepare query performance

2010-10-29 Thread Simon Slavin
On 29 Oct 2010, at 4:52pm, john Papier wrote: > The thing is, I need to keep a cursor to where in the > table I was last searching, so I can continue the search from where I left > off, which is why using multiple tables was preferable; i.e., i can track > the row_id, and then resume the search t

[sqlite] Multilple tables with the same schema and prepare query performance

2010-10-29 Thread john Papier
Hi, I need to create multiple tables all having the same schema. The number/names of the tables will by dynamic. There would be somewhere in the order of 10 to 100 of these tables. When doing operations on these tables, I want to avoid having to do a prepare_query every time for performance reason