Optimizing SQL Queries

Summary
This article describes how to optimize SQL queries to run more efficiently at a lower cost
to the SQL Server. The query examples in this article apply to the Source 550 v6.1.4 SQL
Server edition only (SQL Server 6.5 or 7.0) and will not work in Source 520.

Detail
Typically, there's more than one way to write a SQL query to retrieve information from a
database. Depending on the data in the tables you're hitting, your query may not be as
efficient as it could be and it may take longer than necessary for the Server to return the
results of the query to Source. This could be particularly problematic if the inefficient
query is run against a large database. Source 500 has a 10 minute limit for how long it
waits for queries to execute and results to be returned from SQL Server. If your query
cannot complete in this time, Source 500 stops the query and a VB 3669 execution halted
error will be logged.
There are 2 workarounds to the timeout issue. The first involves a [Link] setting.
See article Z14365 for more information. Using this setting will impact all of areas of
Source 500. Since setting the timeout value too high may cause unpredictable results in
Source, this isn't the recommended approach.
Alternatively, you can write your query to run more efficiently. The SQL Server 7.0
Query Optimizer is designed to decide the most efficient way to run a query at the least
cost, but it doesn't always succeed. For best results, write an efficient query from the
start.
Defining Efficient Queries
The SQL Server 6.5 Books Online defines the following query techniques as resource
intensive. A query that is resource intensive will not run efficiently and should be
avoided:
"Some types of queries are inherently resource-intensive. This is related to fundamental
database and index issues common to most relational databases, including SQL Server.
They are not inefficient, as the optimizer will execute the queries in the most efficient
fashion possible. However, they are resource-intensive, and the set-oriented nature of
SQL Server may make them appear inefficient. No degree of optimizer intelligence can
eliminate the inherent resource cost of these constructs. They are intrinsically costly
when compared to a more simple query. Although SQL Server will use the most
optimum access plan, this is limited by what is fundamentally possible.
For example, the following query characteristics are resource-intensive:
· Large results sets
· IN, NOT IN, and OR queries
· <> (not equal)
· Row aggregate functions, such as SUM
· Local variables, expressions, or data conversions in the WHERE clause
· Highly nonunique WHERE clause
· Complex views with GROUP BY or ORDER BY

