For the query above, the following recommendations will be helpful as part of the SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
Avoid Calling Functions With Indexed Columns (query line: 13): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `address_data` is indexed, the index won’t be used as it’s wrapped with the function `ISNUMERIC`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
Avoid LIKE Searches With Leading Wildcard (query line: 12): The database will not use an index when using like searches with a leading wildcard (e.g. '%,%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
Avoid Selecting Unnecessary Columns (query line: 5): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Avoid Selecting Unnecessary Columns (query line: 9): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Avoid Subqueries (query line: 8): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
oa.* INTO #tmpAddress
OUTER APPLY (SELECT
address_data LIKE '%,%'
AND ISNUMERIC(RIGHT(address_data, 4)) = 1
AND address_line_id != 1