Re: [sqlite] SQLite vs. Oracle (parallelized)

[Alexey Pechnikov]

Hello!

On Friday 27 February 2009 23:57:39 Allan Edwards wrote:
> You setup 2 seperate machines, sqlite on one, and Oracle on another...
> they both have the exact make and model hard drives and OS.  Which one
> can write data faster to the hard drive?

There are two strategies:

1. Rows are writing in realtime. Oracle is faster because using internal write 
buffers and buffers doesn't really flushing to disk immediately.

2. Rows are writing in batch mode. For example exactly 1 write transaction 
(with dirrerent count of rows) per second. SQLite is faster because don't have 
internal write buffers and doesn't write some log files and jornals. But SQLite 
index creating performance is not very good and using a lot of indexes may 
provide problems. A lot of realtime write operations (in financials and like 
applications) are not possible by SQLite (auto-commit mode has bad performance 
becouse each operation must be flushed to disk).

And read SQLite performance can be more better then Oracle because SQLIte 
dosn't have multy-version control for rows and doesn't have client-server 
interprocessing overhead but can perform a lot of parallels readings in 
different threads.

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Alexey Pechnikov]

Hello!

On Saturday 28 February 2009 00:31:37 pyt...@bdurham.com wrote:
> Your approach of splitting large data sets sounds similar to what other
> SQLite users with large data sets seem to be doing. At a high level,
> this sounds like how one would partition data using Oracle? I'm going to
> start a new thread on this topic.

Oracle manage partitions automatically but SQLite databases are manages by 
your application. So you must write more code. But good result is that SQLite 
databases may be packed and removed to archive and your application can easy 
resolv this situation. And a lot of external programs (as example demon for 
network monitoring or tacacs/radius server) can store data in single databases 
without working with full dataset. And Oracle is using some buffers for 
modified 
data and can perform a lot of write/read transactions in parallels. SQLIte-
driven application must plan write operation and use some hashes/in-memory 
databases as buffers and perform saves sets of records to on-disk database 
because write operation with single record is not effective. So I have tcl data 
collectors which regular save chunks of data to on-disk SQLite database. Of 
cource users operations by web-interface performs immediately and it's work 
fine because in web-applications there are a lot of read operations and a few 
write operations.

> > With your hardware I think 100Gb dataset is not limit.
>
> Good news. I'm looking forward to verifying this over the next month or
> so.

Please mail to list or to me directly about you problems and results. I do 
some big systems based on SQLite now and the information about similar 
projects is important for me. 

I support debian repository with my own SQLite build with extra modules. For 
big projects is needed some additional functions (data compression, md5 
functions, extended csv import, etc.).

Debian etch: 
deb http://mobigroup.ru/debian/ etch main contrib non-free
deb-src http://mobigroup.ru/debian/ etch main contrib non-free

Debian lenny: 
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free

Keyring may be installed as
sudo aptitude install debian-mobigroup-keyring

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[python]

Alexey,

Thank you for your reply and for sharing your success with SQLite. I'm
excited by your results (60x faster). On an informal basis, we've been
going back and re-benchmarking some of our old, 'traditional'
(Oracle/Informatica) ETL/DW projects and we now believe the majority of
these systems could be simplified and made faster by using alternative
techniques based on in-memory data processing (definitely) and/or SQLite
(we still need to test). 

Your approach of splitting large data sets sounds similar to what other
SQLite users with large data sets seem to be doing. At a high level,
this sounds like how one would partition data using Oracle? I'm going to
start a new thread on this topic.

> With your hardware I think 100Gb dataset is not limit.

Good news. I'm looking forward to verifying this over the next month or
so.

Best regards,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[python]

Allan,

Thanks for your reply. I'm new to SQLite, but have used a similar list
of databases (with an emphasis on server vs. client side databases)
professionally for years. My background is designing and building
enterprise BI, ETL, and data warehouse systems using databases like
Oracle, DB2, SQL Server and ETL tools like Informatica, Ab Initio, BO
Data Integrator, etc.

My goal is to be able to offer our customers cost effective, high
performance alternatives to traditional commercial ETL solutions. We
recently re-implemented a mid-sized Oracle/Informatica based ETL system
in Python using in memory data structures (dicts) and improved the
performance by a factor of 20x. We're now looking to expand this
approach to SQLite for environments where the volume of data is greater
than available memory.

> When you talk about performance comparisons your question really needs to be 
> squared up to what the database is used for. Are you on a server, 
> workstation, or embedded? How large in the database? How are the internal 
> algos of the database engine lined up? What is the memory footprint? How did 
> you configure what is configurable in the database?

