D. Richard Hipp wrote:
I am running on linux, and I will need this to scale to at least
200,000 rows
If you upgrade to the vary latest code in CVS (version 3.2.2+)
and you create an index like this:
CREATE INDEX idx ON table(parent_name, name);
Then the query above should be very efficient.
You can try it creating the index above with version 2.x. It might
work. I do not remember how smart the optimizer was about using
indexes to optimize sorting in version 2.x (that was so long ago.)
If you really need to use 2.8, you could just create the index
above, make sure that it is the *only* index on the table, then
omit the ORDER BY clause all together. When the index above is
used, things will come out sorted or by name just because of
the way they work in SQLite. SQL does not guarantee that behavior
so it probably will not work that way on other database engines,
but it should work fine in SQLite.
I'm actually experiencing similar problem with sqlite3.0, when tables
are empty, select and modify operations are very fast, but when the
table gets more data the speed actually breaks suddenly down. My
experience, it doesn't matter, witch page size I use. Latest I tried
with page number size of 32000 and page size of 8192, but got the same
results. Tempspace is set to memory.
To clarify what I mean, in my application the parsers scan files and
produce cross-reference entries using inserts in two tables. In a
project with about 3000 files, the first 500 files are done in about
15sec. the latest 500 files take about 90sec.
Is this issue fixed in 3.3.2+ too? Does anyone have any idea, what could
be wrong here?
thanks
khamis
--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
* C/C++
* Java
* .NET (including CSharp, VB.Net and other .NET components)
* Classic Visual Basic
* PHP, HTML, XML, ASP, CSS
* Tcl/Tk,
* Perl
* Python
* SQL,
* m4 Preprocessor
* Cobol
