Additional ADS SQL Topics


In addition to the topics covered earlier in this chapter, there are a couple of other topics that you should know before you continue onto Chapters 10 and 11. These are related to the limits of SQL statements, the difference between live and static cursors, and subqueries. These topics are discussed in the following sections.

SQL Statement Dimensions

There are limits to the length of individual SQL statements that you can pass to ADS. Fortunately, these limits are quite high and rarely, if ever, pose a problem.

For example, a single SQL statement cannot exceed 65,535 (64K) characters in length. Within that limit, individual string literals can be no longer than 1,024 characters. Table names and database names can be up to 256 characters in length, while column and index names can be up to 63 characters. For CREATE INDEX SQL statements, there can be no more than 15 column names in the index.

Tip

If you need to use strings longer than the 1,024 character limit, use a parameterized query (described earlier) and assign the string literals to the parameter.

Live Cursors Versus Static Cursors

You use SQL SELECT statements to retrieve data from the database server to your client application. When you execute a SELECT statement, ADS is going to produce one of two types of cursors: a live cursor or a static cursor. Which type of cursor is produced by a query has implications for both performance and features.

Creating Live Cursors

A live cursor is one that maps directly to an ADS table. If the live cursor does not include all records in the table being queried, the live cursor is produced by ADS by creating an AOF (Advantage Optimized Filter). (Selecting fewer than all records means that a WHERE clause is included in the SELECT statement.)

ADS will first attempt to build the AOF using existing indexes. If all of the necessary indexes already exist for the table, ADS is able to quickly build an AOF. These AOFs are referred to as fully optimized AOFs, and they are one of the primary sources of ADS’s speed.

If the WHERE clause can make use of one or more indexes, and some, but not all, of the indexes that ADS requires are present, the AOFs will be partially optimized based on the existing indexes. These AOFs are referred to as partially optimized filters. ADS will then have to explicitly read the records that match the optimized part of the AOF to determine whether or not they match the nonoptimized part of the filter expression.

Imagine that you have a customer table that includes both Last Name and City fields. Imagine further that you have an index on Last Name, but not on City. If your filter expression is Last Name = "Green" and City = "Chicago", the AOF will be partially optimized. ADS will use the Last Name filter to quickly select all Greens, but then must read every one of these identified customers in order to determine whether or not they live in Chicago.

If no indexes currently exist to assist in satisfying the WHERE clause, the AOF is a nonoptimized filter. With a nonoptimized filter, ADS must read every record in the entire table to determine whether or not it passes the filter expression. As you can imagine, this results in the slowest performance of the three AOF types.

Once the AOF has been constructed, ADS begins returning records to the client. The number of records returned depends on the size of the result set, the size of individual records, the number of records requested by the application, and the requirements of the client. ADS will usually return to the client the number of records configured in the record cache setting (or a single record if the application requested only the first record in the result set). The default record cache setting is 10 or however many records fit into a transmission burst, whichever is smaller. A transmission burst is 22K bytes with IP, and 8K bytes with IPX.

Note

See the ADS help for information on changing the record cache size.

If the returned records do not satisfy the client’s needs—for example, if the client application is attempting to populate a grid that can display more records than that received in the first transmission—another transmission, and then another, if necessary, is returned until the client is satisfied. This process is repeated each time the client navigates to another part of the result set, as well as each time the query is executed.

From a performance perspective, whether or not an optimized AOF can be created affects how quickly live cursors are returned. This is why it is important to create indexes on those expressions that will be used regularly to select data from your tables.

Using Live Cursors

The primary benefit of a live cursor is that it is a virtual view of a table. If another client changes the contents of the underlying table, that change is immediately reflected in the AOF. For example, newly inserted records that meet the WHERE clause become part of the live cursor, and will be received by the client the next time the client application requests data from the region of the AOF where the record was inserted. This may occur due to normal navigation, or be the result of an explicit refresh.

But there are two specific situations where live cursors based on AOFs do not have automatic access to newly posted data. With ALS, AOFs are not updated each time data changes. As a result, with ALS, an AOF may become obsolete on a client, returning data that no longer meets the WHERE clause. The second situation is when you are using compatibility locking to share DBF files with non-ADS applications. In these cases, ADS is not the sole application that is accessing those files, and once again, the AOF may become obsolete. For these situations, you need to reexecute the query to create a current AOF.

Creating Static Cursors

Static cursors are created automatically any time ADS cannot create a live cursor. This happens when the SELECT statement selects more than simple columns from a single table, contains the DISTINCT or TOP keyword or an aggregate function in the SELECT clause, contains a GROUP BY or HAVING clause, includes a subquery (discussed later in this section), or includes either a BLOB field or the LIKE operator in the WHERE clause. Another element that will prevent a live cursor is the use of any scalar function in an expression in the WHERE clause other than the functions listed in Table 9-6.

Table 9-6: SQL Scalar Functions That Can Be Used in Live Cursors

ABS

BIT_LENGTH

CHAR

CHARACTER_LENGTH

CHAR_LENGTH

CONCAT

CONTAINS

CURDATE

CURRENT_DATE

CURRENT_TIME

CURTIME

IFNULL

IIF

ISNULL

LCASE

LEFT

LENGTH

LOCATE

LOWER