I'm interested in exploring whether or not SQLite can be used as an ETL
tool with large data sets (~80+ Gb). In this context, SQLite would be
run on 64-bit Intel servers with lots of RAM (16-64 Gb). The data would
be stored/processed on server local SCSI drives vs. located on a SAN.
File access would be via a single process per SQLite database. The
interface language would most likely be Python.

> The reality is I BET and I would love to know myself Sqlite is just as fast 
> on inserts to the same hard drive as oracle.

I would love to see such a benchmark as well.

> Are you some kind of political Oracle covering biggot?

LOL. No. I try to be as database agnostic as possible.

> Or, do you want another chance to post something that states you are trying 
> to find the best data storage solution to deliver sincere value to your 
> client in terms of a database choice?

Subtle point here ... I'm not looking for a long term data storage
solution - I'm exploring the possibility of using SQLite as a way to
validate, transform, and pre-aggregate raw data that would in turn be
exported to raw text files and imported (in final form) to a customer's
data warehouse.

> Sometimes that choice is Sqlite, but in some cases it is Oracle instead.

Agreed.

Regards,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Igor Tandetnik]

"Chris Wedgwood"  wrote in message
news:20090226172406.ga685...@puku.stupidest.org
> On Thu, Feb 26, 2009 at 09:53:18AM -0600, Jay A. Kreibich wrote:
>
>>   I've always wondered about this... someone please correct me if I'm
>>   wrong, but my understanding is that there wasn't any difference
>>   between a left and right join except for the argument order.  It
>>   seems like implementing right joins should be pretty easy by just
>>   having the parser reverse the arguments when it builds the parse
>>   tree.  I realize that might screw with the optimizer a bit, but is
>>   there anything obvious I'm missing?
>
> When you use a right and a left join together you can't do that.
> Sometimes you can restructure the queries to make use or two left
> joins or a sub-query, but it's often cumbersome and not always the
> same thing.
>
> If in general there is a way to turn something like
>
>  select c1,c2,c3 from m left join l on l.mid=m.id right join r on
> l.rid=r.id
>
> into something conceptually the same that only uses left joins then
> perhaps the optimizer could do this for us.

An equivalent query using only left joins would look something like 
this:

select c1,c2,c3
from r left join (m left join l on l.mid=m.id) ml on ml.rid=r.id;

The problem, I believe, is not in parsing right joins or replacing them 
with equivalent left joins, but in optimizing the resulting statement. 
SQLite's optimizer is fairly simple: I suspect if it sees something like 
the above, it'll throw its hands up in the air and resort to full table 
scans all around.

For this reason, right joins likely have to be natively supported by the 
optimizer - you can't just fake them by transforming the parse tree.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Chris Wedgwood]

On Thu, Feb 26, 2009 at 09:53:18AM -0600, Jay A. Kreibich wrote:

>   I've always wondered about this... someone please correct me if I'm
>   wrong, but my understanding is that there wasn't any difference
>   between a left and right join except for the argument order.  It
>   seems like implementing right joins should be pretty easy by just
>   having the parser reverse the arguments when it builds the parse
>   tree.  I realize that might screw with the optimizer a bit, but is
>   there anything obvious I'm missing?

When you use a right and a left join together you can't do that.
Sometimes you can restructure the queries to make use or two left
joins or a sub-query, but it's often cumbersome and not always the same
thing.

If in general there is a way to turn something like

  select c1,c2,c3 from m left join l on l.mid=m.id right join r on l.rid=r.id

into something conceptually the same that only uses left joins then
perhaps the optimizer could do this for us.

There might be corner cases though were such a transform isn't
possible.  When it's vague like this I defer to people like Igor who's
SQL abilities constantly amaze me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Alexey Pechnikov]

Hello!

On Thursday 26 February 2009 18:53:18 Jay A. Kreibich wrote:
>   I agree.  The only major addition I'd like to see is full support for
>   referential integrity.  I know you can fake it, to a degree, with
>   triggers, but I'd still like to see it baked into the database engine
>   itself.

But why? I did use only triggers for referential integrity checks in 
PostgreSQL because trigger can return user-friendly description on national 
lang. As example from SQLIte-based project for russian users:

CREATE TRIGGER users_update_before before update on users begin
  select RAISE (ABORT,'Нельзя изменять идентификатор')
WHERE OLD.id != NEW.id;
  SELECT RAISE(ABORT, 'Указан несуществующий регион.')
WHERE NEW.region_id != '' AND (select ROWID from regions where 
id=NEW.region_id) IS NULL;
  SELECT RAISE(ABORT, 'Пользователь с указанным именем уже существует! 
Пожалуйста, придумайте для этого пользователя другое имя.')
WHERE  (SELECT ROWID FROM users WHERE nickname = NEW.nickname and 
rowid!=NEW.rowid) IS NOT NULL;
end;

Standart foreign key error message is not good for users. User does not 
understand message such as "Foreign key error: ... key ... table...".

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Jay A. Kreibich]

