SQL code refactoring – ways to improve database performance (part I) – Encapsulate code as a stored procedure (4218859)

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?

• Make sure that all object references are fully qualified. The algorithms to match new SQL statements to existing execution plans, require that all objects are fully qualified
• Use parameters instead of constant values. The following two statements will not be matched as the same in an execution plan:
  

  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
  

  
  
• Identify the parts of queries that are most frequently used or take the longest time to run and refactor the SQL code to encapsulate them as stored procedures or functions. This will amp up execution plans reusing, as even an extra space can cause the queries to be declared as different and be recompiled.

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:

• Security - Granting and revoking permissions on an individual stored procedure is simple and provides a finer granularity access control. The access can be denied to the underlying tables and allow access only through the stored procedures. This cannot be done for parameterized SQL queries
• Using procedure parameters, limits SQL injection issues. Parameter input is not treated as executable code, so it’s more difficult to insert a command into the T-SQL inside procedures and endanger the database
• Reduced server/client network traffic - In a client/server environment, only the call to execute the procedure is sent across the network, unlike all lines of code in case of executing a SQL query
• Easier maintenance - As the stored procedure keeps the code in a single place, changes are applied only to one object the underlying database. A fix deployment of a stored procedure is simple and instant while the application itself remains intact
• Reuse of code - Database operations that are repeated are all encapsulated in a stored procedure, so there’s no need to rewrite same code, thus eliminating code inconsistency
• Vendor locking - Using stored procedures enables to lock the code if some software is written for other companies
• Code versioning - Stored procedures creation scripts can be added to a source control system, the same as creation scripts for tables, views, and other database objects

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:

1. Determine all input variables
2. Determine all output variables
3. Write declarations for all variables used in the code
4. Wrap it up in BEGIN … END
5. Parse the code to detect SQL errors

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

1. Open SQL query that needs to be encapsulated in either SQL Server Management Studio or Visual Studio

• In the ApexSQL menu, select ApexSQL Refactor

• 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:

  

• In the Encapsulate code as window, specify the schema and name of the stored procedure that will be created

• Click the Preview button to have ApexSQL Refactor list all the input and output parameters used in the SQL query and stored procedure code itself. ApexSQL Refactor automatically determines the parameter data type, whether the parameters are input or output ones and generates declaration code

• To create the code that executes the newly created stored procedure with all the necessary parameters, leave the Modify source script to reference this new object option checked

• Click the Create script button to open the generated script in the Query editor, so it can be modified:

  
  

  

• Click the Execute button to create a new stored procedure in the database currently
  connected to.
  

  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