Speed Test Done !

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Speed Test Done !

Richard-118
Well, finally import 9,337,681 records into sqlite3 test2.db
and ran the test.


import sqtest4.txt : 2 min 28 seconds
select A, '32.0833' From T ; 9 min 20 seconds
--


I use another database for MacOS X
also works on Windows, Call Panorama

Did another test, comparing database...

import sqtest4.txt : 36 seconds
select from Field A contains 32.0833 / 55 seconds found 4322 records
out of 9,337,681

Still have found no sql program yet, that can beat Panorama in speed.

Regards-
Richard Nagle
CMS



Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Lindsay
Richard wrote:

>
> Still have found no sql program yet, that can beat Panorama in speed.


Since Panorama is RAM based, how about if you did your speed test with a
SQLite in memory database ?


--
Lindsay


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Puneet Kishor
In reply to this post by Richard-118

On Oct 5, 2005, at 10:57 PM, Richard wrote:

> Well, finally import 9,337,681 records into sqlite3 test2.db
> and ran the test.
>
>
> import sqtest4.txt : 2 min 28 seconds
> select A, '32.0833' From T ; 9 min 20 seconds

there is no constraint in the above statement... you are selecting A
(which, I am assuming, is a column name) and a string that looks like a
number from table T. Is that what you really want?

And, finally, have you indexed the table?


> --
>
>
> I use another database for MacOS X
> also works on Windows, Call Panorama
>
> Did another test, comparing database...
>
> import sqtest4.txt : 36 seconds
> select from Field A contains 32.0833 / 55 seconds found 4322 records
> out of 9,337,681
>
> Still have found no sql program yet, that can beat Panorama in speed.
>
> Regards-
> Richard Nagle
> CMS
>
>
>
>
--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
To find 32.0833 from field A, in Table T


Richard
PS: Nothing is index



On Thu, 06 Oct 2005 01:17:35 -0400, Puneet Kishor <[hidden email]>  
wrote:

>
> On Oct 5, 2005, at 10:57 PM, Richard wrote:
>
>> Well, finally import 9,337,681 records into sqlite3 test2.db
>> and ran the test.
>>
>>
>> import sqtest4.txt : 2 min 28 seconds
>> select A, '32.0833' From T ; 9 min 20 seconds
>
> there is no constraint in the above statement... you are selecting A  
> (which, I am assuming, is a column name) and a string that looks like a  
> number from table T. Is that what you really want?
>
> And, finally, have you indexed the table?
>
>
>> --
>>
>>
>> I use another database for MacOS X
>> also works on Windows, Call Panorama
>>
>> Did another test, comparing database...
>>
>> import sqtest4.txt : 36 seconds
>> select from Field A contains 32.0833 / 55 seconds found 4322 records
>> out of 9,337,681
>>
>> Still have found no sql program yet, that can beat Panorama in speed.
>>
>> Regards-
>> Richard Nagle
>> CMS
>>
>>
>>
>>
> --
> Puneet Kishor
>


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
In reply to this post by Lindsay
You can load sqlite into memory?
I do have 2GB of RAM.

Richar
d

On Thu, 06 Oct 2005 00:04:29 -0400, Lindsay <[hidden email]> wrote:

> Richard wrote:
>
>>
>> Still have found no sql program yet, that can beat Panorama in speed.
>
>
> Since Panorama is RAM based, how about if you did your speed test with a  
> SQLite in memory database ?
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Chris Schirlinger
> You can load sqlite into memory?
> I do have 2GB of RAM.

You can pass ":memory:" to the sqlite_open command to open a SQLite
database in memory I think it is

I imagine this will speed up your retrievals but without an index it
will still be slower that it should be.

With an index I imagine your test should have been sub 1 second to
return that select statement



Reply | Threaded
Open this post in threaded view
|

RE: Speed Test Done !

Steve O'Hara
In reply to this post by Richard-118

Your speed comparison is deeply flawed.......