On Wed, Feb 25, 2009 at 10:29:59AM -0800, Chris Wedgwood scratched on the wall:
> On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote:
> 
> > Remember: SQLite is not trying to replace Oracle.  SQLite is trying
> > to replace fopen().
> 
> Exactly.  SQLite is *wonderful* for this, and I would hate to see it
> compromised by adding features that really aren't necessary.

> I'm very happy with how small, compact, and blisteringly fast, SQLite
> is for a whole range of uses, I don't want it bloated down to make it
> something it's not.

  I agree.  The only major addition I'd like to see is full support for
  referential integrity.  I know you can fake it, to a degree, with
  triggers, but I'd still like to see it baked into the database engine
  itself.

> Oh, except right joins.  If you add those I won't mind :-)

  I've always wondered about this... someone please correct me if I'm
  wrong, but my understanding is that there wasn't any difference
  between a left and right join except for the argument order.  It
  seems like implementing right joins should be pretty easy by just
  having the parser reverse the arguments when it builds the parse
  tree.  I realize that might screw with the optimizer a bit, but is
  there anything obvious I'm missing?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Chris Wedgwood]

On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote:

> Remember: SQLite is not trying to replace Oracle.  SQLite is trying
> to replace fopen().

Exactly.  SQLite is *wonderful* for this, and I would hate to see it
compromised by adding features that really aren't necessary.

There are a lot of feature requests, and many of them are to make
SQLite more like MySQL or Oracle --- in which case I would suggest
people really should be using those.

I'm very happy with how small, compact, and blisteringly fast, SQLite
is for a whole range of uses, I don't want it bloated down to make it
something it's not.

Oh, except right joins.  If you add those I won't mind :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Allan Edwards]

I must trade my works for currency.  "Will Code for FOOD" : - )  I
consult and architect systems professionally and really can't afford
to do much free work.  As much as I love to develop solutions, we are
not in the Star Trek age of we just live to better others and
ourselves!  If we were you would find me saying "Computer, lets work
up a new piece of software on Sqlite... etc. etc. etc."



On Tue, Feb 24, 2009 at 7:55 AM, P Kishor  wrote:
> On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards  
> wrote:
> ..
>>
>> I have personally written a socket based server on top of the database
>> and it works very well.  So I have actually scaled the database myself
>> as I preached in this email.  Don't be a WIMP and map shares to share
>> a database... write a socket based beauty like YOURS TRULY! hehe  For
>> "most" solutions it is wonderful.  After years and building millions
>> of lines of code keeping the business delivery requirements fulfilled
>> in the most simple manner seems to be the best approach for me.  If
>> you are the same, stay agnostic to all solutions available and run up
>> a strategy that will give you the best of all worlds.  And yes, at
>> times you have to write a little EXTRA code to get there! : - )
>>
>> Allan
>>
>> P.S.  If somebody does decide to build enterprise Sqlite, I would love
>> to throw in my 2 cents on how to write the stuff on the outside to add
>> in the big dog features.  I was working out tonight and while thinking
>> about this I believe you could actually maintain the wonderfulness of
>> the core engine, then scale the library from an outside piece of code.
>>  Then you can keep integrity on both sides of the fence and not make
>> sqlite into sqlitetoomuch.
>>
> ..
>
> Have you considered taking the "socket based server" that you wrote, I
> am assuming, on top of SQLite, and donating it to public domain/open
> source, putting it on the sqlite wiki, so others may benefit?
>
> Who knows, with a seed like that, someone may well build a
> SQLiteEnterprise (as much an oxymoron as that might be).
>
> --
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> Sent from: Madison WI United States.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
W Allan Edwards
214-289-2959
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Alexey Pechnikov]

Hello!

On Monday 23 February 2009 23:54:56 pyt...@bdurham.com wrote:
> Are there any plans to enhance SQLite to support some of Oracle's
> parallel processing or partitioning capabilities?

But Oracle does not database for "cloud computing". 
You can't get no-installable and no-administrate Oracle instance. 
You can't use in-memory Oracle databases. 
You can't easy add new functions to Oracle. 
You can't [effectively] use Oracle on single SATA disk and common CPU. 
You can't have hundreds of Oracle databases on single host. 
You can't create mobile replica of oracle dataset for smartphone or PDA. 
...
And do you like Oracle? :-)

Multi-core processor can perform operations with multiple instances of SQLite 
in parallel. For common web application there are hundreds of read operations 
on each write operation and selects performance is more important than locks 
on parallel write operations. For very fast write operations you must use key-
value databases such as berkeleydb or tokyocabinet. 

