SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL Optimization - CASTing and CURSOR


TSQL Optimization - CASTing and CURSOR

Author
Message
Jay-246689
Jay-246689
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 105
hi,

Is CASTing cost an additional overhead to the query? I need to optimize our database initialization and i reviewing some of the sql scripts and found out that there are several sp using this CAST( NULL AS VARBINARY(MAX)) in defining their column. If they cast a null value then it results is always a null value so why they still need to cast it? This sp is called a million times (>7millions calls).

How about a declaring a cursor, what it is overhead cost in terms of performance. They use it in one of their scalar function and being called also several times. this function compares the value of previous record to the current one. the loop will only pass on less than 10 records, and reading to a table containing a millions of records.

any comments, suggestion.

thanks.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5238 Visits: 4076
definitely conversion has some cost whether it is explicit or implicit. and if the cast/convert have been used in left part of filter (like cast(last_name as nvarchar(30)) = 'bhuv') then it will also hamper the index usagel.

second , why you want to replace the code with cursor ? it wil definitely has some bad impact.

Post your code , for better opinion.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2081 Visits: 3232
Using a CAST(NULL as <DataType>Wink is usually done to ensure that a place holder column has the right datatype for a later process especially when doing an SELECT INTO or when doing a Union across 2 tables with different columns, e.g.


Select
Column1
,column2
,CAST(NULL as Varchar(50)) as Column3
From
Table1
UNION ALL
Select
Column1
,column2
,Column3
Into #tmp
From
Table2



The other common use is when you are working with RS and SSIS calling SP's that use Temp tables, as these applications have problems 'guessing' the data set so you can fool it by doing this at the top of the proc.


IF 1=0
Begin
Select
Cast(NULL as Int) as Column1
Cast(NULL as Decimal(18,2)) as Column2
Cast(NULL as DateTime) as Column3
--- More columns
End



_________________________________________________________________________
SSC Guide to Posting and Best Practices
DiverKas
DiverKas
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 460
Jay-246689 (2/20/2013)
hi,

Is CASTing cost an additional overhead to the query? I need to optimize our database initialization and i reviewing some of the sql scripts and found out that there are several sp using this CAST( NULL AS VARBINARY(MAX)) in defining their column. If they cast a null value then it results is always a null value so why they still need to cast it? This sp is called a million times (>7millions calls).

How about a declaring a cursor, what it is overhead cost in terms of performance. They use it in one of their scalar function and being called also several times. this function compares the value of previous record to the current one. the loop will only pass on less than 10 records, and reading to a table containing a millions of records.

any comments, suggestion.

thanks.


The CAST function is pretty negligible, I wouldnt focus on it. The VARBINARY(MAX) has bigger potential issues than the CAST itself. Unless your using FILESTREAM, the page splits can be a bigger cost.

As for CURSORs, they are not evil. Contrary to what the lemmings say. It is what you do IN the cursor loop that is the danger. There are times and places for them, and most of the time they can be replaced with TSQL alternatives. Knowing when they are the correct tool, and being cognizant of what your doing in the loop can make them reasonably efficient.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7880 Visits: 7149
Is CASTing cost an additional overhead to the query?


In this case, no. In fact, here it might actually help ever so slightly.

SQL has to determine a datatype for every column anyway -- think about it, you can't have a table with a column with an unknown datatype. By explicitly CASTing it, you prevent SQL from having to try to determine a column datatype itself.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Jay-246689
Jay-246689
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 105
Thanks for all the reply, i been focus on other things and just read your messages. So it seems it is already optimized (Casting). Yes, on the latter part they used it to do insert/update on this field and some cases use it as a condition.

For the cursor here below is the scripts. This is created as scalar-valued functions, and being called multiples times in one process, >5millions calls. The results/returns records of each cursor called will not be more than 10 records and most of the times, 95%, it will only return one record. dbo.table holds more or less 800K records. And there are only 4 objects dependent on this function. Can you suggest any optimization on this scripts.
-----------------
DECLARE @m INT
SET @m = 0
DECLARE @l INT
SET @l = 0

DECLARE l_cursor CURSOR FOR
SELECT LId
FROM [dbo].table
WHERE EH = @param1
ORDER BY LId

OPEN l_cursor FETCH NEXT FROM l_cursor INTO @l
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l = 0 SET @m = @m | 0x0001
ELSE IF @l = 1 SET @m = @m | 0x0009
ELSE IF @l = 2 SET @m = @m | 0x0011
........multiple else if

FETCH NEXT FROM l_cursor
INTO @l
END

CLOSE l_cursor
DEALLOCATE l_cursor

return @m;

--------------

Thanks again.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39285 Visits: 38529
Jay-246689 (3/20/2013)
Thanks for all the reply, i been focus on other things and just read your messages. So it seems it is already optimized (Casting). Yes, on the latter part they used it to do insert/update on this field and some cases use it as a condition.

For the cursor here below is the scripts. This is created as scalar-valued functions, and being called multiples times in one process, >5millions calls. The results/returns records of each cursor called will not be more than 10 records and most of the times, 95%, it will only return one record. dbo.table holds more or less 800K records. And there are only 4 objects dependent on this function. Can you suggest any optimization on this scripts.
-----------------
DECLARE @m INT
SET @m = 0
DECLARE @l INT
SET @l = 0

DECLARE l_cursor CURSOR FOR
SELECT LId
FROM [dbo].table
WHERE EH = @param1
ORDER BY LId

OPEN l_cursor FETCH NEXT FROM l_cursor INTO @l
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l = 0 SET @m = @m | 0x0001
ELSE IF @l = 1 SET @m = @m | 0x0009
ELSE IF @l = 2 SET @m = @m | 0x0011
........multiple else if

FETCH NEXT FROM l_cursor
INTO @l
END

CLOSE l_cursor
DEALLOCATE l_cursor

return @m;

--------------

Thanks again.


A little hard to offer advice on how to rewrite a cursor when you don't even provide the entire code.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search