Some other things to keep in mind

✅ DO use of temp table#

This is yet another issue that is very difficult to solve. In many cases, we use a temp table to stop double-dipping into large tables. A temp table can also be used to significantly reduce the mandatory computing power when dealing with large volumes of data.

When linking data from a table to a large table, add a large subset to reduce the efficiency hindrance.

✅ DO minimize large write operations#

When updating and altering data, adding indexes or checking constraints to queries, processing triggers, and so on, writing, modifying, deleting, or importing huge volumes of data may influence SQL query performance and even cause the table to be blocked.

Furthermore, writing a large amount of data increases the size of log files. As a result, while big write operations may not be a significant performance issue, you should be aware of the repercussions and be prepared in the event of unexpected behavior.

✅ DO secure your code#

Databases store a wide range of data, making them great targets for hackers. SQL injections , in which a user enters a SQL query instead of a username, retrieves or modifies your database, are a common attack.

SQL injections can take the form of:

employeeId = getRequestString("EmployeeId ");
textSQL = "SELECT Name, Role FROM Employees WHERE EmployeeId = " + employeeId ;

Assuming you have this, employeeId will retrieve the employee’s input. Here’s where things can go wrong:

SELECT Name, Role FROM Employees WHERE EmployeeId = 988 OR 1=1;

It will retrieve all of the data from the Employees database because 1=1 is always true.

You may protect your database from SQL injections by utilizing parameterized statements, input validations, and input sanitization, among other things. The DBMS determines how you secure your database. To build secure code, you’ll need to understand your database management system and its security issues.

❌ AVOID running queries in loop#

• Coding SQL queries in loop slows down the entire sequence.
• Instead of writing a query that loops an INSERT statement, you can use bulk insert or update.
• This way as well the application will not handle another function to loop and call the database to perform action everytime it is looped.

Inefficient

SET STATISTICS TIME ON

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)

    INSERT INTO [SalesLT].[ProductDescription]
           ([Description]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           ('This is great'
           ,NEWID()
           ,'12/01/2010')
    SET @Counter  = @Counter  + 1
END

Efficient

SET STATISTICS TIME ON
INSERT INTO [SalesLT].[ProductDescription]
           ([Description]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           ('This is great'
           ,NEWID()
           ,'12/01/2010'),
		   ('New news'
           ,NEWID()
           ,'12/01/2010'),
		   ('Awesome product.'
           ,NEWID()
           ,'12/01/2010'),
..........,
		    ('Awesome product.'
           ,NEWID()
           ,'12/01/2010')
GO

Learn More#

SQL Query Optimization: How to Tune Performance of SQL Queries

Top 10 SQL Query Optimization Techniques

11 Tips to Boost SQL Query Optimization