But SQLite have problems with long transactions. Oracle is good for this job. 

Dataflow processing applications prefer in-memory databases and can be SQLite-
driven. May be this is not task for SQL-databases but SQL is very useful and 
comfortable.

May be Oracle is good for mainframes but non-administrate clusters are [more] 
popular now. For example mnesia database (writed on erlang) can be good for 
this job by it's very complex solution.

So I think Oracle may be used for applications with long transactions and only 
if you have high-powered servers. SQLite can be used for many other causes. 
This preference may be lost in case SQLite will like Oracle .

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Jay A. Kreibich]

On Mon, Feb 23, 2009 at 10:34:50PM -0500, pyt...@bdurham.com scratched on the 
wall:
> Hi Billy,
> 
> >> Are there any plans to enhance SQLite to support some of Oracle's
> >> parallel processing or partitioning capabilities?
> 
> > I realized that you're asking Richard, and not the peanut gallery, but 
> > I figured I might as well ask out of curiosity: why do you want to 
> > see these features in SQLite?

> It would be great to see SQLite be able to exploit the extra processing
> power of multiple cores. This is not a request for handling multiple
> simultaneous transactions - it is a request to have single transactions
> be processed across multiple cores.

  The only way to go multi-core is multi-process and/or multi-thread.
  Multi-process is pretty much out of the question, given the design
  goals of SQLite.  That leaves multi-threaded.

  Ask Dr. Hipp about his feelings** on the efficiency and elegance of
  heavily threaded programming, and in specific how easy they are to
  test and verify.  Go ahead... It's a rough week at work and I could
  use the entertainment.


  ** with which I agree.

> Another interesting Oracle feature is compression. Oracle's compression
> techniques not only compress data, but also speed up many types of
> selects.

  This is one area I do think SQLite might benefit from.  As processors
  get much faster and storage only gets a little faster, it can often
  be faster to read smaller chunks off disk and decompress them than it
  is to read the uncompressed chunk.  It gets tricky with small page
  sizes, but on some platforms-- especially those doing direct access
  to slower flash devices, it might be worth it.  There are a number of
  factors to balance, but it might be an interesting exercise,
  especially if you're not interested in saving file space, only
  improving I/O speeds.
  
  Hwaci, Inc. (the commercial side of SQLite) does offer a read-only
  compressed file solution.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[P Kishor]

On Tue, Feb 24, 2009 at 8:19 AM, Allan Edwards  wrote:
> I must trade my works for currency.  "Will Code for FOOD" : - )  I
> consult and architect systems professionally and really can't afford
> to do much free work.  As much as I love to develop solutions, we are
> not in the Star Trek age of we just live to better others and
> ourselves!  If we were you would find me saying "Computer, lets work
> up a new piece of software on Sqlite... etc. etc. etc."

I am not sure what to make of this. I thought you wrote that you had
already "written a socket based server," so that would not mean any
further work for you other than putting it on the sqlite.org website
and in public domain.

On the other hand, I completely understand if your hands are tied with
either client-worker confidentiality, work-for-hire agreements, or
even the exigencies of trying to monetize your earlier work.

In open source, we have to depend on the largesse of others, who, just
like us, have exactly the same number of hours in a day, and pretty
much exactly the same kind of need for making ends meet. Someone has
to, somewhere, decide to give away something. At some point, Richard
did that with Sqlite, and we are all here as a result. Even here, many
people give generously of their time and knowledge -- most of us on
this list would get nowhere without Dan Kennedy, Dennis Cote, Igor
Tantednik, Roger Binns and the like.

The beauty of open source is that if others can't give away, we do
have the freedom of trying to meet our own needs. If we don't have the
technical ability to meet our own need, then we can trade our money or
other resources with someone who does have that ability. Barring all
of that, I would say, we are a noop.

Maybe someday someone will feel a burning need for a massively
parallelized, multi-core, full CPU utilizing SQLiteHeavy version of
this little database, and will have the time or means to make it
happen. If that happens, maybe that person will then put this
SQLiteHeavy into public domain, and maybe it will become highly
popular. We can only hope.


