Stress-testing SQLite -- degradation on big selections

[Ruslan Zasukhin]

On 10/30/10 4:10 AM, "Richard Gaskin" <ambassador at fourthworld.com> wrote:

Hi Richard,
Hi Mark,

> 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.

Okay this answers my question.

So now I can tell you Richard, ONLY because of columnar format,
Valentina DB can do some operations 20 times faster of any ROW-based DB.

And if multiply this to other features of Valentina you can become
With Valentina 20 *4 * 2 = 100-200 times faster.


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

Very not true :)

When you talk about speed and time, all is relative.
You need talk about ABSOLUTE numbers instead.
                   
For one person/project/app 10 sec for search is very fast,
For another this is incredibly slow.

Richard, when you say, I NEED SPEED, you must say:
  I want query in 100 sec or in 0.01 sec

Only having this info, people can advice you.

 
>> 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.

Exactly Richard. 

This is named "degradation of DB" on grow of

A) records number in table(s)
B) records number in the RESULT

Yet from 1996 year, when I did my first benches of Valentina against
 FileMaker, 4D, Access, mySQL, ...

And this very first thing I have found also.

Most dbs if not all, have powerful degradation when recs number grow.

Also exists some special N related to RAM of computer. Below this N dbs go
yet more or less nice. After N degradation can go by jump x10 worse.

With Valentina this N was much higher in the same RAM, because of much more
compact db format for data. And after N it was very good yet.


For example, if for most dbs difference in time between 50 and 500,000 recs
in result is huge, for Valentina it is almost flat.


 
>> 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

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]