Various factors may require the use of some of these query constructs. The effect of these
will be lessened if the optimizer can restrict the results set before applying the resource-
intensive portion of the query. For example, the following restricted query is efficient:
SELECT SUM(qty) FROM sales
WHERE stor_id = 7131
If an index exists on the department column, the optimizer will likely use it to restrict the
results set before applying the SUM operation, which can improve performance.
Compare that with the following unrestricted and inefficient query, in which the sum
operation must read every row in the table:
SELECT SUM(qty) FROM sales
For example, the following query is efficient:
SELECT * FROM phonebook
WHERE last_name = @var
AND zip_code = 98052
If an index exists on the zip_code column, the optimizer will likely use it to restrict the
results set, which can improve performance. Compare that with the following inefficient
query:
SELECT * FROM phonebook
WHERE last_name = @var
The value in the local variable is only known at run time. Because the optimizer builds
the access plan and makes index usage decisions at compile time, the optimizer cannot
use the value in the local variable to restrict the results set, even with an index on the
zip_code column."
Some of these characteristics are unavoidable in Source 550 such as large result sets. For
example, the data scrub utility can produce large result sets when searching for all
employees who do not have a payroll record. However, there are some guidelines you can
follow to write an efficient query that will return a large result set. These are discussed in
the next section.
Efficient Query Guidelines & Examples
When you want to query information from 2 database tables that have a large number of
records, it's better to link these tables in your query using a JOIN operator instead of a
subquery. For instance, let's say you wanted a list of all active employees from the Ebase
table that did not have a Epayrollbase record. The following examples show an inefficient
and efficient way of writing this query. In the inefficient examples, the part highlighted in
red is the resource-intensive part of the query.
Inefficient Example 1:
SELECT EbFlxID, EbLastName, EbFirstName, EbPSID
FROM Ebase
WHERE EbFlxID NOT IN(SELECT PrbFlxIDEb FROM Epayrollbase)
AND EbRecType = 'EBAS'
AND EbDateEnd IS NULL
ORDER BY EbLastName
Efficient Example 2:
SELECT EbFlxID, EbLastName, EbFirstName, EbPSID
FROM Ebase
LEFT JOIN Epayrollbase ON EbFlxID = PrbFlxIDEb
WHERE PrbFlxIDEb IS NULL
AND EbRecType = 'EBAS'
AND EbDateEnd IS NULL
ORDER BY EbLastName
In the efficient query example 1, the LEFT JOIN operator retains all rows from the Ebase
table (considered the left table since it is the first table mentioned in the query) that don't
have a corresponding record in the right table (Epayrollbase). The WHERE clause
specifies Epayrollbase records without a link back to Ebase.
Another example would be a slight variation of example 1: All active employees from the
Ebase table that do not have a active Epayrollbase record.
Inefficient Example 2:
SELECT EbFlxID, EbLastName, EbFirstName, EbPSID
FROM Ebase
WHERE EbFlxID NOT IN(SELECT PrbFlxIDEb FROM Epayrollbase WHERE
PrbDateEnd IS NULL)
AND EbRecType = 'EBAS'
AND EbDateEnd IS NULL
ORDER BY EbLastName
Efficient Example 2:
SELECT EbFlxID, EbLastName, EbFirstName, EbPSID
FROM Ebase
LEFT JOIN Epayrollbase ON EbFlxID = PrbFlxIDEb AND PrbDateEnd IS NULL
WHERE PrbFlxIDEb IS NULL
AND EbRecType = 'EBAS'
AND EbDateEnd IS NULL
ORDER BY EbLastName
In the efficient query example 2, the AND PrbDateEnd IS NULL is added to the ON
clause to only join the 2 tables where there is no corresponding active Epayrollbase
record for each Ebase FlxID.
In the next example, you want to query all active employees in the Ebase table that have
multiple open base pay type Ecomp records (which can cause reporting problems).
Inefficient Example 3:
SELECT EbFlxID, EbLastName, EbFirstName, EbPSID
FROM Ebase WHERE EbDateEnd IS NULL
AND EbFlagEmp = 'Y'
AND EbFlxID IN(SELECT EmFlxIDEb FROM Ecomp WHERE EmDateEnd IS NULL
AND EmKind = 'BASE'
GROUP BY EmFlxIDEb
HAVING COUNT(EmFlxIDEb) >1)
ORDER BY EbLastName
Efficient Example 3:
SELECT EbFlxID, Min(EbLastName), Min(EbFirstName), Min(EbPSID)
FROM Ebase
INNER JOIN Ecomp ON EbFlxID = EmFlxIDEb
WHERE EmDateEnd IS NULL
AND EmKind = 'BASE'
AND EbDateEnd IS NULL
AND EbRecType = 'EBAS'
GROUP BY EbFlxID
HAVING COUNT(EbFlxID) >1
ORDER BY Min(EbLastName)
In the efficient query 3 example, an aggregate function (MIN) is used on all the fields in
the SELECT clause that are not included in the GROUP BY clause. This is a trick that
you can use when you want to group by one field and include additional fields in your
SELECT clause. The MIN function is also needed in the ORDER BY clause. In this
example, an INNER JOIN is used instead of a LEFT JOIN since you want to return
results where the employee has a record in each table. The HAVING clause keeps a count
of how many open base pay type Ecomp records a particular Ebase FlxID has.
In the next example, you want to query the Ebase table to find all clock #'s that are
duplicated.
Inefficient Example 4:
SELECT EbClock, EbFlxId, EbPSID, EbFirstName, EbLastName
FROM Ebase WHERE EbClock IN(SELECT MIN([Link])
FROM Ebase AS EB2
WHERE ([Link] <> '')
AND ([Link] <> ' ')
AND (NOT [Link] IS NULL)
AND ([Link] = 'EBAS')
AND ([Link] IS NULL)
GROUP BY [Link]
HAVING COUNT([Link]) > 1)
AND EbFlagEmp = 'Y'
AND EbDateEnd IS NULL
AND EbRecType = 'EBAS'
ORDER BY EbLastName
Efficient Example 4:
SELECT EbClock, Min(EbFlxId), Min(EbLastName), Min(EbFirstName), Min(EbPSID)
FROM Ebase Eb1
WHERE [Link] = (SELECT EbClock FROM Ebase Eb2
WHERE [Link] = [Link])
AND [Link] IS NOT NULL
AND [Link] <> ''
GROUP BY EbClock
HAVING COUNT(EbClock) >1
ORDER BY Min(EbLastName)
These query examples make 2 passes through the Ebase table: the first time through, the
alias Eb1 is used; the second, the alias Eb2 is used. Both examples make sure the clock #
is not NULL (never had a value) or an empty string. The HAVING clause keeps a count
of how many times a particular clock # is found in the table.

Keywords: Efficient, Inefficient, Queries, Resource-Intensive, Optimize, INNER JOIN,