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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 via email to