RE: Stress-testing SQLite
Check out information from one user from about eight years ago (!) in building a kiosk project, comparing Valentina with MS Access. Of course, you probably wouldn't do this with Access today, but worth considering is that this is with major hardware constraints, the overhead of Director, and that since then most systems of Valentina are exponentially faster now and we've added a huge number of other improvements (64 bit version, etc). Seems really interesting. Is Valentina server able to run as a LiveCode server companion ? Is it way to install it in an on-rev account ? Right now, no, but its something that will come in time. Its something both we and Runtime need to implement. Went Access ever some thing else than a poor and unreliable way to store data ? I never used it in a production-state project... I liked to have to do with direct-to-disk flat-file-based MC/Rev db, SQLServer (a Sybase technology, as anyone should remember), Sybase ASE, PostgreSQL or even Oracle 8i to 11g. I never got pleasure and confidence to run MySQL but it seems i will get good time in testing Valentina, hopefully, in the near. Using Access for anything other than a simple desktop type db never would have occurred to me either, but a lot of folks will build custom front ends with its built in script or VB, or even try to share it on a network or server. In fact, a friend of mine in the federal government (USA) told me about several projects that cost millions of dollars in labor, but in fact were very simple VB + Access projects. I am sometimes shocked by some of the questions we get from developers who want to implement a structure that dramatically increases the chance of data corruption, often to shave a very few bucks off a project in license fees or shave off a few hours of work. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Le 1 nov. 2010 à 16:09, Lynn Fredricks a écrit : Check out information from one user from about eight years ago (!) in building a kiosk project, comparing Valentina with MS Access. Of course, you probably wouldn't do this with Access today, but worth considering is that this is with major hardware constraints, the overhead of Director, and that since then most systems of Valentina are exponentially faster now and we've added a huge number of other improvements (64 bit version, etc). Seems really interesting. Is Valentina server able to run as a LiveCode server companion ? Is it way to install it in an on-rev account ? Right now, no, but its something that will come in time. Its something both we and Runtime need to implement. Good to know. Thanks. I will seriously test it at that time to see if i can improve some method tasks (automation) in using it. Went Access ever some thing else than a poor and unreliable way to store data ? I never used it in a production-state project... I liked to have to do with direct-to-disk flat-file-based MC/Rev db, SQLServer (a Sybase technology, as anyone should remember), Sybase ASE, PostgreSQL or even Oracle 8i to 11g. I never got pleasure and confidence to run MySQL but it seems i will get good time in testing Valentina, hopefully, in the near. Using Access for anything other than a simple desktop type db never would have occurred to me either, but a lot of folks will build custom front ends with its built in script or VB, or even try to share it on a network or server. In fact, a friend of mine in the federal government (USA) told me about several projects that cost millions of dollars in labor, but in fact were very simple VB + Access projects. I am sometimes shocked by some of the questions we get from developers who want to implement a structure that dramatically increases the chance of data corruption, often to shave a very few bucks off a project in license fees or shave off a few hours of work. ;-) Best Regards, Pierre Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- Pierre Sahores mobile : (33) 6 03 95 77 70 www.wrds.com www.sahores-conseil.com ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- what means theoretical limits from C++ point of view
On 10/31/10 12:10 AM, Monte Goulding mo...@sweattechnologies.com wrote: Charts like this, especially on Wikipedia should be taken with a grain of salt. Feature comparisions (yes/no) I can appreciate, but when it comes to capacity and performance, that's a bit different. Indeed. With LiveCode, for example, fields can *theoretically* hold up to 4GB, but I pity the person who tries it. My point was that given your expected max db size of 5kB * 500 is 23GB this is only a very small fraction of the stated theoretical limits of SQLite. Hi Guys, Just in case I want explain here what means these limits. Image that some C/C++ library has class File_16, which has ulong mLength; Ulong is 4 bytes (32 bits) and can keep max 2^32-1 value. this is near to 4GB. In 80-90ths this was normal for most software. Later have come to gave 64 bit API of OS for file systems. And we have change ullong mLength // 2^64 a lots of Terrabytes This was first 64-bit revolution. Another example. Table and Field Count in the table. We have seat and think, how much can be fields, What type to choose. USHORT 64K fields max ULONG 4 billions ? ULLONG like stars in Universe ... :) It looks reasonable to choose USHORT. I have hear about Table with 1024 fields from one user ... And I was sure that was bad lazy design. So theoretical limit, comes first of all from this choice of C/C++ library developer, when he write such lime of code. This not means that in reality somebody have test db up to million of Terrabytes. Or up to 50,000 fields in Table. - Another example and hot stream last years -- 64 bit compatibility. As you know most software was 32 bit, and now step by step C/C++ developers improve things to be able use any memory pointer as 64-bit value. Main bonus that then software can use RAM installed on computer 2-4GB. This is second 64-bit revolution. We have port Valentina engine and Valentina Server and Valentina ADKs to 64 bit year ago. * Surprisingly, many people have jump to use 64 bit versions. * Surprisingly, we have to see real dbs of real users, when 64-bit server works better ... -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- millions records? Use Valentina DB
On 10/30/10 10:14 PM, stephen barncard stephenrevoluti...@barncard.com wrote: Yes, I've been waiting for Ruslan to chime in here. Valentina has been the *elephant in the room* in this discussion and I find it slightly odd that Richard (no newbie in the Rev world) hadn't considered this product for his project. Elephant is postgre logo :-) -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- testimonials
On 10/31/10 12:14 AM, Richard Gaskin ambassa...@fourthworld.com wrote: However, the TPC doesn't have the power to run benchmark tests on a database platform without the approval of the database vendor. In fact, with the exception of IBM, most major database vendors include in their license agreements a clause that forbids the publication of benchmark information without explicit permission. Here's the clause from the SQL Server End User License Agreement (EULA): One of reasons why last years we show mainly reaction of some Valentina users on testimonials page http://www.valentina-db.com/en/company/testimonials Here can be found comparisons of users after SqlLite, and other dbs also. -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Le 30 oct. 2010 à 23:41, Lynn Fredricks a écrit : http://www.informationweek.com/news/software/linux/showArticle .jhtml?articleID=201001901 If a test could be setup in benchmarking the same test database set to run as : - PHP+ Oracle 11g - PHP+PostgreSQL 8.2 - PHP+ Valentina - LiveCode server+Oracle 11g - LiveCode server+PostgreSQL 8.2 - LiveCode server+Valentina in using less expensive comparable hardware configs alike : iMac 27 I7 quad core 2.8 Ghz and an equivalent Desktop PC to test the respective performances of the app's servers+databases against Linux, OSX SL and Solaris 10, I'm not sure at all that PostgreSQL would be slower than Oracle 11g, on both the OpenSuse 11 and OSX SL platforms and it would be interesting to know how Valentina performs for its own against both PostgreSQL and Oracle (would it be faster, as it's presented to to be on the http://www.valentina-db.com/ site ?). Just a tough, Best, Pierre That's an interesting benchmark, I wish I had a couple of $60K to $75K server boxes handy so we could see how Valentina would do. We've always emphasized what can be done with modest hardware specs. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- Pierre Sahores mobile : (33) 6 03 95 77 70 www.wrds.com www.sahores-conseil.com ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
On 10/31/10 12:41 AM, Lynn Fredricks lfredri...@proactive-intl.com wrote: http://www.informationweek.com/news/software/linux/showArticle .jhtml?articleID=201001901 That's an interesting benchmark, I wish I had a couple of $60K to $75K server boxes handy so we could see how Valentina would do. We've always emphasized what can be done with modest hardware specs. :-) as I like say You want to get MAC or PC 100 times faster today, but not 10 years later? Use Valentina DB today. :-) Btw, THIRD revolution (or evolution :) which goes last times - is using of multi-core. We also have start do steps in this directions. For example, in the next 4.8 version of Valentina, sorting will be able to use N cores of your computers. Of course it is used for big selections 4K items. This gives x2-x6 speed up for sorting on regular desktops comparing to Valentina 4.7 -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
On Oct 30, 2010, at 1:55 PM, Richard Gaskin wrote: With LiveCode, for example, fields can *theoretically* hold up to 4GB, but I pity the person who tries it. There's often a vast difference between theoretical addressing limits and real-world use, hence my interest in finding actual use cases for SQLite. As Yogi Berra said, In theory, theory and practice are the same, but in practice they're different. -- Peter Peter M. Brigham pmb...@gmail.com http://home.comcast.net/~pmbrig ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
I'm not sure at all that PostgreSQL would be slower than Oracle 11g, on both the OpenSuse 11 and OSX SL platforms and it would be interesting to know how Valentina performs for its own against both PostgreSQL and Oracle (would it be faster, as it's presented to to be on the http://www.valentina-db.com/ site ?). A number of the testimonials on our site also include platform information, but not all - just what customers give us. I think your suggestion is very astute, and it also goes back to the value proposition of Valentina. A few of our users are using really mighty $60K boxes. Are your clients doing that? Or do they need high performance on more modest hardware? Check out information from one user from about eight years ago (!) in building a kiosk project, comparing Valentina with MS Access. Of course, you probably wouldn't do this with Access today, but worth considering is that this is with major hardware constraints, the overhead of Director, and that since then most systems of Valentina are exponentially faster now and we've added a huge number of other improvements (64 bit version, etc). I developed a kiosk project using Paradigma's Valentina database in Macromedia Director (not exactly renowned as a speedy environment), but on a P3/600 with 384 Mb RAM I imported and indexed 20 million records (about 1.2 Gb) inside two hours. I thought I'd do a benchmark with Access 2k - it crashed after 11 hours of importing (not even indexing). Valentina did 5-term OR searches in under 0.1 sec on the entire dataset, Access (using only 10% of the data) clocked in at about 13 sec. So Valentina was 1300 times faster!!! Does anyone else need convincing that Access is really not a good way to go? Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Hi Lynn, I'm not sure at all that PostgreSQL would be slower than Oracle 11g, on both the OpenSuse 11 and OSX SL platforms and it would be interesting to know how Valentina performs for its own against both PostgreSQL and Oracle (would it be faster, as it's presented to to be on the http://www.valentina-db.com/ site ?). A number of the testimonials on our site also include platform information, but not all - just what customers give us. I think your suggestion is very astute, and it also goes back to the value proposition of Valentina. A few of our users are using really mighty $60K boxes. Are your clients doing that? Or do they need high performance on more modest hardware? High performance on more modest hardware went always the way i recommended to most of my customers (EADS excluded, of course !). Check out information from one user from about eight years ago (!) in building a kiosk project, comparing Valentina with MS Access. Of course, you probably wouldn't do this with Access today, but worth considering is that this is with major hardware constraints, the overhead of Director, and that since then most systems of Valentina are exponentially faster now and we've added a huge number of other improvements (64 bit version, etc). Seems really interesting. Is Valentina server able to run as a LiveCode server companion ? Is it way to install it in an on-rev account ? I developed a kiosk project using Paradigma's Valentina database in Macromedia Director (not exactly renowned as a speedy environment), but on a P3/600 with 384 Mb RAM I imported and indexed 20 million records (about 1.2 Gb) inside two hours. I thought I'd do a benchmark with Access 2k - it crashed after 11 hours of importing (not even indexing). Valentina did 5-term OR searches in under 0.1 sec on the entire dataset, Access (using only 10% of the data) clocked in at about 13 sec. So Valentina was 1300 times faster!!! Does anyone else need convincing that Access is really not a good way to go? Went Access ever some thing else than a poor and unreliable way to store data ? I never used it in a production-state project... I liked to have to do with direct-to-disk flat-file-based MC/Rev db, SQLServer (a Sybase technology, as anyone should remember), Sybase ASE, PostgreSQL or even Oracle 8i to 11g. I never got pleasure and confidence to run MySQL but it seems i will get good time in testing Valentina, hopefully, in the near. Best Regards, Pierre Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- Pierre Sahores mobile : (33) 6 03 95 77 70 www.wrds.com www.sahores-conseil.com ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
many of the data stores for the major apple apps use sqlite. it's a petty robust single user data store. kee nethery On Oct 29, 2010, at 5:32 PM, Mark Stuart wrote: on Fri Oct 29 19:17:40 CDT 2010, Richard Gaskin wrote: Thanks in advance for sharing your experiences with large data sets if SQLite. Hi Richard, How many tables and how many columns per table (on average) are you talking about? That can make a big difference to the performance if there are JOINS involved. If not, then that's not so much a problem. Will the user always apply a WHERE filter to the data? What's the potential return record set count on a typical filter? I'd be happy to do some stress testing if you can give me some details. Regards, Mark Stuart ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution - I check email roughly 2 to 3 times per day. Kagi main office: +1 (510) 550-1336 ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- millions records? Use Valentina DB
On 10/30/10 3:17 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Hi Richard, I have a need coming up for a data store that can robustly handle at least a million records, ideally up to five million, where each record may be as large as 5k. I don't need relationality, so for me SQLite is an option but only an option; I'm happy to consider other options as well. (Yes, it has to be SQLite rather than MySQL, because it needs to work embedded with a commercial application). Valentina DB is faster 100 times of SqlLite, mySQL And can be perfectly embedded into commercial application because it is royalty free. Have any of you done stress testing on SQLite to that degree? I've tried finding even anecdotal data on the web for SQLite limits, and while I can find citations of theoretical limits I haven't come across real-world usage stories of data sets that large. Should I be confident in SQLite as a storage solution for that? Should I be scared? SCARED :) Thanks in advance for sharing your experiences with large data sets if SQLite. You need Valentina DB. Okay you need 5K for each record. How many fields? Let me remind that Valentina has columnar format. This is huge advantage. Also Valentina can give you not only SQL way but NON-SQL way, Which can be additionally 10-20 times faster! -- I can tell you store, that Valentina was tested for AOL Europe by their dev team. Against Berkly, mySQL, postgre, and other dbs. SqlLite even was not in game of course. Task was so simple. Table has 2 fields {URL, PictureBannerAd } So when somebody ask for a WEB page, it needs find banner to be shown. As they told, e.g. Berkly have give 100 faults per time (min our hour I not remember now). Other dbs also. Fault means that banner was not found by DB in time less of timeout. Valentina have give them zero faults. Let me underline this very important feature NON-SQL-ness of Valentina. As well as very powerful SQL. Today is very modern stream talk about how SQL DBs are bad, and how cool are NON-SQL with Key-Value. Guys, be happy, Valentina is perfect for both tasks. :-) If talk about details, in V4REV API (and most others Valentina ADKs) you can use not SQL way to do searches and sortngs using VField_FindValue() VField_FindRange() VField_FindLike() And other similar search methods. They are really FASTEST POSIBLE way. -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- degradation on big selections
On 10/30/10 4:10 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Hi Richard, Hi Mark, Mark Stuart wrote: on Fri Oct 29 19:17:40 CDT 2010, Richard Gaskin wrote: Thanks in advance for sharing your experiences with large data sets if SQLite. Hi Richard, How many tables and how many columns per table (on average) are you talking about? Probably just a single table, with about 20 columns. Okay this answers my question. So now I can tell you Richard, ONLY because of columnar format, Valentina DB can do some operations 20 times faster of any ROW-based DB. And if multiply this to other features of Valentina you can become With Valentina 20 *4 * 2 = 100-200 times faster. If not, then that's not so much a problem. Good to hear. Very not true :) When you talk about speed and time, all is relative. You need talk about ABSOLUTE numbers instead. For one person/project/app 10 sec for search is very fast, For another this is incredibly slow. Richard, when you say, I NEED SPEED, you must say: I want query in 100 sec or in 0.01 sec Only having this info, people can advice you. What's the potential return record set count on a typical filter? It'll vary, and in my own tests that seems to be the only bottleneck with SQLit; queries that return little data are ultra speedy, but once we get into large amounts of return data I see the hit. Exactly Richard. This is named degradation of DB on grow of A) records number in table(s) B) records number in the RESULT Yet from 1996 year, when I did my first benches of Valentina against FileMaker, 4D, Access, mySQL, ... And this very first thing I have found also. Most dbs if not all, have powerful degradation when recs number grow. Also exists some special N related to RAM of computer. Below this N dbs go yet more or less nice. After N degradation can go by jump x10 worse. With Valentina this N was much higher in the same RAM, because of much more compact db format for data. And after N it was very good yet. For example, if for most dbs difference in time between 50 and 500,000 recs in result is huge, for Valentina it is almost flat. I'd be happy to do some stress testing if you can give me some details. Thanks. Don't knock yourself out; I'll be continuing with my own tests here, but if this sort of thing passes for entertainment in your house then of course I'd be grateful for any details you turn up. -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re; Stress-testing SQLite
Sivakatirswami wrote: I was using and old example SQL stack... small data returns were fast, but a lot of data (select * from table whatever.. i.e. everything) from a PostGreSQL database adding it to a display field. It took forever... Then I remembered Dont' Do That! When I got all the data in a variable and just posted to the field once, it was like 20 times faster. It seems in my initial tests that the time it takes to get data through the externals interface is much long than what it takes to move data around within Rev natively. Is this a known limitation of externals, or is this just a case of false attribution of the root cause on my part? -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
many of the data stores for the major apple apps use sqlite. it's a petty robust single user data store. Of course I don't need to expand on what Ruslan will say about Valentina, but I will say that there's a right tool for each job, and others that sort of work but aren't optimal. I can use a brick or the butt of a screwdriver to drive a nail into a piece of wood. There are reasons why, for example, AOL Europe, Nikon Corporation and others that may not be named chose Valentina over something like SQLite for their specific projects. I can say for certain that developers do not pick Valentina because it is free or in public domain :-) SQLite is a functional, single user database, but the database market is filled with alternatives. Its worth looking at Ruslan's emails recently asking about changing schema to see where even his expectations for SQLite were different. SQLite has its place in development, and there are good reasons to use it. But like evaluating any infrastructure technology, you have to consider what your long term plans are and what your clients are likely to ask for later. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
32TB db limit according to this: http://en.wikipedia.org/wiki/Comparison_of_relational_database _management_systems#Limits Charts like this, especially on Wikipedia should be taken with a grain of salt. Feature comparisions (yes/no) I can appreciate, but when it comes to capacity and performance, that's a bit different. There are some database vendors that in their EULAs state you cannot publish performance data, and also have sued some who have done so. My advice if you are considering entering a software product market that is as mature as the database market - save yourself the hair loss and choose another ;-) In all fairness to all the databases in that list (Valentina can act like a relational database, but it isnt there because it's a columnar database), for many of them, the limits are theoretical and based on limitations of how volumes work in file systems and operating systems. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Lynn Fredricks wrote: 32TB db limit according to this: http://en.wikipedia.org/wiki/Comparison_of_relational_database _management_systems#Limits Charts like this, especially on Wikipedia should be taken with a grain of salt. Feature comparisions (yes/no) I can appreciate, but when it comes to capacity and performance, that's a bit different. Indeed. With LiveCode, for example, fields can *theoretically* hold up to 4GB, but I pity the person who tries it. There's often a vast difference between theoretical addressing limits and real-world use, hence my interest in finding actual use cases for SQLite. There are some database vendors that in their EULAs state you cannot publish performance data, and also have sued some who have done so. A curious limitation. Which ones? -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
There are some database vendors that in their EULAs state you cannot publish performance data, and also have sued some who have done so. A curious limitation. Which ones? A bad Halloween joke first: Q: Where do vampires learn to suck blood? A: Law school. Without naming names, Ones with a Really Awesome, Conniving Legal Environments have been known to include such things. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite -- millions records? Use Valentina DB
Yes, I've been waiting for Ruslan to chime in here. Valentina has been the *elephant in the room* in this discussion and I find it slightly odd that Richard (no newbie in the Rev world) hadn't considered this product for his project. If I were starting a new db project right now and wasn't forced into mySQL by the client, I would take a serious look at the Valentina ADK. Right now they are offering the beta of Valentina Studio Pro for free ( and there's a free Valentina Linux server for non-commercial use - Richmond?) geesh, I just talked myself into finally trying this product myself. I don't see any other db company bending over backward to serve Rev/Livecode users. And we even have Ruslan on the list here On 29 October 2010 23:28, Ruslan Zasukhin ruslan_zasuk...@valentina-db.comwrote: On 10/30/10 3:17 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Hi Richard, I have a need coming up for a data store that can robustly handle at least a million records, ideally up to five million, where each record may be as large as 5k. I don't need relationality, so for me SQLite is an option but only an option; I'm happy to consider other options as well. (Yes, it has to be SQLite rather than MySQL, because it needs to work embedded with a commercial application). Valentina DB is faster 100 times of SqlLite, mySQL And can be perfectly embedded into commercial application because it is royalty free. Have any of you done stress testing on SQLite to that degree? I've tried finding even anecdotal data on the web for SQLite limits, and while I can find citations of theoretical limits I haven't come across real-world usage stories of data sets that large. Should I be confident in SQLite as a storage solution for that? Should I be scared? SCARED :) Thanks in advance for sharing your experiences with large data sets if SQLite. You need Valentina DB. Okay you need 5K for each record. How many fields? Let me remind that Valentina has columnar format. This is huge advantage. Also Valentina can give you not only SQL way but NON-SQL way, Which can be additionally 10-20 times faster! -- I can tell you store, that Valentina was tested for AOL Europe by their dev team. Against Berkly, mySQL, postgre, and other dbs. SqlLite even was not in game of course. Task was so simple. Table has 2 fields {URL, PictureBannerAd } So when somebody ask for a WEB page, it needs find banner to be shown. As they told, e.g. Berkly have give 100 faults per time (min our hour I not remember now). Other dbs also. Fault means that banner was not found by DB in time less of timeout. Valentina have give them zero faults. Let me underline this very important feature NON-SQL-ness of Valentina. As well as very powerful SQL. Today is very modern stream talk about how SQL DBs are bad, and how cool are NON-SQL with Key-Value. Guys, be happy, Valentina is perfect for both tasks. :-) If talk about details, in V4REV API (and most others Valentina ADKs) you can use not SQL way to do searches and sortngs using VField_FindValue() VField_FindRange() VField_FindLike() And other similar search methods. They are really FASTEST POSIBLE way. -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- Stephen Barncard San Francisco Ca. USA more about sqb http://www.google.com/profiles/sbarncar ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Lynn- Saturday, October 30, 2010, 11:15:48 AM, you wrote: Without naming names, Ones with a Really Awesome, Conniving Legal Environments have been known to include such things. Sounds a bit of an urban legend. I just checked my license (granted it's only version 8.0.5, but...) and there's nothing like that. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
Without naming names, Ones with a Really Awesome, Conniving Legal Environments have been known to include such things. Sounds a bit of an urban legend. I just checked my license (granted it's only version 8.0.5, but...) and there's nothing like that. It was big news a few years ago when they sued someone for posting benchmarks (I believe comparing with DB2 and a few others). Im sure you can find article bits if you search. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Re; Stress-testing SQLite
Could you post some of your results here Richard. Sent from my iPad On 31/10/2010, at 2:23 AM, Richard Gaskin ambassa...@fourthworld.com wrote: It seems in my initial tests that the time it takes to get data through the externals interface is much long than what it takes to move data around within Rev natively. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
On 31/10/2010, at 4:55 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Lynn Fredricks wrote: 32TB db limit according to this: http://en.wikipedia.org/wiki/Comparison_of_relational_database _management_systems#Limits Charts like this, especially on Wikipedia should be taken with a grain of salt. Feature comparisions (yes/no) I can appreciate, but when it comes to capacity and performance, that's a bit different. Indeed. With LiveCode, for example, fields can *theoretically* hold up to 4GB, but I pity the person who tries it. My point was that given your expected max db size of 5kB * 500 is 23GB this is only a very small fraction of the stated theoretical limits of SQLite. Cheers Monte___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Interesting find: The Truth About the TPC ... For example, one reader asked, Why does the TPC organization only test commercially licensed operating systems and databases? My presumptions would lead me to think that a non-profit based organization would be benchmarking anything they could get their hands on. An example being, why don't they test postreqsql or mysql on a Linux platform? ... However, the TPC doesn't have the power to run benchmark tests on a database platform without the approval of the database vendor. In fact, with the exception of IBM, most major database vendors include in their license agreements a clause that forbids the publication of benchmark information without explicit permission. Here's the clause from the SQL Server End User License Agreement (EULA): e. Benchmark Testing. You may not disclose the results of any benchmark test of either the Server Software or Client Software to any third party without Microsoft's prior written approval. Oracle, Sybase, and Informix each have a similar clause. These clauses are generically referred to as DeWitt clauses. David DeWitt was one of the founders of the Wisconsin Benchmarks, which were first published in the mid-1980s. At that time, the Wisconsin Benchmarks published less-than-favorable scores for an Oracle database, and Oracle wasn't happy with the negative publicity. Oracle added a clause to its license agreement forbidding unauthorized benchmarking, and most other vendors followed suit. ... http://www.sqlmag.com/article/benchmarks/the-truth-about-the-tpc.aspx -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Richard- http://www.informationweek.com/news/software/linux/showArticle.jhtml?articleID=201001901 -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
Indeed. With LiveCode, for example, fields can *theoretically* hold up to 4GB, but I pity the person who tries it. My point was that given your expected max db size of 5kB * 500 is 23GB this is only a very small fraction of the stated theoretical limits of SQLite. I have a feeling that the lists on wikipedia, since they come from difference sources, are a mix of theoretical limits and also limits that have been tested against differing criteria, but you can't really tell which is which. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
Oracle, Sybase, and Informix each have a similar clause. These clauses are generically referred to as DeWitt clauses. David DeWitt was one of the founders of the Wisconsin Benchmarks, which were first published in the mid-1980s. At that time, the Wisconsin Benchmarks published less-than-favorable scores for an Oracle database, and Oracle wasn't happy with the negative publicity. Oracle added a clause to its license agreement forbidding unauthorized benchmarking, and most other vendors followed suit. ... http://www.sqlmag.com/article/benchmarks/the-truth-about-the- tpc.aspx Indeed, very interesting - this came onto my radar when it was reported that one of the companies sued someone for doing a comparision of the big iron databases. Oracle, IBM and MS are big money players with massive legal departments and super huge budgets for their products. You'd think they'd simply kill off smaller companies, leaving only themselves and open source databases. But the only ones really killed off are those that do not have any really differentiating value. For us, I think half of the picture is performance, the other half is platform and extended value support. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite -- millions records? Use Valentina DB
Right now they are offering the beta of Valentina Studio Pro for free ( and there's a free Valentina Linux server for non-commercial use - Richmond?) geesh, I just talked myself into finally trying this product myself. I don't see any other db company bending over backward to serve Rev/Livecode users. And we even have Ruslan on the list here Thanks for pointing that out, Stephen. Most DB companies don't show that much interest in working with IDE vendors (except for themselves when they also sell and IDE product). We've been very open about working with other vendors. Runtime has always been really great about partnering. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Stress-testing SQLite
http://www.informationweek.com/news/software/linux/showArticle .jhtml?articleID=201001901 That's an interesting benchmark, I wish I had a couple of $60K to $75K server boxes handy so we could see how Valentina would do. We've always emphasized what can be done with modest hardware specs. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
Mark Stuart wrote: on Fri Oct 29 19:17:40 CDT 2010, Richard Gaskin wrote: Thanks in advance for sharing your experiences with large data sets if SQLite. Hi Richard, How many tables and how many columns per table (on average) are you talking about? Probably just a single table, with about 20 columns. That can make a big difference to the performance if there are JOINS involved. None - all flat. I'd even considered rolling my own data storage for this one, but the indexing is more work that I'd care to do if I can use an off-the-shelf solution. If not, then that's not so much a problem. Good to hear. Will the user always apply a WHERE filter to the data? For the most part, yes. I'll have about three or maybe four indexes, and most of the time the searches will be using those. I may have the odd case of a substring search, but the performance hit is anticipated. What's the potential return record set count on a typical filter? It'll vary, and in my own tests that seems to be the only bottleneck with SQLit; queries that return little data are ultra speedy, but once we get into large amounts of return data I see the hit. I'd be happy to do some stress testing if you can give me some details. Thanks. Don't knock yourself out; I'll be continuing with my own tests here, but if this sort of thing passes for entertainment in your house then of course I'd be grateful for any details you turn up. -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
On 10/29/10 3:10 PM, Richard Gaskin wrote: It'll vary, and in my own tests that seems to be the only bottleneck with SQLit; queries that return little data are ultra speedy, but once we get into large amounts of return data I see the hit. Just a reminder, which you probably don't need at all, but I had this same experience with PostGreSQL recently, but probably for different reasons: but FWIW... I was using and old example SQL stack... small data returns were fast, but a lot of data (select * from table whatever.. i.e. everything) from a PostGreSQL database adding it to a display field. It took forever... Then I remembered Dont' Do That! When I got all the data in a variable and just posted to the field once, it was like 20 times faster. skts ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Stress-testing SQLite
32TB db limit according to this: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Limits Maybe use limit and offset to page through query results though. Cheers Monte Sent from my iPad On 30/10/2010, at 11:17 AM, Richard Gaskin ambassa...@fourthworld.com wrote: I have a need coming up for a data store that can robustly handle at least a million records, ideally up to five million, where each record may be as large as 5k. I don't need relationality, so for me SQLite is an option but only an option; I'm happy to consider other options as well. (Yes, it has to be SQLite rather than MySQL, because it needs to work embedded with a commercial application). Have any of you done stress testing on SQLite to that degree? I've tried finding even anecdotal data on the web for SQLite limits, and while I can find citations of theoretical limits I haven't come across real-world usage stories of data sets that large. Should I be confident in SQLite as a storage solution for that? Should I be scared? Thanks in advance for sharing your experiences with large data sets if SQLite. -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com LiveCode Journal blog: http://LiveCodejournal.com/blog.irv ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution