Databases Reference
In-Depth Information
SQL statement provide the fastest way to access a row because they usually point
to the exact location of the physical record.
Performance Tip
Use
SQLSpecialColumns
to identify the most optimal columns, typically
pseudo-columns, to use in the
Where
clause for updating data.
Some applications, such as an application that forms a
Where
clause consisting
of a subset of the column values retrieved in the result set, cannot be designed to
take advantage of positioned updates and deletes. Some applications may formulate
the
Where
clause by using searchable result columns or by calling
SQLStatistics
to
find columns that may be part of a unique index. These methods usually work but
can result in fairly complex queries. For example:
rc = SQLExecDirect (hstmt, "SELECT first_name, last_name," +
"ssn, address, city, state, zip" +
"FROM employees", SQL_NTS);
// fetch data using complex query
...
rc = SQLExecDirect (hstmt, "UPDATE employees SET address = ?" +
"WHERE first_name = ? AND last_name = ?" +
"AND ssn = ? AND address = ? AND city = ? AND" +
"state = ? AND zip = ?", SQL_NTS);
Many databases support pseudo-columns that are not explicitly defined in the
table definition but are hidden columns of every table (for example,
ROWID
for
Oracle). Because pseudo-columns are not part of the explicit table definition,
they're not retrieved when
SQLColumns
is called. To determine if pseudo-columns
exist, your application must call
SQLSpecialColumns
. For example:
...
rc = SQLSpecialColumns (hstmt, SQL_BEST_ROWID, ...);
...
