Below are some tips on how to improve the performance of SQL DB objects which need to be followed while creating or modifying any DB objects.

Add Nolocks

No lock should be added to all select statements and while joining multiple tables when those tables are used by multiple pages or applications. It is advisable not to use Nolock on temp tables and Views.

Ensure No Table Scan

Ensure there is no table scan. Check this in the execution plan on SQL Server. The table scan can be avoided by using indexed columns on join conditions

Join and Update should happen on Primary Key Column

Avoid join of two tables on columns other than the primary key column /indexed column. Update also should happen on update key condition.

Select Only required Columns 

To reduce performance issues for queries that return heavy data use only required columns instead of all on the select statements.

Use Column Names in Insert Query

When inserting to a table that is more frequently used by multiple applications and which has heavy data specify column names to reduce performance issues.

Use Table Alias in Join query

Using table alias in join query helps reducing performance issues in lengthy stored procedures.

Query Optimization By SQL Indexes

Indexes improve search operation and reduce table scan hence improves performance. So it is always advised to use indexed columns for joins, select query filter conditions. Indexes somehow reduce the performance of the insert operation hence while creating indexes on the temp table it's a good practice to create it post data insertion to the temp table.

HostForLIFEASP.NET SQL Server 2019 Hosting