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.