Home > Data Access in Web Forms > Structured Query Language (SQL) Basics > Optimizing Queries
Using SQL Aggregate Functions
In most cases, SQL decides what is needed to optimize a query and does the work for you. You do not need to create indexes for find and sort operations to optimize these operations. If SQL decides it needs indexes, it creates temporary indexes for its own use.
The following situations, though, can degrade performance in a Select query when creating join conditions, and may produce unexpected results, so it’s best to keep this information in mind and to avoid them:
- Including two tables in a query without specifying a complete join condition
If you specify more than one table in the FROM clause but have not included a JOIN and a WHERE clause to indicate how the tables are to be joined, the query generates a Cartesian product of the tables.
A Cartesian product is the result of joining two relational tables, producing all possible ordered combinations of rows from the first table with all rows from the second table. Every record in the first table is joined with every record in the second table as long as the filter conditions are met. Such a query can produce lengthy yet unneeded results.
- Joining tables containing empty fields
Use caution when joining tables with empty fields because some providers match empty fields. For example, if you join two tables and one contains 25 empty fields, and the other contains 50, the query output will contain 1,250 extra records resulting from the empty fields.
Well, that’s about it for this session. Hopefully at least, you’ve gained insight on the fundamentals of working with variations of the SELECT query.
In certain cases, query syntax may depend on the specific provider. An error can occur if you attempt to execute a query when one of the following conditions applies:
- the SQL command is incomplete or contains one or more syntax errors.
- the SQL command is valid but is not supported by the provider.
- the SQL command is valid but contains syntax specific to a data connection you are using.
The topics in this article use syntax applicable to most providers whenever possible, but you should always ensure whether a statement is valid for a specific provider by running the query on a target open connection.
End of series.
Using SQL Aggregate Functions