Using Stored Procedures

When writing T-SQL code, you have several options available. Depending on the implementation you use, we will determine what SQL Server does with that T-SQL code. You can write ad hoc T-SQL where each query is written without uniformity. While this code can be repeated, you will need to be careful to make sure that all formatting is exactly consistent, including whitespace. There is also what is referred to as prepared statements. This type of T-SQL code uses parameters. When the prepared statements are used, the values passed to the parameters are updated. Finally, there are stored procedures. These are database objects that are persistent. Each of these has their pros and cons. However, stored procedures generally are the most consistent when writing T-SQL.

There are several aspects to consider when writing T-SQL code for applications. In order to better understand how you want your applications to use T-SQL, it may be best to understand how SQL Server handles each of the T-SQL options available. When SQL Server executes a query, it must determine how it will go about executing that query. Before doing this, SQL Server will check to see if there is already an execution plan for this query. Depending on how the T-SQL is written will have some impact on how SQL Server verifies that the execution plan is already in place.

SQL Server will create this execution plan and save the execution plan to the query plan cache. If this stored procedure is run again in the future, SQL Server will check the plan cache to see if this stored procedure already exists in the cache. This plan will sit in the plan cache for as long as SQL Server deems the plan relevant. In Figure 4-1, you can see the execution plan associated with the stored procedure in Listing 4-2.

You can see in Table 4-2 that there are now three entries in the plan cache. Instead of SQL Server reusing the execution plan from the first time the ad hoc query was called, SQL Server has generated an entirely new execution plan. Each time SQL Server creates a new execution plan, it uses additional resources to create the plan. This can cause it to take a couple of seconds longer for SQL Server to return the query results. Each query plan executed is saved in the plan cache. Having multiple execution plans saved for each query can fill up the execution plan cache. This can cause other query execution plans to be removed from the plan cache prematurely. The removal of a query plan from the cache will cause SQL Server to expend additional resources to create a new execution for that other query. This is something to consider when determining how your applications will call T-SQL code.

While a stored procedure gives us a consistent way to call that same type of code more than once, stored procedures can be fairly limiting if the exact same base query will be used for every scenario. I will often want to write a general piece of T-SQL code that can be used for various scenarios. In these types of situations, I find myself wanting to write queries or stored procedures that use parameters.

Using Parameters

In Table 4-3, you can see that the stored procedure has been executed twice. The SELECT statement with the hard-coded value and the SELECT statement with the parameter value each have their own execution plan. This may not have a large impact when you are discussing only a handful of queries. However, if your entire environment has a significant number of queries that are not stored procedures, you may want to check your plan cache to see how they are being handled.

For this stored procedure execution, I get the following execution plan as shown in Figure 4-2.

When I execute this stored procedure, I find that a different plan cache is created. You can see the other execution plan that is created in Figure 4-3.

If I run the same T-SQL from Listing 4-12, I now get the same execution plan. This is a sign that this query is encountering parameter sniffing. There are several options available to handle parameter sniffing. Ideally, you may be able to redesign your query to use different indexes.

In Listing 14-14, the execution plan is considered stable when MealTypeID of 2 is provided. When the stored procedure is run with a MealTypeID of 2, the stored procedure will execute the T-SQL code without generating a new execution plan. However, if a different value is provided for the MealTypeID, then SQL Server will generate a new execution plan each time. This type of scenario works well when most of the time the query is executed; it will receive the parameter of MealTypeID of 2. This keeps SQL Server from generating an execution plan every time the stored procedure is called. If one or two times out of every ten times the stored procedure is executed and the parameter is not set as MealTypeID of 2, then SQL Server will generate a new execution plan. This method allows you to minimize the cost of recompiling the execution and limiting this to happen much less frequently. While SQL Server is working as intended, it is the shape of the data that is contributing to the performance issue.

Using Complex Logic

There are some basic operations in SQL Server that are not significantly complex. These basic operations can include inserting, reading, updating, and deleting table from single tables. Some of these operations may involve a couple of joins, but at some point in time, there may be a need for more complex logic. One of the challenges that happens while working with T-SQL is dealing with complex logic.

When dealing with complex logic, it is important to keep a couple of things in mind. The first step in dealing with complex logic is breaking down the request into smaller parts. Part of this logic will be to figure out what data you need to start with and how to whittle that data down into a smaller data set. You will also want to focus on taking all the requirements and breaking those requirements down into simplified steps.

