Stress-testing SQLite

[Richard Gaskin]

Mark Stuart wrote:

> on Fri Oct 29 19:17:40 CDT 2010, Richard Gaskin wrote:
>>>
> Thanks in advance for sharing your experiences with large data sets if
> SQLite.
> <<
>
> Hi Richard,
> How many tables and how many columns per table (on average) are you
> talking about?

Probably just a single table, with about 20 columns.

> That can make a big difference to the performance if there are JOINS
> involved.

None - all flat.  I'd even considered rolling my own data storage for 
this one, but the indexing is more work that I'd care to do if I can use 
an off-the-shelf solution.

> If not, then that's not so much a problem.

Good to hear.

> Will the user always apply a WHERE filter to the data?

For the most part, yes.  I'll have about three or maybe four indexes, 
and most of the time the searches will be using those.  I may have the 
odd case of a substring search, but the performance hit is anticipated.

> What's the potential return record set count on a typical filter?

It'll vary, and in my own tests that seems to be the only bottleneck 
with SQLit; queries that return little data are ultra speedy, but once 
we get into large amounts of return data I see the hit.

> I'd be happy to do some stress testing if you can give me some details.

Thanks.  Don't knock yourself out; I'll be continuing with my own tests 
here, but if this sort of thing passes for entertainment in your house 
then of course I'd be grateful for any details you turn up.

--
  Richard Gaskin
  Fourth World
  LiveCode training and consulting: http://www.fourthworld.com
  Webzine for LiveCode developers: http://www.LiveCodeJournal.com
  LiveCode Journal blog: http://LiveCodejournal.com/blog.irv