Re: [sqlite] SQLite vs. Oracle (parallelized)
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)
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)
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)
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)
Chris Wedgwood c...@f00f.org 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)
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)
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)
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)
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)
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 punk.k...@gmail.com wrote: On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards wallanedwa...@gmail.com 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)
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 d...@hwaci.com 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)
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, pyt...@bdurham.com 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)
On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards wallanedwa...@gmail.com 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)
On Tue, Feb 24, 2009 at 8:19 AM, Allan Edwards wallanedwa...@gmail.com 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 punk.k...@gmail.com wrote: On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards wallanedwa...@gmail.com 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)
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)
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
[sqlite] SQLite vs. Oracle (parallelized)
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
Re: [sqlite] SQLite vs. Oracle (parallelized)
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, 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. 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
Re: [sqlite] SQLite vs. Oracle (parallelized)
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)
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)
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)
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)
On Mon, Feb 23, 2009 at 9:34 PM, pyt...@bdurham.com 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)
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