>
>
>
> On Tue, Feb 24, 2009 at 7:55 AM, P Kishor  wrote:
>> On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards  
>> wrote:
>> ..
>>>
>>> I have personally written a socket based server on top of the database
>>> and it works very well.  So I have actually scaled the database myself
>>> as I preached in this email.  Don't be a WIMP and map shares to share
>>> a database... write a socket based beauty like YOURS TRULY! hehe  For
>>> "most" solutions it is wonderful.  After years and building millions
>>> of lines of code keeping the business delivery requirements fulfilled
>>> in the most simple manner seems to be the best approach for me.  If
>>> you are the same, stay agnostic to all solutions available and run up
>>> a strategy that will give you the best of all worlds.  And yes, at
>>> times you have to write a little EXTRA code to get there! : - )
>>>
>>> Allan
>>>
>>> P.S.  If somebody does decide to build enterprise Sqlite, I would love
>>> to throw in my 2 cents on how to write the stuff on the outside to add
>>> in the big dog features.  I was working out tonight and while thinking
>>> about this I believe you could actually maintain the wonderfulness of
>>> the core engine, then scale the library from an outside piece of code.
>>>  Then you can keep integrity on both sides of the fence and not make
>>> sqlite into sqlitetoomuch.
>>>
>> ..
>>
>> Have you considered taking the "socket based server" that you wrote, I
>> am assuming, on top of SQLite, and donating it to public domain/open
>> source, putting it on the sqlite wiki, so others may benefit?
>>
>> Who knows, with a seed like that, someone may well build a
>> SQLiteEnterprise (as much an oxymoron as that might be).
>>
>> --
>> Puneet Kishor http://www.punkish.org/
>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>> Sent from: Madison WI United States.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> W Allan Edwards
> 214-289-2959
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[P Kishor]

On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards  wrote:
..
>
> I have personally written a socket based server on top of the database
> and it works very well.  So I have actually scaled the database myself
> as I preached in this email.  Don't be a WIMP and map shares to share
> a database... write a socket based beauty like YOURS TRULY! hehe  For
> "most" solutions it is wonderful.  After years and building millions
> of lines of code keeping the business delivery requirements fulfilled
> in the most simple manner seems to be the best approach for me.  If
> you are the same, stay agnostic to all solutions available and run up
> a strategy that will give you the best of all worlds.  And yes, at
> times you have to write a little EXTRA code to get there! : - )
>
> Allan
>
> P.S.  If somebody does decide to build enterprise Sqlite, I would love
> to throw in my 2 cents on how to write the stuff on the outside to add
> in the big dog features.  I was working out tonight and while thinking
> about this I believe you could actually maintain the wonderfulness of
> the core engine, then scale the library from an outside piece of code.
>  Then you can keep integrity on both sides of the fence and not make
> sqlite into sqlitetoomuch.
>
..

Have you considered taking the "socket based server" that you wrote, I
am assuming, on top of SQLite, and donating it to public domain/open
source, putting it on the sqlite wiki, so others may benefit?

Who knows, with a seed like that, someone may well build a
SQLiteEnterprise (as much an oxymoron as that might be).

-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Allan Edwards]

I wanted to throw out a few more points about being parallel and
scalable in terms of data storage?

What is the reason we want to be this way?  Well, for tons of users
and to mitigate risk across lots of machines.  Yes, out of the box the
value that Oracle provides is of a designation that satisfies these
ideals for large scale systems.  YET, as memory, hard drive space,
processors with more cores, etc.  come online, how could you get
Sqlite to be SQLiteIKickOraclesButt?

Well, I although I am pressed for time in development of a number of
systems I don't have time to try this out but what Sqlite would need
is a broker like server that could utilize it's existing features.
The broker server could provide the same features as an Oracle by
splitting inserts across multiple sqlites that hit multiple disks on
multiple systems.  The reality is that Sqlite has taken care of the
nasty details of organizing data into a binary file cross platform,
but you could literally take the database and write code above the
engine itself that leveraged it's capabilities in such a way that it
worked like a large scale database.

Back years ago I was the head architect on a C based system in
semiconductor.  We had a crazy requirement to bust out hundreds to
thousands of grey scale images from a camera source in a real time
manufacturing solution.  We had to store this data on the drive
quickly.  Before I took charge of the project we failed to get the
necessary performance needed from an RDBMS on our first project time
delivery limits.  On the second round we bench marked all system
components and figured out how we needed to architect a system from
every hardware dependency.  We ended up writing a buffer management
library that basically was optimized to pre allocate memory of
specific sizes for specific image sizes.  This pre allocation allowed
for the most optimal approach in moving data into memory.  We then had
writer threads that had thread synced access into that buffer and
eventually the images would get written to an RDBMS.

The reason I shot you the example above is to give you food for
thought on how you might still utilize the great power of Sqlite if
you don't need the massive cost or overhead of an Oracle.  Sqlite is a
beautiful thing due to it's simplicity.  It is the most powerful
database management solution on the planet for it's size (this is
TOTALLY OBJECTIVE).  Installing Oracle on a computer, being force fed
crappy performing and unrobust java (the enterprise Oracle manager
crashes on me with HUGE stack traces) too butt with Oracle makes for
one nasty user experience.  Yet with sexy Sqlite you can "xcopy"
install that puppy and go to town.  So in your thought on your desired
solutions, IT MIGHT be good to think of what Sqlite offers and
consider writing a separate library that could fulfill your extrea
RDBMS requirements so you can take advantage of the sweetness this
little data storage gym offers.

