Seagate ST43401N/ND Informazioni Techniche Scaricare il pdf (Pagina 78)

5-24 Implement: Making Required Application Changes

If Your Current Version is 4.x, 10.x, or 11.0.x Adaptive Server Enterprise 11.5

General Tips

• Try to use queries that can be “ﬂattened” by the optimizer. The

optimizer can treat certain subqueries like normal or existence

joins, which are much quicker to execute. Queries that can be

ﬂattened include:

- Many

in, any, and exists subqueries

- Expression subqueries like the following:

column {<, <=, >, >=, !=, =} subquery

- Expression subqueries with unique joins or that return unique

columns.

• Avoid subqueries that cannot be ﬂattened, such as the following:

- Most

not in, not exists, and all subqueries

in, any, exists in an or clause

in, any, exists in a correlated subquery with aggregates

- Expression subqueries without unique joins or not returning

unique columns

• Queries with

exists and not exists are faster than in and not in when

used with

if. The exists test will stop processing the instant it ﬁnds

a matching row. This is true for both

if statements and subqueries.

Don't:

if 0 < (select count(*) from Employee where

LastName = "BURKE")

begin

.../*statement group*/

end

if "BURKE" not in (select LastName from Employee)

begin

.../*statement group*/

end

Do:

if exists (select * from Employee where LastName

= "BURKE")

begin

.../*statement group*/

end

• Use “>=” rather than “>” when using a non-unique index or just

the leading columns of a composite index. In a query such as this

(with a non-unique index on the age column of Employee. In a

1 2 ... 73 74 75 76 77 78 79 80 81 82 83 ... 171 172

Commenti su questo manuale

Nessun commento

Seagate ST43401N/ND Informazioni Techniche Pagina 78

Commenti su questo manuale