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 Correlated Subqueries (query line: 4): A correlated subquery is a subquery that contains a reference (column: inscode) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
fs_export_stage_data_customer m CROSS APPLY (SELECT
m2.inscode = m.inscode) sq1(cnt)
ON m.session = se.id
cnt > 1