Sunday

SQL PERFORMANCE: HOW TO IMPROVE SQL QUERY PERFORMANCE

SQL PERFORMANCE: HOW TO IMPROVE SQL QUERY PERFORMANCE
07:03 by Anjan Kant

INTRODUCTION

SQL query performance improvement is an extremely intriguing topic amongst developers and the client group. Clients dependably needs a quick reaction on their data recovery actions and developers set forth their earnest attempts to give the information in the most brief time, be that as it may, there is no clear approach to characterise what is the best performance. At some point it's easily proven wrong what is good and what is bad execution of a question however generally on the off chance that you take after prescribed procedures amid development, you can give the best inquiry reaction to clients and avoid such discussions about optimal performance.

HOW TO IMPROVE SQL QUERY PERFORMANCE

There are multiple approaches to improve SQL query performance, which falls under different classes like re-composing the SQL query, creation and use of Indexes, management of statistics, etc.
Here are some SQL best practices to ensure query optimisation:
Avoid multiple joins: Avoid writing a SQL query using multiple joins with outer joins, cross apply, outer apply and other complex sub queries.
Eliminate cursors: Remove cursors from query and opt for set-based query as it is more efficient than cursor-based.
Avoid using non-correlated Scalar Sub query: Re-write query to eliminate non-correlated scalar sub query as a separate query and store output in a variable.
Avoid Multi-statement Table Valued Functions: Avoid Multi-statement TVFs as they are costlier than inline TFVs.
Clustered and Non-Clustered index: Create Clustered and Non-clustered index in SQL. These allows SQL server to rapidly and proficiently discover rows associated with key values.
Restrict size of working data set: Analyse the tables utilised as a part of the SELECT statement to check whether you can apply filters in the WHERE clause of your statement. An example case will be when a query initially functioned well when there were just a few  thousand lines in the table. As the application grew the query slowed. The answer might be as basic as limiting the query to look  at the present month's data. When you have queries that have sub-selects, apply filters to the internal statement of the sub-selects rather than the external statements.
Eliminate unnecessary tables: Writing SQL statements is a procedure that generally takes various iterations as you write and test your SQL statements. Amid development it is conceivable that you add tables to the query that might not have any effect on the data returned by the SQL code. By deleting these unnecessary tables you decrease the degree of processing the database has to do.
Exclude prefix “sp_”: Avoid prefix “sp_” with user defined stored process name since SQL server search the user defined process in the master database before in the current session database.
UNION ALL in place of UNION: UNION ALL does not need to make the additional stride of checking the result sets and separating just the unique values, so use UNION ALL over UNION, since it doesn't need to sort the result set.

SQL Performance

Specify All Primary Keys and Foreign Key Relationships: Primary keys and foreign key relationships that are effectively characterised help ensure that you can compose optimal queries. One common consequence of incorrect relationships is adding DISTINCT clauses to dispose of excess data from result sets.
Examine Your Server Specifications and Performance: You should have a database maintenance plan with focus on index management and database reduction. Your database server should have required RAM to support SQL Server. SSIS ETL processes should run on an ETL or staging server if they are memory/CPU intensive for long periods of time.
Use Stored Procedures or Parameterized Queries: The SQL server saves execution plans for stored procedures and parameterized queries under most circumstances. This allows them to be reused on later calls.

Add Schema Name before your SQL object name
To improve your SQL query performance, you are required to apply schema name followed by  “.”
 -- dbo is here used as schema name
SELECT col_1, col_2, col_3 from dbo.tblMyTable;
-- Should Avoid T-SQL statement like this
SELECT col_1, col_2, col_3 from tblMyTable;
Best Practice by Applying SET NOCOUNT ON
After applying SET NOCOUNT ON, it is quite sure to improve your SQL statement performance. SQL Server returns no of rows affected after statements SELECT, INSERT, UPDATE and DELETE statement, after applying SET NOCOUNT ON we can stop it.
CREATE PROCEDURE USP_MyFirstProcedure
AS
SET NOCOUNT ON
BEGIN
.
.
.
END
Always Apply TRY-Catch
By practising TRY-Catch, we can cut deadlocks caused by complex queries.
BEGIN TRY  
     { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
     [ { sql_statement | statement_block } ]  
END CATCH  

CONCLUSION

These are some quick and simple techniques for enhancing query performance for some long-running process. While these systems won't have any significant bearing to each issue you may experience but, they will help in a few cases.

Relevant Reading

.Net Core: What Is New In ASP.NET Core 2.0

.Net Core: What Is New In ASP.NET Core 2.0
04:25 by Anjan Kant

Introduction

ASP.NET is the next evolution of Microsoft for the web pages that are supposed to be server driven. Rather than modifying and amending the ASP and adding new features and functionalities into it, new software called the ASP.NET has come up. It is completely and overhauls as it provides an interface for programming that lets the developers keep coding in an advanced way for the internet. These are the developers who are familiar with the concepts of Visual Basic as well as the other tools that are based on the development of relations between client and server. The most well-known providers of hosting services support the technologies that have a .NET extension; they support both ASP.NET core 2.0 and ASP.NET core 3.5.  The new .net core 2.0 features are also supported by these providers.

Things to know about the latest ASP.NET Core 2.0:

In the latest ASP.NET core 2.0 hosting those developers who have a past record of developing different stuff in the world of client and server are allowed for transferring their skills seamlessly to the web. Those developers and programmers who also have been writing programs in different languages including Java, Visual Basic, C++, etc. for the purpose of development on the internet are allowed for using the already existing components. This lets them keep working with their web pages of ASP.NET core and the web forms as well. In the latest release of ASP.NET, these functionalities have been enhanced to a great level.
What Is New In ASP.NET Core 2.0

New features added in ASP.NET core:

Support for Razor Pages

The support for Razor pages is one of the features that have been recently added to the functionality of ASP.NET core. MVC has the same features as the Razor pages.

New framework capabilities

The APIs, as well as the ability for referencing the complete Framework Libraries, has been expanded in the .NET core 2.0.

New OS addons

There is an expanded support of Operating System.

New Optimisations

Now .NET core is supported by the Visual Beginners All-purpose Symbolic Instruction Code. It allows the users to make console applications and the class libraries.

Csproj is the new game

The project files of xproj are to be replaced by the csproj’s new version.

Legacy Support

The MS builds have started working again in the new version.

New updates for visual studio

There are certain tooling enhancements done by Visual Studio 2017 in the new version of .NET.

Conclusion

You should keep over viewing ASP.NET Core 2.0, and its latest versions so that you do not miss out any of its new functionalities. Recently, the tools for developers and the integration of the database for the latest version of ASP.NET have also been designed in such a way that it becomes very easy to use. By following the easy steps, you can go for .net core 2.0 download and further explore about the software.

Suggested Reading