Your select statement is non-sensical (it returns 2 columns, A and
'32.0883' with 9,337,681 records and you haven't told us if you're using
PRAGMA synchronous = OFF;?
Assuming you do a proper search like "select * from T where
A='32.0883';" you should make sure A is indexed.

Try this and come back to us with the results:-

sqlite3 test2.db
create Table T (A, B, C );
PRAGMA synchronous = OFF;
.separator ,
.import 'sqtest2.txt' T
create index TA on T(A);
select * from T where A='32.0883';

Steve
 

-----Original Message-----
From: sqlite-users-return-8176-sohara=[hidden email]
[mailto:sqlite-users-return-8176-sohara=[hidden email]
rg] On Behalf Of Richard
Sent: 06 October 2005 04:57
To: SQLite Users Mailing List
Subject: [sqlite] Speed Test Done !
Importance: High

Well, finally import 9,337,681 records into sqlite3 test2.db
and ran the test.


import sqtest4.txt : 2 min 28 seconds
select A, '32.0833' From T ; 9 min 20 seconds
--


I use another database for MacOS X
also works on Windows, Call Panorama

Did another test, comparing database...

import sqtest4.txt : 36 seconds
select from Field A contains 32.0833 / 55 seconds found 4322 records
out of 9,337,681

Still have found no sql program yet, that can beat Panorama in speed.

Regards-
Richard Nagle
CMS





Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

BertV
Panorama does not seem to support SQL (I did not find the term SQL on its
feature-page http://www.provue.com/panorama5.html), it requires a paid
runtime-version for distribution, it is restricted only for Mac and Windows.
It seems to need a lot of RAM, because it seems to mirror its database into
RAM, that is the way how it come to speed, also disadvantage that for  
transactions to work safe, data have to be written to disk in between, this
should be taken into benchmarks

see http://www.provue.com/

Bert

> -----Original Message-----
> From: sqlite-users-return-8176-sohara=[hidden email]
> [mailto:sqlite-users-return-8176-sohara=[hidden email]
> rg] On Behalf Of Richard
> Sent: 06 October 2005 04:57
> To: SQLite Users Mailing List
> Subject: [sqlite] Speed Test Done !
> Importance: High
>
> Well, finally import 9,337,681 records into sqlite3 test2.db
> and ran the test.
>
>
> import sqtest4.txt : 2 min 28 seconds
> select A, '32.0833' From T ; 9 min 20 seconds
> --
>
>
> I use another database for MacOS X
> also works on Windows, Call Panorama
>
> Did another test, comparing database...
>
> import sqtest4.txt : 36 seconds
> select from Field A contains 32.0833 / 55 seconds found 4322 records
> out of 9,337,681
>
> Still have found no sql program yet, that can beat Panorama in speed.
>
> Regards-
> Richard Nagle
> CMS

--
Met vriendelijke groet
Bert Verhees
ROSA Software
Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

John Stanton-3
In reply to this post by Richard-118
As I understand it Panorama is ram-based, and consequently unsuitable
for large databases.  Being memory resident naturally gives it greater
speed, but limits it to small scale applications.

I note that Dr Hipp quite clearly makes the point that Sqlite does not
even map the Sqlite file into virtual memory, thus avoiding addressing
limitations and permitting the database to be limited in size only by
the addressing limit of the file.  A wise design condition.

It would be interesting to compare the performance of Panorama with
Sqlite using ":memory".

JS


Richard wrote:

> Well, finally import 9,337,681 records into sqlite3 test2.db
> and ran the test.
>
>
> import sqtest4.txt : 2 min 28 seconds
> select A, '32.0833' From T ; 9 min 20 seconds
> --
>
>
> I use another database for MacOS X
> also works on Windows, Call Panorama
>
> Did another test, comparing database...
>
> import sqtest4.txt : 36 seconds
> select from Field A contains 32.0833 / 55 seconds found 4322 records
> out of 9,337,681
>
> Still have found no sql program yet, that can beat Panorama in speed.
>
> Regards-
> Richard Nagle
> CMS
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
In reply to this post by Steve O'Hara
Okay Steve,
I try this, please note: in PAN, there is no way for me to deselect
the other field ie: in this three field database, I do a search,
PAN will display all three field but with the found set, of the search  
string.

Richard


On Thu, 06 Oct 2005 06:22:31 -0400, Steve O'Hara  
<[hidden email]> wrote:

>
> Your speed comparison is deeply flawed.......
> Your select statement is non-sensical (it returns 2 columns, A and
> '32.0883' with 9,337,681 records and you haven't told us if you're using
> PRAGMA synchronous = OFF;?
> Assuming you do a proper search like "select * from T where
> A='32.0883';" you should make sure A is indexed.
> Try this and come back to us with the results:-
> sqlite3 test2.db
> create Table T (A, B, C );
> PRAGMA synchronous = OFF;
> .separator ,
> .import 'sqtest2.txt' T
> create index TA on T(A);
> select * from T where A='32.0883';
> Steve


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Dennis Cote
In reply to this post by Richard-118
Richard wrote:

> To find 32.0833 from field A, in Table T
>
>
Richard,

To correctly compare the speed of SQLite to Panorama running from memory
you need to do the following.

Start sqlite with no database file name. It will open a database in memory.

sqlite3

Now create your table and import your data.
 
create table T (A, B, C );
.separator ,
.import 'sqtest2.csv' T

Then, because you want to quickly search for records based on the value
of column A, create an index on column A.

create index I on T(A);

Now search for all records that match your constraint. Display all
columns for the found records. Note that this often be limited by screen
writing speed rather than search speed, so you should probably redirect
the output to a file first, and back to the screen after the search.

.output results.txt
select * from T where A=32.0833;
.output stdout

If you time these steps, I think you will be pleasantly surprised.

HTH
Dennis Cote



Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
Well, There must be something wrong with the test,
you wanted me to run:

Welcome to Darwin!
G4:~ rn$ sqlite3
SQLite version 3.2.5
Enter ".help" for instructions
sqlite> create table T (A, B, C );
sqlite> .separator ,
sqlite> .import 'sqtest4.txt' T
sqlite> create index I on T (A) ;
sqlite> .output results.txt
sqlite> select * from T where A=32.0833;
sqlite> .output stdout
sqlite>


As you can see its the exact code, you wanted me to test.
however, the results.txt was empty.

But do have import time: 3 min 14 second
and Over 12 Mins to index field A

Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Dennis Cote
Richard wrote:

> Well, There must be something wrong with the test,
> you wanted me to run:
>
> Welcome to Darwin!
> G4:~ rn$ sqlite3
> SQLite version 3.2.5
> Enter ".help" for instructions
> sqlite> create table T (A, B, C );
> sqlite> .separator ,
> sqlite> .import 'sqtest4.txt' T
> sqlite> create index I on T (A) ;
> sqlite> .output results.txt
> sqlite> select * from T where A=32.0833;
> sqlite> .output stdout
> sqlite>
>
>
> As you can see its the exact code, you wanted me to test.
> however, the results.txt was empty.
>
> But do have import time: 3 min 14 second
> and Over 12 Mins to index field A
>
Richard,

I suspect that the problem is due to the use of equality tests and
floating point values (a common problem). Repeat the test with the
following select statement instead;

select * from T where A >= 32.0833 and A < 32.0834;

I'm noticed that your import time is 194 seconds (3 min 14 sec) versus
the value of 148 seconds you reported earlier, that's 31% slower. Did
something else change on your machine? It might just be due to swapping,
since I these 10M records will require quite a large amount of ram.
Creating the index will require even more ram. Can you check the memory
usage for sqlite3 after you have imported the data and again after you
create the index?

Dennis Cote


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
Well, Did the correction,
and still the results text was nothing (zero)
there should be over 2000 hits...

cron import time: 3 min 20 second
and index time: 16 min 6 second.

Again, correct the last part:
  select * from T where A >= 32.0833 and A < 32.0834;
no results show in file.

Richard


On Thu, 06 Oct 2005 12:25:53 -0400, Dennis Cote <[hidden email]>  
wrote:

> Richard wrote:
>
>> Well, There must be something wrong with the test,
>> you wanted me to run:
>>
>> Welcome to Darwin!
>> G4:~ rn$ sqlite3
>> SQLite version 3.2.5
>> Enter ".help" for instructions
>> sqlite> create table T (A, B, C );
>> sqlite> .separator ,
>> sqlite> .import 'sqtest4.txt' T
>> sqlite> create index I on T (A) ;
>> sqlite> .output results.txt
>> sqlite> select * from T where A=32.0833;
>> sqlite> .output stdout
>> sqlite>
>>
>>
>> As you can see its the exact code, you wanted me to test.
>> however, the results.txt was empty.
>>
>> But do have import time: 3 min 14 second
>> and Over 12 Mins to index field A
>>
> Richard,
>
> I suspect that the problem is due to the use of equality tests and  
> floating point values (a common problem). Repeat the test with the  
> following select statement instead;
>
> select * from T where A >= 32.0833 and A < 32.0834;
>
> I'm noticed that your import time is 194 seconds (3 min 14 sec) versus  
> the value of 148 seconds you reported earlier, that's 31% slower. Did  
> something else change on your machine? It might just be due to swapping,  
> since I these 10M records will require quite a large amount of ram.  
> Creating the index will require even more ram. Can you check the memory  
> usage for sqlite3 after you have imported the data and again after you  
> create the index?
>
> Dennis Cote
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Robert L Cochran
select * from T where A = '32.0833';

(quote the 32.0833...'32.0833')

If you happen to have the sqtest4.txt file available for downloading
(via wget or http), I might try doing this myself.

Bob Cochran

Richard wrote:

> Well, Did the correction,
> and still the results text was nothing (zero)
> there should be over 2000 hits...
>
> cron import time: 3 min 20 second
> and index time: 16 min 6 second.
>
> Again, correct the last part:
>  select * from T where A >= 32.0833 and A < 32.0834;
> no results show in file.
>
> Richard
>
>
> On Thu, 06 Oct 2005 12:25:53 -0400, Dennis Cote
> <[hidden email]>  wrote:
>
>> Richard wrote:
>>
>>> Well, There must be something wrong with the test,
>>> you wanted me to run:
>>>
>>> Welcome to Darwin!
>>> G4:~ rn$ sqlite3
>>> SQLite version 3.2.5
>>> Enter ".help" for instructions
>>> sqlite> create table T (A, B, C );
>>> sqlite> .separator ,
>>> sqlite> .import 'sqtest4.txt' T
>>> sqlite> create index I on T (A) ;
>>> sqlite> .output results.txt
>>> sqlite> select * from T where A=32.0833;
>>> sqlite> .output stdout
>>> sqlite>
>>>
>>>
>>> As you can see its the exact code, you wanted me to test.
>>> however, the results.txt was empty.
>>>
>>> But do have import time: 3 min 14 second
>>> and Over 12 Mins to index field A
>>>
>> Richard,
>>
>> I suspect that the problem is due to the use of equality tests and  
>> floating point values (a common problem). Repeat the test with the  
>> following select statement instead;
>>
>> select * from T where A >= 32.0833 and A < 32.0834;
>>
>> I'm noticed that your import time is 194 seconds (3 min 14 sec)
>> versus  the value of 148 seconds you reported earlier, that's 31%
>> slower. Did  something else change on your machine? It might just be
>> due to swapping,  since I these 10M records will require quite a
>> large amount of ram.  Creating the index will require even more ram.
>> Can you check the memory  usage for sqlite3 after you have imported
>> the data and again after you  create the index?
>>
>> Dennis Cote
>>
>>
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Richard-118
Hey,

I be more than happy to uploaded to anyone web site, for all to download.
its just a test database based on the National Geological Study, its there  
data,
however its huge. 175 MB text file.

Richard



On Thu, 06 Oct 2005 14:06:47 -0400, Robert L Cochran  
<[hidden email]> wrote:

> select * from T where A = '32.0833';
>
> (quote the 32.0833...'32.0833')
>
> If you happen to have the sqtest4.txt file available for downloading  
> (via wget or http), I might try doing this myself.
>
> Bob Cochran
>
> Richard wrote:
>
>> Well, Did the correction,
>> and still the results text was nothing (zero)
>> there should be over 2000 hits...
>>
>> cron import time: 3 min 20 second
>> and index time: 16 min 6 second.
>>
>> Again, correct the last part:
>>  select * from T where A >= 32.0833 and A < 32.0834;
>> no results show in file.
>>
>> Richard
>>
>>
>> On Thu, 06 Oct 2005 12:25:53 -0400, Dennis Cote  
>> <[hidden email]>  wrote:
>>
>>> Richard wrote:
>>>
>>>> Well, There must be something wrong with the test,
>>>> you wanted me to run:
>>>>
>>>> Welcome to Darwin!
>>>> G4:~ rn$ sqlite3
>>>> SQLite version 3.2.5
>>>> Enter ".help" for instructions
>>>> sqlite> create table T (A, B, C );
>>>> sqlite> .separator ,
>>>> sqlite> .import 'sqtest4.txt' T
>>>> sqlite> create index I on T (A) ;
>>>> sqlite> .output results.txt
>>>> sqlite> select * from T where A=32.0833;
>>>> sqlite> .output stdout
>>>> sqlite>
>>>>
>>>>
>>>> As you can see its the exact code, you wanted me to test.
>>>> however, the results.txt was empty.
>>>>
>>>> But do have import time: 3 min 14 second
>>>> and Over 12 Mins to index field A
>>>>
>>> Richard,
>>>
>>> I suspect that the problem is due to the use of equality tests and  
>>> floating point values (a common problem). Repeat the test with the  
>>> following select statement instead;
>>>
>>> select * from T where A >= 32.0833 and A < 32.0834;
>>>
>>> I'm noticed that your import time is 194 seconds (3 min 14 sec)  
>>> versus  the value of 148 seconds you reported earlier, that's 31%  
>>> slower. Did  something else change on your machine? It might just be  
>>> due to swapping,  since I these 10M records will require quite a large  
>>> amount of ram.  Creating the index will require even more ram. Can you  
>>> check the memory  usage for sqlite3 after you have imported the data  
>>> and again after you  create the index?
>>>
>>> Dennis Cote
>>>
>>>
>>
>>
>>
>>
>


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Dennis Cote
In reply to this post by Richard-118
Richard wrote:

> Well, Did the correction,
> and still the results text was nothing (zero)
> there should be over 2000 hits...
>
> cron import time: 3 min 20 second
> and index time: 16 min 6 second.
>
> Again, correct the last part:
>  select * from T where A >= 32.0833 and A < 32.0834;
> no results show in file.
>
Richard,

Can you just dump a few sample lines from your table using the following?

select * from T limit 25;
select typeof(A) from T limit 25;

That will give us an idea of the format of your data.

Thanks
Dennis Cote


Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done !

Robert L Cochran
In reply to this post by Richard-118
Can you post a link to the source of the text data. That is, a link to
the web site where you got the data from.

Bob Cochran


Richard wrote:

> Hey,
>
> I be more than happy to uploaded to anyone web site, for all to download.
> its just a test database based on the National Geological Study, its
> there  data,
> however its huge. 175 MB text file.
>
> Richard
>
>
>
> On Thu, 06 Oct 2005 14:06:47 -0400, Robert L Cochran  
> <[hidden email]> wrote:
>
>> select * from T where A = '32.0833';
>>
>> (quote the 32.0833...'32.0833')
>>
>> If you happen to have the sqtest4.txt file available for downloading  
>> (via wget or http), I might try doing this myself.
>>
>> Bob Cochran
>>
>> Richard wrote:
>>
>>> Well, Did the correction,
>>> and still the results text was nothing (zero)
>>> there should be over 2000 hits...
>>>
>>> cron import time: 3 min 20 second
>>> and index time: 16 min 6 second.
>>>
>>> Again, correct the last part:
>>>  select * from T where A >= 32.0833 and A < 32.0834;
>>> no results show in file.
>>>
>>> Richard
>>>
>>>
>>> On Thu, 06 Oct 2005 12:25:53 -0400, Dennis Cote  
>>> <[hidden email]>  wrote:
>>>
>>>> Richard wrote:
>>>>
>>>>> Well, There must be something wrong with the test,
>>>>> you wanted me to run:
>>>>>
>>>>> Welcome to Darwin!
>>>>> G4:~ rn$ sqlite3
>>>>> SQLite version 3.2.5
>>>>> Enter ".help" for instructions
>>>>> sqlite> create table T (A, B, C );
>>>>> sqlite> .separator ,
>>>>> sqlite> .import 'sqtest4.txt' T
>>>>> sqlite> create index I on T (A) ;
>>>>> sqlite> .output results.txt
>>>>> sqlite> select * from T where A=32.0833;
>>>>> sqlite> .output stdout
>>>>> sqlite>
>>>>>
>>>>>
>>>>> As you can see its the exact code, you wanted me to test.
>>>>> however, the results.txt was empty.
>>>>>
>>>>> But do have import time: 3 min 14 second
>>>>> and Over 12 Mins to index field A
>>>>>
>>>> Richard,
>>>>
>>>> I suspect that the problem is due to the use of equality tests
>>>> and   floating point values (a common problem). Repeat the test
>>>> with the   following select statement instead;
>>>>
>>>> select * from T where A >= 32.0833 and A < 32.0834;
>>>>
>>>> I'm noticed that your import time is 194 seconds (3 min 14 sec)  
>>>> versus  the value of 148 seconds you reported earlier, that's 31%  
>>>> slower. Did  something else change on your machine? It might just
>>>> be  due to swapping,  since I these 10M records will require quite
>>>> a large  amount of ram.  Creating the index will require even more
>>>> ram. Can you  check the memory  usage for sqlite3 after you have
>>>> imported the data  and again after you  create the index?
>>>>
>>>> Dennis Cote
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Speed Test Done (RE )

Richard-118
Okay,,

Thanks to all, thanks to Robert, for the tweak on select string.
that work...

However, the select * from T where A = '32.0833' ;
work, and now the speed test report:

It found 2161 records, in 0.0075 seconds. ( out of 9,337,681 records )

Memory, did not jump that much, from 177 MB to 190 MB useages.
so, subtract the diff, and that is what sqlite3 is using doing all this....

Now, question,
can one load any and all database into memory? ( like Panorama ? )
or is this a freak speed test on searching string.
second, how often does a index need to rebuilt? 16 mins to create a index,
how long to rebuild it?

So, enclosing, PAN, had the fastest import time of 36 seconds,
and compare to this sqlite test, the second fastest time of finding of 55  
seconds.

**** I need to do this test again, with that output string, where sql tell
the user, that search took x amount of second searching threw 9 million  
records..
and found x amount of records that match string. ( aka Proof )



Regards-
Richard