SQL Optimisation, Unique Fields and Indexes

Many applications, particularly Microsoft Access, want to know about unique keys that have 'indexes' (access paths) so they can re-fetch a row using a key and be assured they get the same record efficiently. The key can be a set of more than one field and the index is on the set. DataEase 4.53 cannot have an index on more than one field (that comes with compound indices in 5.x), and so the strict requirements of Access / the ODBC specification cannot be complied with. Providing an application with the set of unique fields ensures that the application can get the record it wants, but unless that set includes an indexed field, the whole table will have to be read. The LinkEase ODBC driver gives an application the set of fields which are unique or indexed. LinkEase will NOT give an application a key set of fields if none of them are unique. When an application wants a record, it should send the driver a value for each field in the set. LinkEase uses the index on the first indexed field to search the table for the given value in that field, and then selects the one record which matches on the unique field values from the many that may match the indexed field value. This is the nearest to the ideal required by Access and ODBC. If there are several indexed fields, LinkEase may not use the most efficient one - LinkEase uses the first one identified in a query (like DataEase?). If there are unique fields, but no indexed fields, LinkEase will still give an application the set of unique fields, but there is no way of efficiently finding any record that the application might want - LinkEase will have to read the whole table! LinkEase also give the application a separate list of indexed fields.

When Microsoft Access pages through a table that has been attached via ODBC, it issues an SQL command with a WHERE clause that ORs the values in the set of unique fields for each of the records that it wants to display in the current window. If the set of unique fields does not include an indexed field, LinkEase will have to read every record of the attached table - every time the user tries to scroll through the table. If the table is large, the delays can be unacceptable. Making a field unique can seriously affect the database design, whereas adding an index to one of the existing unique fields in a DataEase 4.53 database does not affect the design - except for the possible increase in time it takes to modify or add a record.

Home Email Us