I thought users of Unidata SQL / ODBC would be interested in understanding why the engine appears to be slow.
The engine works well if only one table is being access. However when a table join is attempted things slow down considerably. This can be explained by a single sentence contained in a "tip" in the Unidata manual: "Indexes are not used in queries that involve a join of two or more sql tables or views." As every database /application designer will know, if you wish to link two tables together you need to use an index for speed. Not using the indexes ensures that every join is slow (as you have to start scanning the table for records). For example if I wish to retrieve a customer from the customer file and it's associated orders, I would use a hash index on the customer file to retrieve the customer record and a btree on the orders file for the customer field to retrieve the order records. This is also scaleable. E.g. if I add lots more customers or orders the retrieval time will be the same. However with the Unidata SQL engine this is not the case. As the indexes on the file are ignored the join takes longer and longer depending on the number of records in each file. This is only an issue for Unidata however. The Universe engine has been better designed: "UniVerse SQL processes standard table joins, also known as inner joins, in one of the following ways: ? If one of the columns is a primary key and you use an equi-join (=), UniVerse SQL retrieves the matching row directly, much like using the TRANS function in an I-descriptor. However, it is more efficient because there is no BASIC code to execute. ? If neither of the columns is a primary key, UniVerse uses a secondary index to join the tables. ? If there is no index, UniVerse SQL tries to use a sort-merge-join. ? All other joins are processed using a Cartesian product. For example, for each row in the first table, the entire second table is scanned for matching rows. This is a slow process with large tables." Q. If you are using Unidata's SQL engine, would you like it to be improved to work like Universe ? James Hogan Sungard ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
