Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread James Steward
> Ken <[EMAIL PROTECTED]> wrote:
> 
>   Can you do a test load to a memory database. What is the performance 
> (row/sec) of your data using an in memory database? 

I tried a RAMDisk on Windows.  I haven't tried at home on Linux.
I'm not sure if I tried the C API to RAMDisk database combination, only
the Tcl API to RAMDisk database combination.  Maybe Tcl was the
bottleneck here.

>   Is that a burst rate or a sustained data rate?  You need to achieve 
> about 70k rows per second to be able to maintain 66k rps. 

Well, at the moment it is mostly only bursts of data, but I have no control
over the possibility that it will become even faster, or continuous, so I want
need to push the envelope.

>   Maximum TPS is 60 on a 7200 rpm drive. A 15000 RPM drive should get 
> about 120, which is double the TPS. So maybe a faster drive would be 
> helpful.

Quite possible.  At the moment I'm not sure what HD is installed on the 
vehicle where the data is produced, but it can always be improved upon
by throwing $$ at it ;-)

Cheers,
James.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread Ken
Or you will need to get a faster disk drive! And more of them.
   
  Consider a raid 0 system using striping. High speed fiber connects will also 
be helpful.
   
  Can you do a test load to a memory database. What is the performance 
(row/sec) of your data using an in memory database? 
   
  Is that a burst rate or a sustained data rate?  You need to achieve about 70k 
rows per second to be able to maintain 66k rps. 
   
  Maximum TPS is 60 on a 7200 rpm drive. A 15000 RPM drive should get about 
120, which is double the TPS. So maybe a faster drive would be helpful.
   
  Ken
  

[EMAIL PROTECTED] wrote:
  James Steward wrote:
> > Michael Ruck wrote:
> > 
> > I know that a natural join exists, but it is not automatic as
> > it seems to be in MySQL. 
> 
> Thanks , and thanks to all who replied to my questions.
> 
> I've been testing SQLite's speed, for inserting the type of data I 
> gather from the field. I've tried encapsulating multiple inserts 
> between begin and commit statements, fiddling the pragmas 
> and with both Tcl and C interfaces, even using a RAM disk to 
> store the database file.
> 
> I can receive up to 2,000,000 records in about 30 seconds from
> the field, and I can't seem to jam them into an SQLite database
> any faster than about 100 seconds at best, on my system.
> 

SQLite will do about 5 inserts/sec on my Linux workstation.
Doing 2 million in 30 seconds is going to be pushing the 
envelope. You should probably consider a different solution.

--
D. Richard Hipp 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.

2007-11-20 Thread drh
James Steward <[EMAIL PROTECTED]> wrote:
> > Michael Ruck <[EMAIL PROTECTED]> wrote:
> > 
> > I know that a natural join exists, but it is not automatic as
> > it seems to be in MySQL. 
> 
> Thanks , and thanks to all who replied to my questions.
> 
> I've been testing SQLite's speed, for inserting the type of data I 
> gather from the field.  I've tried encapsulating multiple inserts 
> between begin and commit statements, fiddling the pragmas 
> and with both Tcl and C interfaces, even using a RAM disk to 
> store the database file.
> 
> I can receive up to 2,000,000 records in about 30 seconds from
> the field, and I can't seem to jam them into an SQLite database
> any faster than about 100 seconds at best, on my system.
> 

SQLite will do about 5 inserts/sec on my Linux workstation.
Doing 2 million in 30 seconds is going to be pushing the 
envelope.  You should probably consider a different solution.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-