I have personally written a socket based server on top of the database
and it works very well.  So I have actually scaled the database myself
as I preached in this email.  Don't be a WIMP and map shares to share
a database... write a socket based beauty like YOURS TRULY! hehe  For
"most" solutions it is wonderful.  After years and building millions
of lines of code keeping the business delivery requirements fulfilled
in the most simple manner seems to be the best approach for me.  If
you are the same, stay agnostic to all solutions available and run up
a strategy that will give you the best of all worlds.  And yes, at
times you have to write a little EXTRA code to get there! : - )

Allan

P.S.  If somebody does decide to build enterprise Sqlite, I would love
to throw in my 2 cents on how to write the stuff on the outside to add
in the big dog features.  I was working out tonight and while thinking
about this I believe you could actually maintain the wonderfulness of
the core engine, then scale the library from an outside piece of code.
 Then you can keep integrity on both sides of the fence and not make
sqlite into sqlitetoomuch.

On Mon, Feb 23, 2009 at 9:49 PM,   wrote:
> P Kishor,
>
>>> Most computers these days are multi-core. ..
>
>> One of things easy to overlook is that SQLite is not a PC-exclusive
> software. About 10 million copies of SQLite run on iPhone. Who knows
> how many run on other handhelds, embedded platforms, Vxworks, the
> like. SQLite used to fit on a floppy and probably still does.
>
> Good point!
>
> I lost my perspective on SQLite's intended audience.
>
> Regards,
> Malcolm
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
W Allan Edwards
214-289-2959
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Allan Edwards]

Actually, Oracle boots pretty fast and once it is booted up, it is
SUPER fast on insertion.

I have used the following databases professionally for years

* MS SQL Server
* MS SQL Server Embedded
* MS Access
* Oracle (numerous versions)
* MySQL (multiple versions)
* Objectivity
* PostgreSQL
* Sqlite
* DB2

When you talk about performance comparisons your question really needs
to be squared up to what the database is used for.  Are you on a
server, workstation, or embedded?  How large in the database?  How are
the internal algos of the database engine lined up?  What is the
memory footprint?  How did you configure what is configurable in the
database?

The reality is that Sqlite is slower on 1 insert per transaction, but
if you start a transaction, insert a slew of records, then commit, the
database is very fast.

The major platforms that are typically server oriented like Oracle pre
allocate memory and file space so they can "cheat" for a while to be
optimized.  Oh wait, and index space is pre allocated.  Sqlite appears
to be optimized for single file access and as the docs on the website
say, no server full of memory to buffer and give the appearance that
it is faster than it really is on inserts in terms of hard drive write
speed.  The reality is I BET and I would love to know myself Sqlite is
just as fast on inserts to the same hard drive as oracle.

People that are political in slant toward a specific thing tend to
like to make a blanket statement like Oracle is faster than Sqlite but
not inform everyone else as to a specific comparison which makes the
statement full of it! : - )  Are you some kind of political Oracle
covering biggot?  hehehe  Or, do you want another chance to post
something that states you are trying to find the best data storage
solution to deliver sincere value to your client in terms of a
database choice?  Sometimes that choice is Sqlite, but in some cases
it is Oracle instead.

Sqlite is a very Hipp database.

Allan

On Mon, Feb 23, 2009 at 3:28 PM, D. Richard Hipp  wrote:
>
> On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote:
>
>> Dr. Hipp,
>>
>> When you say "SQLite is way faster than Oracle in a single-user
>> applications" do you mean that SQLite can be faster than Oracle even
>> when Oracle's parallel processing features are being used? For example
>> Oracle's support for parallelization can speed up table loading from
>> an
>> external data source, certain SQL selects, and certain indexing
>> operations.
>
> I don't run Oracle and have no way of verifying the following.  But I
> conjecture that from a cold start, you and launch an application that
> uses SQLite, have it do a dozen or so queries, print out the answer,
> and shut down, all before the Oracle server has even booted up to the
> point where it will accept connections.  Correct me if I am wrong.
>
> Perhaps Oracle will run a gazillion more transactions per second,
> given enough memory and CPUs, and once you get it up and going.  I
> have no way of knowing.  But then again, that isn't really the point
> of SQLite.
>
>
>>
>>
>> Are there any plans to enhance SQLite to support some of Oracle's
>> parallel processing or partitioning capabilities?
>
> Remember:  SQLite is not trying to replace Oracle.  SQLite is trying
> to replace fopen().
>
> For people who are using Oracle as a replacement for fopen() (as
> apparently Angela is) they will likely find that SQLite makes a far
> superior replacement.  Or to put it another way, people who are using
> Oracle for a single-user application (low concurrency) will likely
> find that SQLite works much better for them.  It has been my
> experience that old-time Oracle users are incredulous at this
> statement, until they actually see a live demonstration.  So I won't
> try to argue the point.  It is merely my observation.
>
> On the other hand, nobody things that SQLite is a suitable database
> when you have 1000 separate connections beating on the database all at
> once.
>
>
>
>>
>>
>> Thank you,
>> Malcolm
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
W Allan Edwards
214-289-2959
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[python]