LTRIM

NOW

OCTET_LENGTH

PI

POSITION

POWER

RIGHT

RTRIM

SPACE

SUBSTRING

UCASE

UPPER

Even if your SELECT statement would not otherwise create a static cursor, you can specifically request a static cursor when you create your SQL statement. Simply include the {static} directive immediately following the SELECT keyword to request a static cursor. For example, the following query produces a static cursor, even though ADS would normally create a live cursor:

SELECT {static} * FROM INVOICE

While a live cursor is a filtered view of a table based on AOF, a static cursor is actually a new, temporary table constructed by (and managed by) the server. While this temporary table may take a while to create, ADS will begin returning records to the client before this temporary table is completely populated. As a result, there are rare situations where static cursors will make records available to the client faster than when live cursors are used. In most cases, using live cursors is faster because no temporary files will be created.

This behavior of static cursors—returning results before the temporary table is completely filled—is obvious if you first obtain a static cursor on a large result set and then attempt to navigate to the last record. Going to the last record is something that can only be accomplished once the static cursor is completely populated. Consequently, if you obtain a static cursor and then immediately attempt to move to the last record, there will often be a noticeable delay while ADS awaits the population of the temporary table prior to returning the last record.

SQL statements that cause a static cursor to be fully populated include the use of aggregate functions in your query, such as MIN, MAX, and COUNT, as well as including an ORDER BY clause.

This discussion is not meant to imply that static cursors are not optimized. Indeed, whenever possible, ADS will employ indexes and use other optimization techniques to identify the records that must be copied to the temporary table.

Using Static Cursors

From the standpoint of client applications, there are only two differences between using a live cursor and a static cursor. The first is that a static cursor is readonly. You cannot make changes to the result set created using a static cursor. (The exception is static, client-side cursors in ADO, which are updateable.) By comparison, you can insert, delete, and update records using a live cursor.

The second difference is that static cursors do not automatically reflect changes that have been posted to the underlying table. Recall that a static cursor actually points to a temporary table on the server. Consequently, if another client inserts a record into a table from which you selected a static cursor, the record is inserted into the destination table but not into the temporary table. If you want to see changes made to the underlying table, you have to reexecute the query, which causes ADS to rebuild the temporary table.

Using Subqueries

A subquery is a SQL SELECT query within another query. As discussed in more detail in Chapter 10, SELECT queries produce a result set of zero or more records. The values returned by the subquery are used to define which records are affected by the operation defined in the query that contains the subquery. For example, a subquery may select all customers who have a perfect record of payment. Those records can then be used to select the employee responsible for the greatest number of sales to that customer.

A subquery can appear in the values section of INSERT queries, the HAVING clause of SELECT queries, and the WHERE clause of DELETE, SELECT, and UPDATE queries. For example, the following query includes a subquery in the WHERE clause of a SELECT query:

SELECT * FROM EMPLOYEE   WHERE "Employee Number" IN   (SELECT Contact FROM DEPARTMENTS)

This SELECT query selects all fields for those records where the employee number appears in the Contact field of the DEPARTMENTS table. Assuming that the Contacts integer field contains the employee number of the department head, this query will select all employees who are department heads.

Here is a SQL script that employs a subquery, which assumes that an INV_BAK table (an archive table) exists:

INSERT INTO INV_BAK    SELECT * FROM INVOICE   WHERE "Date Payment Received" < CURDATE() - 365; DELETE FROM INVOICE   WHERE "Date Payment Received" < CURDATE() – 365

The first SQL statement in this script is an INSERT query that inserts records into the INV_BAK table from the INVOICE table where the payment was received more than one year ago. The second query then removes these records from the active INVOICE table.

In both of the subquery examples presented here, the subquery is one that can be optimized. Furthermore, because these subqueries do not include references to fields outside the table they are selecting from, ADS is able to execute the subquery once, and reuse that result for all operations performed by the query containing it.

Since subqueries can contain any valid statements that can appear in a SELECT statement, it is possible to include links to fields outside of the table the subquery is selecting from. These subqueries are referred to as correlated subqueries, and unless you have small tables that you are querying, they should probably be avoided for performance reasons. Consider the following query:

SELECT * FROM EMPLOYEE Emp WHERE "Employee Number" IN   (SELECT "Employee ID" FROM INVOICE Inv    WHERE Emp."Employee Number" = Inv."Employee ID"   AND "Date Payment Received" IS NULL)

This query selects all fields from each employee record where that employee made a sale and the customer’s payment was never entered. In this case, the WHERE clause in the subquery includes a Boolean comparison with a value from the outer query. Unlike the previous queries, this one requires the subquery to be executed repeatedly, once for each employee in the EMPLOYEE table. If the EMPLOYEE table has few records, this query will execute quickly. However, if the EMPLOYEE table is very large, this query could take a substantial amount of time to execute.

This discussion is not meant to dissuade you from using subqueries. Quite the opposite; subqueries are exceptionally powerful. However, if performance is an important element of your database design, you should consider what you are asking ADS to do in your subqueries, and balance the power of these queries with your performance requirements.




Advantage Database Server. The Official Guide
Advantage Database Server: The Official Guide
ISBN: 0072230843
EAN: 2147483647
Year: 2002
Pages: 129

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net