Many of these query requests can involve either dealing with databases that were not designed to take advantage of the strengths for relational databases and systems that were not designed to be integrated or interacting with legacy applications. Oftentimes, we do not have control over what we are requested to do, and we are lucky if we get to design how the data is stored in the database.

These types of scenarios can also involve various types of coding methodologies that do not necessarily coincide with best practices. This includes needing to loop through data including recursion. There are also times that you may want to use correlated subqueries or work with various strings such as XML or JSON. While many of these options may seem like they are the perfect solution, many of these options are overly complex. This is where the balancing act between making T-SQL code easy to understand and having T-SQL code that performs well can be tricky. My goal here is to show how using what seems like more advanced query techniques may be contributing to poor performance in your applications.

It is often tempting with writing T-SQL to write your code in a way that matches the specific acceptance criteria or business request. Sometimes this will work well, and you will end up with code that performs very well. In other instances, not only can trying to write to solve complex problem be difficult and frustrating, but if the T-SQL code is not written in a way that works best for SQL Server, you have the potential to come across significant performance issues. I have found that SQL Server generally works best if you can keep your code simple and straightforward. This also means that using new functionality in SQL Server may not always yield the best results in terms of performance, even if the code is easier to write.

This query will give us the exact results we want, but SQL Server will have to repeat the logic inside the cursor for each row that the cursor will be analyzing. When the query logic inside the cursor is written well, there can be minimal impact. However, the real challenges happen when the T-SQL code inside the query either performs slowly or uses a significant amount of hardware. Then the server and even the applications can feel the pain every time the code inside the cursor is executed. While it is easy to blame cursors specifically, there are other scenarios like WHILE loops that have the same potential for issues.

When working with queries where the logic is not straightforward, I will start by breaking down the overall request into smaller pieces. Ideally, the process of breaking the acceptance criteria down will allow me to focus on where I can start minimizing data. I will look at the query from two different perspectives. The first is determining how I can start paring the data down to only the data I will need in my result. I usually try to do that as early as possible in my query design. I will also try to look for the sections where I know the T-SQL code needed will be simple.

The original cursor gave the output shown in Figure 4-4 when the results were written to text.

To confirm that our rewrite from Listing 4-19 is correct, I captured the results to text when executing the same query. The output in Figure 4-5 appears to be the same.

However, these outputs are only the same when they are exported to text. When dealing with small sets of data, the extra code and level of work may not seem to be required. As is usually the case, most performance issues do not become apparent until the performance is significantly and negatively impacted. In the case of these two methods, when working with two records, the performance appeared the same. However, when handling over 10,000 recipes, it becomes immediately clear that the query from Listing 4-19 performs significantly better.

In this chapter, I have covered how and why you would want to use stored procedures. This includes creating code that is reusable and consistent. I have also shown you how to use parameters. For the most part, parameters will help make your code more adaptable and dynamic. Parameters are helpful for many different scenarios. While parameters are very useful in many different situations, you will also need to make sure your T-SQL is not being negatively affected by parameter sniffing. I also covered a common situation where you may need to solve complex problems using T-SQL. You will often find your T-SQL performs better when you keep the code more straightforward. This may mean that the code is not as readable or clean, but SQL Server will have a better idea how to get an optimal execution plan.

This also concludes the current section on writing understandable T-SQL. This is T-SQL that uses the best data type for each scenario. This is usually a data type that takes up the least amount of space possible and provides the necessary accuracy. You will also want to understand the various SQL Server database objects available that you can use to build your T-SQL. You will need to determine the benefits vs. the challenges of using one database object vs. another. In some cases, you will need to decide if code readability or database performance is more important. Another aspect of writing understandable T-SQL is formatting, naming, and commenting your T-SQL so that others can quickly understand what you have written.

When working with T-SQL, there will be times where you want to write T-SQL that can be called consistently more than once. This T-SQL code may also become more flexible using parameters. When faced with writing T-SQL that is not straightforward, it will help you break the code into segments to simplify what needs to be written. Once you feel comfortable writing T-SQL that is understandable, you will be ready to start focusing on writing T-SQL that is efficient and minimizes performance impact on SQL Server.