The third part of Fabiano's mission to describe the major Showplan Operators used by SQL Server's Query Optimiser continues with the 'Compute Scalar' operator. Fabiano shows how a tweak to SQL to avoid a 'Compute Scalar' step can improve its performance.
Compute Scalar
In the past two weeks, I’ve talked about two of the most important showplan operators, Concatenation and Assert. It is useful to know about such Showplan Operators if you are programming in SQL Server, because they are used by SQL Server's Query Optimizer (QO) to perform a particular operation within a query plan. Each physical operation in the Query Plan is performed by an operator. When you look at a graphical execution plan, you will see each operator represented by an icon. This week we’ll be featuring the compute scalar showplan operator. This operator is very common, and we can see it in many execution plans.
As is obvious from its name, Compute Scalar performs a scalar computation and returns a computed value. This calculation can be as simple as a conversion of value, or a concatenation of values.
Most of the time, it is ignored by SQL users because it represents a minimal cost when compared to the cost of the entire execution plan, but, it can become well-worth looking at when we are dealing with cursors and some huge loops, and especially if you are having a CPU problem.
To start with, let’s take a simple use of Compute Scalar. One simple conversion of data from Int to Char can be done without much problem but, if we execute this conversion one million times, it becomes a different matter. If we change the query so as to not execute this conversion step, we will have an optimization in CPU use, and a consequential improvement in the speed of execution.
Let’s take the following query as a sample:
The following script will create a table TabTeste and populate with some garbage data.
USE tempdb
GO
CREATE TABLE TABTeste(ID Int Identity(1,1) PRIMARY KEY,
Nome VarChar(250) DEFAULT NewID())
GO
SET NOCOUNT ON
GO
INSERT INTO TABTeste DEFAULT VALUES
GO 10000
Now, the code bellow will pass to the loop one million of times.
DECLARE @I Int
SET @I = 0
WHILE @I < 1000000
BEGIN
IF EXISTS(SELECT ID FROM TABTeste WHERE ID = @I)
BEGIN
PRINT 'Entrou no IF'
END
SET @I = @I + 1;
END
GO
Graphical execution plan:
As we can see, the Operator Compute Scalar is used, let’s take a look at the text execution plan to see more details about that operation.
Text execution plan:
This plan is using the Compute Scalar to check if the Nested Loop returns any rows, on the other words; it is doing the IF EXISTS Job.
If we look at the profiler results we can see the column CPU which show us how much CPU the query above uses.
Now let’s change the code to remove the Compute Scalar operator.
DECLARE @I Int, @Var Int
SET @I = 0
WHILE @I < 1000000
BEGIN
SELECT @Var = ID FROM TABTeste WHERE ID = @I
IF @@ROWCOUNT > 0
BEGIN
PRINT 'Entrou no IF'
END
SET @I = @I + 1;
END
GO
Graphical execution plan:
Text execution plan:
Now that SQL Server does not use the Compute Scalar, let’s take a look at the CPU costs.
As you can see, SQL Server uses less CPU and finishes the execution of the query faster than it does with the first query. I’m not trying to show you the better way to check whether a particular value exists, I’m just showing the Compute Scalar behavior. However, if you have never seen this kind of validation using @@RowCount, it may be that it could help you a little bit in your coding. Some time ago I changed one procedure that uses a lot of IF Exists in much the same way, with very satisfactory result for the performance of the procedure.
Let’s take a look at more practical examples of Compute Scalar.
DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)
SELECT 'Fabiano' + ' - ' + 'Amorim' FROM @Tab
Graphical execution plan:
Text execution plan:
|--Compute Scalar(DEFINE:([Expr1003]='Fabiano - Amorim'))
|--Clustered Index Scan(OBJECT:(@Tab))
The plan was generated using the Compute Scalar just to make the concatenation between “Fabiano”, “-” and “Amorim”: Quite simple.
Now we’ll see one very interesting behavior of Compute Scalar that it changes in SQL Server 2005/2008.
Consider the following query:
DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)
DECLARE @ID_Int Integer
SELECT *
FROM @Tab
WHERE ID = @ID_Int
Notice that the Column ID is a SmallInt type, and the variable @ID_Int is a Integer, that means SQL Server as to convert the value of @ID_Int to be able to compare the value with ID Column.
At SQL Server 2000 we have the following plan.
SQL 2000 Graphical execution plan:
SQL 2000 Text execution plan:
Wow, it’s a quite hard work, don’t you think? Now let’s take a look what happens if we run this code at SQL 2005/2008.
SQL 2005/2008 Graphical execution plan:
SQL 2005/2008 Text execution plan:
|--Clustered Index Seek(OBJECT:(@Tab), SEEK:([ID]=[@ID_Int]) ORDERED FORWARD)
Yep, now we have a much more simple plan (which was nothing about the band). But wait a minute, what is this? Now SQL Server does not convert the value!
Let’s look at the execution plan to understand what is going on with the Clustered Index Seek show plan operator.
Graphical execution plan:
As we can see, the SQL Server Dev Team has changed the Engine to use a function called “Scalar Operator” to convert the value to the appropriate datatype, that’s interesting.
That’s all folks, I see you next week with more “Showplan Operators”.
If you missed last week's thrilling Showplan Operator, Concatenation, you can see it here.