P Kishor,

>> Most computers these days are multi-core. ..

> One of things easy to overlook is that SQLite is not a PC-exclusive
software. About 10 million copies of SQLite run on iPhone. Who knows
how many run on other handhelds, embedded platforms, Vxworks, the
like. SQLite used to fit on a floppy and probably still does.

Good point!

I lost my perspective on SQLite's intended audience.

Regards,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[P Kishor]

On Mon, Feb 23, 2009 at 9:34 PM,   wrote:
> Hi Billy,
>
>>> Are there any plans to enhance SQLite to support some of Oracle's
>>> parallel processing or partitioning capabilities?
>
>> I realized that you're asking Richard, and not the peanut gallery, but
>> I figured I might as well ask out of curiosity: why do you want to
>> see these features in SQLite?
>
> Most computers these days are multi-core. ..


One of things easy to overlook is that SQLite is not a PC-exclusive
software. About 10 million copies of SQLite run on iPhone. Who knows
how many run on other handhelds, embedded platforms, Vxworks, the
like. SQLite used to fit on a floppy and probably still does.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[python]

Hi Billy,

>> Are there any plans to enhance SQLite to support some of Oracle's
>> parallel processing or partitioning capabilities?

> I realized that you're asking Richard, and not the peanut gallery, but 
> I figured I might as well ask out of curiosity: why do you want to 
> see these features in SQLite?

Most computers these days are multi-core. Oracle has done some excellent
work adding support for parallel processing of many database activities.
It would be great to see SQLite be able to exploit the extra processing
power of multiple cores. This is not a request for handling multiple
simultaneous transactions - it is a request to have single transactions
be processed across multiple cores.

Oracle also supports a rich mix of partitioning features. Partitioning
allows one to divide a table and/or index into logical subsets that
allow additional query optimizations. Partitioning is also useful for
quickly dropping a logical subset of data, eg. if you've partitioned
data by month, you can quickly drop your oldest month of data by
dropping its partition vs. performing a massive number of individual
deletes, followed by a vacuum. Finally, partitions can also support
parallelization tasks such as loading large data sets (each partition
can be loaded and optionally indexed independently of others) and for
building partial result sets for SQL selects (each partition can be
queried independently of other partitions).

Another interesting Oracle feature is compression. Oracle's compression
techniques not only compress data, but also speed up many types of
selects.

Thinking-out-loud: I wonder if some of Oracle's parallelization and
partitioning features could be emulated by creating a physical SQLite
database for each logical partition; loading large logical tables
quickly by using a separate process to load each 'partition specific'
SQLite database, and then creating a high-level code library to
translate a high-level SQL commands (insert, update, delete, select)
into multiple, 'partition specific' SQLite commands that get executed in
parallel. In the case of parallel selects, the intermediate results
would be cached to partition specific SQLite databases and then unioned
together by a master controlling process to create a logical cursor for
processing.

Is anyone using similar techniques with very large SQLite
tables/databases?

Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[python]

Dr. Hipp and others,

Thank you for your replies to my question.

Regards,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Ken]

I fully agree with DRH regarding SQLITE and single user performance. If you 
need to replace  fopen then sqlite is a really really great product. Even if 
you have some mild concurrency sqlite still does pretty darned good.

Oracle excels when you have many users that require concurrent database 
changes. 

Oracle performs row level locking and Multi-Versioning on data blocks to 
achieve concurrency.

Oracle can perform Parallel queries. But the best usage of parallel query is 
for full table scans where the entire table is read. And there are equally 
sized segments for scanning by the query slaves. Other wise parallel query 
probably won't help much. 



--- On Mon, 2/23/09, D. Richard Hipp <d...@hwaci.com> wrote:

