{"id":872,"date":"2010-04-28T00:00:00","date_gmt":"2010-04-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/showplan-operator-of-the-week-compute-scalar\/"},"modified":"2021-08-16T15:02:17","modified_gmt":"2021-08-16T15:02:17","slug":"showplan-operator-of-the-week-compute-scalar","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/showplan-operator-of-the-week-compute-scalar\/","title":{"rendered":"Showplan Operator of the Week – Compute Scalar"},"content":{"rendered":"
\n

\"1018-F1.JPG\"Compute Scalar<\/p>\n

In the past two weeks, I’ve talked about two of the most important showplan operators, Concatenation<\/a> and Assert<\/a>. 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<\/b> showplan operator. This operator is very common, and we can see it in many execution plans.<\/p>\n

As is obvious from its name, Compute Scalar<\/b> 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.<\/p>\n

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.<\/p>\n

To start with, let’s take a simple use of Compute Scalar<\/b>. One simple conversion of data from Int<\/b> to Char <\/b>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.<\/p>\n

Let’s take the following query as a sample:<\/p>\n

The following script will create a table TabTeste and populate with some garbage data.<\/p>\n

USE tempdb\nGO\nCREATE TABLE TABTeste(ID   Int Identity(1,1) PRIMARY KEY, \n                      Nome VarChar(250)      DEFAULT NewID())\nGO\nSET NOCOUNT ON\nGO\nINSERT INTO TABTeste DEFAULT VALUES\nGO 10000\nNow, the code bellow will pass to the loop one million of times.\nDECLARE @I Int\nSET @I = 0\nWHILE @I < 1000000\nBEGIN\n  IF EXISTS(SELECT ID FROM TABTeste WHERE ID = @I)\n  BEGIN\n    PRINT 'Entrou no IF'\n  END\n  SET @I = @I + 1;\nEND\nGO\n<\/pre>\n
Graphical execution plan:<\/b><\/p>\n
\"1018-F2.JPG\"<\/p>\n
As we can see, the Operator Compute Scalar<\/b> is used, let’s take a look at the text execution plan to see more details about that operation.<\/p>\n
Text execution plan:<\/b><\/p>\n
\n
|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))\n        |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))\n             |--Constant Scan\n             |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED FORWARD)\n\n<\/pre>\n<\/div>\n
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.<\/p>\n
If we look at the profiler results we can see the column CPU which show us how much CPU the query above uses.<\/p>\n
\"1018-F3.JPG\"<\/p>\n
Now let’s change the code to remove the Compute Scalar operator.<\/p>\n
DECLARE @I Int, @Var Int\nSET @I = 0\nWHILE @I < 1000000\nBEGIN\n  SELECT @Var = ID FROM TABTeste WHERE ID = @I\n  IF @@ROWCOUNT > 0\n  BEGIN\n    PRINT 'Entrou no IF'\n  END\n  SET @I = @I + 1;\nEND\nGO\n<\/pre>\n
Graphical execution plan:<\/b><\/p>\n
\"1018-F4.JPG\"<\/p>\n
Text execution plan:<\/b><\/p>\n
\n
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED FORWARD)\n\n<\/pre>\n<\/div>\n
Now that SQL Server does not use the Compute Scalar, let’s take a look at the CPU costs.<\/p>\n
<\/pre>\n
\"1018-F5.JPG\"<\/p>\n
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<\/b> 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<\/b> in much the same way, with very satisfactory result for the performance of the procedure.<\/p>\n
Let’s take a look at more practical examples  of Compute Scalar<\/b>.<\/p>\n
DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)  \nSELECT 'Fabiano' + ' - ' + 'Amorim' FROM @Tab\n<\/pre>\n
Graphical execution plan:<\/b><\/p>\n
\"1018-F6.JPG\"<\/p>\n
Text execution plan:<\/b><\/p>\n
  |--Compute Scalar(DEFINE:([Expr1003]='Fabiano - Amorim'))\n       |--Clustered Index Scan(OBJECT:(@Tab))\n<\/pre>\n
The plan was generated using the Compute Scalar<\/b> just to make the concatenation between “Fabiano”, “-” and “Amorim”: Quite simple.<\/p>\n
Now we’ll see one very interesting behavior of Compute Scalar<\/b>  that it changes in SQL Server 2005\/2008.<\/p>\n
Consider the following query:<\/p>\n
DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)  \nDECLARE @ID_Int Integer\nSELECT *\n  FROM @Tab\n WHERE ID = @ID_Int\n<\/pre>\n
Notice that the Column ID is a SmallInt<\/b> type, and the variable @ID_Int<\/b> is a Integer, that means SQL Server as to convert the value of @ID_Int <\/b>to be able to compare the value with ID Column.<\/p>\n
At SQL Server 2000 we have the following plan.<\/p>\n
SQL 2000 Graphical execution plan:<\/b><\/p>\n
\"1018-F7.JPG\"<\/p>\n
SQL 2000 Text execution plan:<\/b><\/p>\n
\n
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))\n       |--Compute Scalar(DEFINE:([Expr1002]=Convert([@ID_Int])-1, [Expr1003]=Convert([@ID_Int])+1, [Expr1004]=If (Convert([@ID_Int])-1=NULL) then 0 else 6|If (Convert([@ID_Int])+1=NULL) then 0 else 10))       |    |--Constant Scan\n       |--Clustered Index Seek(OBJECT:(@Tab), SEEK:(@Tab.[ID] > [Expr1002] AND @Tab.[ID] < [Expr1003]),  WHERE:(Convert(@Tab.[ID])=[@ID_Int]) ORDERED FORWARD)\n\n<\/pre>\n<\/p>\n<\/div>\n
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.<\/p>\n
SQL 2005\/2008 Graphical execution plan:<\/b><\/p>\n
\"1018-F8.JPG\"<\/p>\n
SQL 2005\/2008 Text execution plan:<\/b><\/p>\n
|--Clustered Index Seek(OBJECT:(@Tab), SEEK:([ID]=[@ID_Int]) ORDERED FORWARD)  \n<\/pre>\n
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!<\/p>\n
Let’s look at the execution plan to understand what is going on with the Clustered Index Seek<\/b> show plan operator.<\/p>\n
Graphical execution plan:<\/b><\/p>\n
\"1018-F9.JPG\"<\/p>\n
As we can see, the SQL Server Dev Team has changed the Engine to use a function called “Scalar Operator<\/b>” to convert the value to the appropriate datatype, that’s interesting.<\/p>\n
That’s all folks, I see you next week with more “Showplan Operators”.<\/p>\n
\n
If you missed last week’s thrilling Showplan Operator, Concatenation, you can see it here.<\/a><\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n","protected":false},"author":65554,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4178,5178,5177,4149,5084,5162,4150,4151],"coauthors":[],"class_list":["post-872","post","type-post","status-publish","format-standard","hentry","category-learn","tag-bi","tag-compute-scalar","tag-fabiano-amorim","tag-learn-sql-server","tag-optimiser","tag-showplan","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/872","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/65554"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=872"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/872\/revisions"}],"predecessor-version":[{"id":92146,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/872\/revisions\/92146"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=872"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=872"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=872"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=872"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}