On Sat, Feb 5, 2011 at 8:25 PM, Mark Phippard <markphip_at_gmail.com> wrote:
> On Sat, Feb 5, 2011 at 1:05 PM, Erik Huelsmann <ehuels_at_gmail.com> wrote:
>
>> Scenario (2) takes ~0.27 seconds to evaluate in the unmodified
>> database. Adding an index on (wc_id, local_relpath) makes the
>> execution time drop to ~0.000156 seconds!
>>
>>
>> Seems Philip was right :-) We need to carefully review the indices we
>> have in our database to support good performance.
> I wish this document were fully fleshed out, it seems like it has some
> good info in it:
>
> http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
>
> Getting indexes in place for the bulk of our reads is essential. It
> seems like now would be a good time to make that a priority. Of
> course adding more indexes will further slow down write speed (which
> seems bad already) so maybe the above document will give ideas for
> other optimizations.
>
> Did anyone see the tests I posted on users@ of a checkout with 5000
> files in single folder? I really thought we would be faster than 1.6
> already but we are actually several factors slower.
>
> My background is all with DB2 on OS/400. Something I was looking for
> in SQLite docs is whether it uses hints for the number of rows in a
> table. For example, DB2 optimizes a new table for 10,000 rows with
> increments of 1,000 when you reach the limit. If you know you are
> inserting 100,000 rows you can get a massive performance improvement
> by telling DB2 to optimize for a larger size. I was wondering if
> SQLite was doing something like optimizing for 100 rows or something
> small. I noticed the end of the checkout is really slow which implies
> it does not insert the rows fast. Maybe this is just an area where we
> need to use transactions better?
Their FAQ (http://www.sqlite.org/faq.html#q19) sure suggests that it's
not wise to do separate inserts: the document says SQLite easily does
50k inserts per sec into a table on moderate hardware, but only
roughly 60 transactions per second...
That would surely point into the direction of using transactions when
we need mass inserts! I'm not sure exactly where in our code these
inserts should be collected though. Maybe one of the WC-NG regulars
has an idea?
Bye,
Erik.
Received on 2011-02-05 20:42:12 CET