> From: D. Richard Hipp <d...@hwaci.com>
> Subject: Re: [sqlite] SQLite vs. Oracle (parallelized)
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Monday, February 23, 2009, 3:28 PM
> On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote:
> 
> > Dr. Hipp,
> >
> > When you say "SQLite is way faster than Oracle in
> a single-user
> > applications" do you mean that SQLite can be
> faster than Oracle even
> > when Oracle's parallel processing features are
> being used? For example
> > Oracle's support for parallelization can speed up
> table loading from  
> > an
> > external data source, certain SQL selects, and certain
> indexing
> > operations.
> 
> I don't run Oracle and have no way of verifying the
> following.  But I  
> conjecture that from a cold start, you and launch an
> application that  
> uses SQLite, have it do a dozen or so queries, print out
> the answer,  
> and shut down, all before the Oracle server has even booted
> up to the  
> point where it will accept connections.  Correct me if I am
> wrong.
> 
> Perhaps Oracle will run a gazillion more transactions per
> second,  
> given enough memory and CPUs, and once you get it up and
> going.  I  
> have no way of knowing.  But then again, that isn't
> really the point  
> of SQLite.
> 
> 
> >
> >
> > Are there any plans to enhance SQLite to support some
> of Oracle's
> > parallel processing or partitioning capabilities?
> 
> Remember:  SQLite is not trying to replace Oracle.  SQLite
> is trying  
> to replace fopen().
> 
> For people who are using Oracle as a replacement for
> fopen() (as  
> apparently Angela is) they will likely find that SQLite
> makes a far  
> superior replacement.  Or to put it another way, people who
> are using  
> Oracle for a single-user application (low concurrency) will
> likely  
> find that SQLite works much better for them.  It has been
> my  
> experience that old-time Oracle users are incredulous at
> this  
> statement, until they actually see a live demonstration. 
> So I won't  
> try to argue the point.  It is merely my observation.
> 
> On the other hand, nobody things that SQLite is a suitable
> database  
> when you have 1000 separate connections beating on the
> database all at  
> once.
> 
> 
> 
> >
> >
> > Thank you,
> > Malcolm
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> >
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[D. Richard Hipp]

On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote:

> Dr. Hipp,
>
> When you say "SQLite is way faster than Oracle in a single-user
> applications" do you mean that SQLite can be faster than Oracle even
> when Oracle's parallel processing features are being used? For example
> Oracle's support for parallelization can speed up table loading from  
> an
> external data source, certain SQL selects, and certain indexing
> operations.

I don't run Oracle and have no way of verifying the following.  But I  
conjecture that from a cold start, you and launch an application that  
uses SQLite, have it do a dozen or so queries, print out the answer,  
and shut down, all before the Oracle server has even booted up to the  
point where it will accept connections.  Correct me if I am wrong.

Perhaps Oracle will run a gazillion more transactions per second,  
given enough memory and CPUs, and once you get it up and going.  I  
have no way of knowing.  But then again, that isn't really the point  
of SQLite.


>
>
> Are there any plans to enhance SQLite to support some of Oracle's
> parallel processing or partitioning capabilities?

Remember:  SQLite is not trying to replace Oracle.  SQLite is trying  
to replace fopen().

For people who are using Oracle as a replacement for fopen() (as  
apparently Angela is) they will likely find that SQLite makes a far  
superior replacement.  Or to put it another way, people who are using  
Oracle for a single-user application (low concurrency) will likely  
find that SQLite works much better for them.  It has been my  
experience that old-time Oracle users are incredulous at this  
statement, until they actually see a live demonstration.  So I won't  
try to argue the point.  It is merely my observation.

On the other hand, nobody things that SQLite is a suitable database  
when you have 1000 separate connections beating on the database all at  
once.



>
>
> Thank you,
> Malcolm
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Billy Gray]

>
> Are there any plans to enhance SQLite to support some of Oracle's
> parallel processing or partitioning capabilities?
>

Malcolm,

I realized that you're asking Richard, and not the peanut gallery, but I
figured I might as well ask out of curiosity: why do you want to see these
features in SQLite?

Cheers,
Billy

On Mon, Feb 23, 2009 at 3:54 PM,  wrote:

> Dr. Hipp,
>
> When you say "SQLite is way faster than Oracle in a single-user
> applications" do you mean that SQLite can be faster than Oracle even
> when Oracle's parallel processing features are being used? For example
> Oracle's support for parallelization can speed up table loading from an
> external data source, certain SQL selects, and certain indexing
> operations.
>
> Are there any plans to enhance SQLite to support some of Oracle's
> parallel processing or partitioning capabilities?
>
> Thank you,
> Malcolm
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite vs. Oracle (parallelized)

[python]

Dr. Hipp,

When you say "SQLite is way faster than Oracle in a single-user
applications" do you mean that SQLite can be faster than Oracle even
when Oracle's parallel processing features are being used? For example
Oracle's support for parallelization can speed up table loading from an
external data source, certain SQL selects, and certain indexing
operations.

Are there any plans to enhance SQLite to support some of Oracle's
parallel processing or partitioning capabilities?

Thank you,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users