Query Optimization Principles

Poorly created queries can cause performance issues. You can apply a few best practices to queries to avoid unnecessary impacts on performance.​

  • All queries should pull only the required data needed – Your queries should pull from a defined list of fields. For example, if you need to pull a customer’s account number, you should limit your query to only pull the Account number field. Pulling unnecessary fields will decrease performance.​
  • Avoid nested queries – Nested queries are queries within a query. For example, a nested query occurs if you create a query to pull the Customer table and then create a second query to pull the Sales table based on the first query. Instead, use a join to link the two tables into a single query.​

The four types of joins that you can make with select queries are:​

  • Join – The standard join pulls records that match on both tables, like an inner join.​
  • Outer join – This join pulls records regardless of whether those records match on both tables.​
  • Exists join – This join pulls all the records from the first table that match the records in the second table. No records from the second table are returned.​
  • Notexists join – This join pulls all the records from the first table that do not match the records in the second table. No records from the second table are returned.​

Using the appropriate join can increase performance by reducing the number of unneeded records and fields that are pulled.​