[Solved] Performance of join vs pre-select on MsSQL
EverSQL Database Performance Knowledge Base
Performance of join vs pre-select on MsSQL
I can do the same query in two ways as following, will #1 be more efficient as we don't have join?
select table1.* from table1
inner join table2 on table1.key = table2.key
where table2.id = 1
select * from table1
where key = (select key from table2 where id=1)
How to optimize this SQL query?
The following recommendations will help you in your 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 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.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX table1_idx_key ON table1 (key);
CREATE INDEX table2_idx_id ON table2 (id);
The optimized query:
ON table1.key = table2.key
table2.id = 1