mohan@ihlpf.ATT.COM (Mohan Palat) (03/07/87)
>I'd like information on Ingres. I know that it uses something called QUEL >instead of SQL; this may be an advantage (cf. ISQL above). What kind of C >interface does it have, if any? How does its speed compare to UNIFY or >Informix (either 3.30 or SQL), or to Progress? > > ++Brandon (Resident Elf @ ncoast.UUCP) Ingres also has an SQL interface. Therefore, you have the option of using either SQL or QUEL in your applications. Performance tests have shown that Informix performs better than Ingres for small to medium sized databases. However, Ingres out-performs Informix for applications with multiple users and large databases. Overall, Ingres (5.0) is said to perform much better than Informix or Oracle. Mohan Palat AT&T Technologies, Inc.
authorplaceholder@tiger.UUCP.UUCP (03/11/87)
Ingres 5.0 may indeed be faster, but does anybody care about concurrency? If so, the page/table level locking of Ingres will still disappoint you on the "speed" of multi-user operations requiring snap-shots of large tables.
pavlov@hscfvax.UUCP (03/13/87)
In article <143900001@tiger.UUCP>, authorplaceholder@tiger.UUCP.UUCP writes: > > Ingres 5.0 may indeed be faster, but does anybody care about concurrency? > If so, the page/table level locking of Ingres will still disappoint you > on the "speed" of multi-user operations requiring snap-shots of large > tables. Do you have any examples of executions/timings (particularly in comparison to other dbms's) that you can pass on to us ? I would be interested in seeing them. thanks, greg pavlov, fstrf, amherst, ny
allbery@ncoast.UUCP (03/15/87)
As quoted from <143900001@tiger.UUCP> by authorplaceholder@tiger.UUCP.UUCP: +--------------- | Ingres 5.0 may indeed be faster, but does anybody care about concurrency? | | If so, the page/table level locking of Ingres will still disappoint you | on the "speed" of multi-user operations requiring snap-shots of large | tables. +--------------- Informix 3.30 only locks single records (no table lock!); Informix-SQL only locks records during UPDATE SQL statements, and the only shared lock is at the table level. I see no improvement here. ++Brandon -- t'E Ir. <<Have you any idea what it takes to be an elf in this crazy world?>> ____ ______________ / \ / __ __ __ \ Brandon S. Allbery <backbone>!ncoast!allbery ___ | /__> / \ / \ aXcess Co., Consulting ncoast!allbery@Case.CSNET / \ | | `--, `--, 6615 Center St. #A1-105 (...@relay.CS.NET) | | \__/ \__/ \__/ Mentor, OH 44060-4101 \____/ \______________/ +1 216 974 9210
authorplaceholder@tiger.UUCP.UUCP (03/23/87)
And now for additional cannon-fodder intended to incite thought (if not all-out riots)... When referring to the concurrency capabilities (or lack thereof) of the Ingres 5.0 vs Informix 3.0 (or SQL) vs Oracle 5.0.20 (all on unix hosts of course), my argument is based on what happens to the second user's process whilst the first user has locked a table (or row of a table). A hypothetical situation: User A is in the process of doing a select from a table (a rather large one 300,000+ rows). The select includes an order by clause and even on a single user system, the query takes 10 to 15 minutes. (yes, that's a while, but it is a join or some such thing creating a large aggregate report). User B wants to update one or two rows of the same table and submits his update request just a minute or two after User A begins his select. What happens to User B??? Ingres: Locking is page level promoted to table level after a tunable percentage of pages are locked UNLESS the select requested minlocks in his QUEL range statement (analagous SQL syntax??). Problem: if minlocks were not requested, the select has LOCKED the table to updates. The poor data entry operator attempting to change a patients phone number must sit and wait for her screen to come back 15 minutes later (boy is that some fast database manager :-) ). If minlocks were requested (select will not lock the table (or pages)), the update process completes immediately; however, the updates may affect the results of the select already begun. Since updates are really a delete and an append, the updated row can and often does appear in the report twice. Minor problem you say... not when the reports are Work In Progress reports for high volume manufacturing lines. Neither solution is satisfactory. Informix: Yes last responder, Informix only locks rows... except in the midst of a transaction! After begin transaction and one update, the table is locked until commit or rollback completes. Soooo, without transactions, the updates are once again visible to the select, and with transactions the table lock cannot be granted until the select completes. Why transactions?? how about transferring cash from one bank account number to another while a balance report is being generated. Does he get credit for both accounts or neither account? Yes, odds are against error, but odds go up as the number of concurrent users goes up. Plus, if transactions are in progress, the select will not even begin until the locks are released. Oracle: Implements locking of individual rows with table locks in the middle of transactions (similar to Informix). However, there is one MAJOR difference... Selects of the table are never blocked. Once a select has begun, it is assured a snapshot of the table(s) as they exist at that moment. Plus, updates (inserts and delete too) can continue to take place. A "before image" of the table is maintained until the select completes. Soooo, selects are assured accurate snapshots AND other update users can continue to work simultaneously; kinda like the best of both worlds. As for performance of the products, Informix is quicker on simple queries on small to medium tables (less than 50,000 rows), but try a 3 table join or a nested select and you can measure Informix performance in hours. Oracle performance is near linear on table size and complex features work too. Bottom line: Oracle NEVER blocks select processes (unless you explicitly ask it too) and ALWAYS presents consistent snapshots. Yes, there are always work-arounds, but I want my programmers writing constructive code, not constantly working around the "Features" of my database manager. I only wish some benchmarks would concern themselves with REAL issues, not just transaction rates! Jeff McReynolds (otherwise known as authorplaceholder, aren't gateways nice) AT&T Network Systems (see, I don't even work for Oracle) Oklahoma City, OK ...occrsh!tiger!jlm
garyp@cognos.UUCP (Gary Puckering) (03/27/87)
In article <143900002@tiger.UUCP> authorplaceholder@tiger.UUCP.UUCP writes: > >When referring to the concurrency capabilities (or lack thereof) of the >Ingres 5.0 vs Informix 3.0 (or SQL) vs Oracle 5.0.20 (all on unix hosts >of course), my argument is based on what happens to the second user's >process whilst the first user has locked a table (or row of a table). > > ... > >Oracle NEVER blocks select processes (unless you explicitly ask it too) and >ALWAYS presents consistent snapshots. Yes, there are always work-arounds, >but I want my programmers writing constructive code, not constantly working >around the "Features" of my database manager. I only wish some benchmarks >would concern themselves with REAL issues, not just transaction rates! Nicely put. The approach used by Oracle is also used by DB2, SQL/DS, and Rdb/VMS (although actual implementation details vary). The scenario you describe points out that their are two types of transactions: read_only and read_write. A transaction involving only queries (i.e. only SELECT's) can be read_only. This enables the database manager to perform a Versioned Read on the tables involved. A versioned read involves checking the snapshot file to ensure that the record returned is current as at the start of the transaction (i.e. hide any updates that might have occurred because of concurrent transactions which had not committed before your transaction began). This means that all updates must be logged in the snapshot file, sometimes called a write-ahead log (since a write-ahead protocol is usually used). There is, of course, a penalty you pay for this. What you get, though, is degree 3 consistency (i.e. reproducable reads and no phantoms) and high concurrency for readers. Concurrent read_write transactions may still be blocked, if they are trying to update a record which your transaction has updated (but not committed). Some relational systems provide Transient Reads. This is the ability to read records *without* going through the snapshot file. For some situations, this is acceptable because read reproducability is not required and because a consistent view of the data is not required either. In other words, there are cases when seeing an uncommitted update is not all that serious. For example, many statistical queries are in this category. An objection I have to many relational systems is the lack of choice they provide in transaction control. I can understand why degree 3 consistency is the default, but I can't understand why some systems don't allow you the option of degree 2 or even degree 1. Some systems don't even let you specify a wait time for locks -- your transaction just dies if it has to wait longer than the system-defined wait time, or else it waits forever. Again, lack of choice. While on the subject of transactions, has anyone encountered problems with the fact that most relational database managers cannot retain your cursor position once you've committed the transaction? If so, in what circumstances have you encountered the problem and how did you get around it? -- Gary Puckering 3755 Riverside Dr. Cognos Incorporated Ottawa, Ontario decvax!utzoo!dciem! (613) 738-1440 CANADA K1G 3N3 nrcaer!cognos!garyp