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