Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > Optimizing Queries
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.
The ADO.NET Command.Prepared property indicates whether or not to save a compiled version of a query before execution. The property sets or returns a Boolean ( true or false ) value.
You use the Prepared property to have the provider save a prepared ( or compiled ) version of the query defined in the CommandText property of a Command object before a call to execute the command.
This may slow a command's first execution, but once the provider compiles a command, the provider will use the compiled version of the command for any subsequent calls, which will result in noticeably improved performance, especially if the command is to be called so many number of times.
If the property is false, the provider will execute the Command object directly without creating a compiled version.
If the provider does not support command preparation, it may return an error as soon as this property is set to True. If it does not return an error, it simply ignores the request to prepare the command and sets the Prepared property to false.
To wrap up, at right is an example showing the effect of applying the Prepared property on a Command object that represents a multiple-table join query with aggregate functions.
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.
Introduction To Queries