Database performance is a challenge for every developer and DBA. Even when some improvements are made, there is always a question – is there anything else that can be done.
There might be. Some of the performance improvement techniques are not code related - a proper indexing strategy, adding memory, using different disks for data files, log files, and database backups, using faster disks, optimizing tempdb performance.
Besides these, there are some changes that must be applied to the code. Although code changing sounds like a time-consuming process prone to mistakes, it doesn’t have to be so.
There are a couple of techniques that can be used to wrap up the code better and thus improve the performance, without in-depth analysis and massive code rewriting. Reusing SQL Server execution plans is one of them.
Whenever a SQL statement is executed, SQL Server searches the procedure cache (the part of the memory pool where SQL Server stores execution plans) for the execution plan for the executed SQL statement. If the execution plan already exists, SQL Server will, instead of recompiling the SQL statement, reuse it, thus saving the resources. If the execution plan doesn’t exist, SQL Server will create it and reuse later. Every time a new execution plan is created or the existing is recompiled, query performance is affected.
What can be done to reuse SQL Server execution plans?
SELECT production.product.productid FROM production.product WHERE productsubcategoryid = 1; SELECT production.product.productid FROM production.product
WHERE productsubcategoryid = 4;So, it’s recommended to use the following:
SELECT production.product.productid FROM production.product WHERE productsubcategoryid = @Parm
What are other benefits of using stored procedures?
By refactoring SQL code to encapsulate several statements in a single stored procedure or function, code complexity is reduced. Encapsulating all code into stored procedures can create additional administration that may not be needed, so it’s best to encapsulate only the long-running, complex, multi-staged queries, and most frequently used code. Stored procedures should not contain low-level decision logic and simple queries.
This way, specific code is stored in a single place, eliminating duplicates and reducing the number of lines to maintain. A stored procedure/function can be called from many different applications without duplicating SQL code. Besides improved performance, stored procedures have the following advantages over the SQL code:
One of the options to create a stored procedure is to use the existing SQL code and manually create a generation script for the set of statements that need to be encapsulated. This means going through statements and:
Another option is to do this automatically, using ApexSQL Refactor.
ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which creates stored procedures and functions by refactoring the existing SQL queries, without a need to write any additional code or determine and declare parameters manually. It also expands wildcards and fully qualifies object names, which also improves SQL code performance
Select the Encapsulate code as command and then Stored procedure, Scalar inline function, Table inline function, or View option depending on what the code does:
Click the Create script button to open the generated script in the Query editor, so it can be modified:
If the Modify source script to reference this new object option was checked, code such as this will be generated after the CREATE PROCEDURE script:
-- Modified source: EXECUTE dbo.usp_PersonNew @add, @parm
© ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center