May 24, 2010 at 3:58 am
All,
I just started to work to replace a cursor in a function. My goal is to use SET BASED techniques.
so i had digged my database and found a function which use CURSOR to concatenate the result (which is not at all required)
Create table #tblStrategy
(
IDINT identity,
IsActivechar(1),
IsDeletedchar(1),
Strategyvarchar(100),
AssetType_Codeint NULL
)
insert into #tblStrategy
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
(Don't look at the duplicate for now...because the table contains more columns..i just cut the other columns as it is not required for my requirement)
Function with CURSOR to concatenate the ID's:
CREATE FUNCTION [dbo].[fnGetStrategyId_k]
(
@StrategyNameVARCHAR(100)
,@AssetType_CodeINT
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @IdString VARCHAR(1000)
DECLARE @StrategyIdINT
DECLARE @IsGetDefault CHAR(1)
IF @AssetType_Code IS NULL OR (SELECT COUNT(*) FROM tblStrategy_K WHERE AssetType_Code = @AssetType_Code AND IsActive = 'Y' AND IsDeleted = 'N') < 1
SET @IsGetDefault = 'Y'
ELSE
SET @IsGetDefault = 'N'
SET @IdString = ''
IF @IsGetDefault = 'N'
BEGIN
DECLARE Strategy_Cursor CURSOR FOR
SELECT ID
FROM tblStrategy_K
WHERE UPPER(Strategy) = UPPER(@StrategyName)
AND AssetType_Code = @AssetType_Code
AND IsActive = 'Y'
AND IsDeleted = 'N'
END
ELSE IF @IsGetDefault = 'Y'
BEGIN
DECLARE Strategy_Cursor CURSOR FOR
SELECT ID
FROM tblStrategy_K
WHERE UPPER(Strategy) = UPPER(@StrategyName)
AND AssetType_Code IS NULL
AND IsActive = 'Y'
AND IsDeleted = 'N'
END
OPEN Strategy_Cursor
FETCH NEXT FROM Strategy_CursorINTO @StrategyId
SET @IdString = @IdString + CONVERT(VARCHAR(100), @StrategyId) + ','
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Strategy_CursorINTO @StrategyId
SET @IdString = @IdString + CONVERT(VARCHAR(100), @StrategyId) + ','
END
CLOSE Strategy_Cursor
DEALLOCATE Strategy_Cursor
-- cut last char as it is COMMA
SET @IdString = LEFT(@IdString, LEN(@IdString) - 1)
RETURN @IdString
END
Execution Plan:
Constant Scan Cost : 92%
Compute Scalar Cost : 8%
select cost :0%
My Query (SET BASED approach):
Declare @ID varchar(255), @AssetType_Code INT
Select @ID = ''
select @AssetType_Code = isnull(@AssetType_Code,'')
SELECT @ID = @ID + case when @ID = '' then '' else ',' end + CAST (ID AS VARCHAR(3))
FROM tblStrategy_K
WHERE UPPER(Strategy) = 'A3_Fund' --UPPER(@StrategyName)
AND isnull(AssetType_Code,'') = @AssetType_Code
AND IsActive = 'Y'
AND IsDeleted = 'N'
ExecutionPlan:
Clustered Index scan : 100%
Compute scalar cost : 0%
Compute scalar cost : 0%
select cost :0%
My questions are,
which query is best for good performance? ( AFAIK, second one)
what is the difference between 'Constant Scan' and 'Clustered Index scan' ? which one will give good performance?
is it advisable one to have 'Constant Scan' in the query plan?
Inputs are welcome!
karthik
May 24, 2010 at 4:08 am
http://scarydba.wordpress.com/2008/07/01/constant-scan-in-execution-plans/
Pls find the attached query plan.
karthik
May 24, 2010 at 4:23 am
Working with very large datasets (in one of the largest datawarehouse implementations based on SQLServer in the world) we found that from performance point of view it always better to do desired stuff in in-line SQL (doesn't metter how large and messy it is). If it's not possible than as CLR function. We achieved perfomance boost when we replaced all UDF functions we had with CLRs.
So, I would suggest to use :
1. In-line SQL
2. CLR
3. UDF
Cheers,
Me
May 24, 2010 at 4:28 am
elutin,
I am not getting your point.
you mean to say that using UDF is ok.
what do you mean by in-line SQL ?
i just little bit confused...what is the difference between in-line SQL and UDF ?
you mean
inline sql - Table valued functions ?
UDF - User defined scalar functions?
karthik
May 24, 2010 at 5:23 am
No.
What I mean by in-line SQL is the required functionality is implemented in the SELECT query itself.
Let say you want to format the date field somehow exotically.
Having it done in SELECT query (with whatever many CASE WHENs, CONVERTS and other SQL in-build functions), will perform faster than in CLR or UDF. Yes, using function looks more elegant.
Sometimes the desired functionality cannot be achieved in the SELECT and you will need to implement it as function, then, as I said before, CLR function will perform better than UDF.
May 24, 2010 at 5:30 am
I think the problem is that you apply a function (UPPER to be precise) to a column causing the clustered index scan, since the other columns you're using don't seem to be selective enough.
May 24, 2010 at 6:14 am
Even though both operators have the word SCAN in them, there is no real comparison between a Constant Scan and an Index/Cluster/Table Scan.
Usually, but not always, an Index/Cluster/Table Scan is indicative of poor performance, a badly structured index or a badly structured query. A constant scan is a construct within SQL Server that builds a location for storing data, usually a logical location, not a physical one like with a hash or a spool.
In your case, the multi-statement function is what the constant scan represents, and it is indicative of poor performance. UDF's are just dangerous, though useful, constructs. Instead of comparing operator costs between execution plans (a very poor way to estimate query costs), I'd suggest looking at execution time, I/O and CPU time. Just use the execution plans to understand what's happening within the stored procedure, not to compare performance between queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2010 at 8:50 am
Query without CURSOR ( SET BASED )
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 264 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Function with CURSOR :
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
It seems like function with CURSOR gives good peformance. I am confused...though we used CURSOR inside the function , it is taking less CPU time... But i just rewrite the code without using CURSOR ,i t is taking more CPU time. I am wondering how it is happening?
karthik
May 24, 2010 at 9:05 am
The main problem is, your set based solution is using that UDF instead of straight SQL. That's causing performance issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2010 at 9:06 am
you mean UPPER() function ?
karthik
May 24, 2010 at 9:10 am
Declare @ID varchar(255), @AssetType_Code INT
Select @ID = ''
--select @AssetType_Code = isnull(@AssetType_Code,'')
SELECT @ID = @ID + ',' + CAST (ID AS VARCHAR(3))
FROM tblStrategy_K
WHERE Strategy = 'A3_Fund' --UPPER(@StrategyName)
--AND isnull(AssetType_Code,'') = @AssetType_Code
AND IsActive = 'Y'
AND IsDeleted = 'N'
SELECT @ID
You are correct!
I just removed UPPER() function also did modifications in the query. Wow !
It took 1 ms only.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
karthik
May 24, 2010 at 10:42 pm
Declare @ID varchar(255), @AssetType_Code INT
Select @ID = ''
--select @AssetType_Code = @AssetType_Code
SELECT @ID = @ID + ',' + CAST (ID AS VARCHAR(3))
FROM tblStrategy_k
WHERE Strategy = 'A3_fund' --UPPER(@StrategyName)
--AND (AssetType_Code = @AssetType_Code or AssetType_Code IS NULL)
AND IsActive = 'Y'
AND IsDeleted = 'N'
SELECT substring(@ID,2,len(@ID))
QL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
If I include AND (AssetType_Code = @AssetType_Code or AssetType_Code IS NULL) in the query, then the CPU time is
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 236 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
How should I handle this ?
karthik
May 24, 2010 at 11:43 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2010 at 1:49 am
Gail,
I tried with 'DYNAMIC SQL'. But the ecexution time still remain same.
Declare @ID varchar(255), @AssetType_Code INT,@SQL varchar(255),@WHERE varchar(255)
Select @ID = ''
--select @AssetType_Code = isnull(@AssetType_Code,'')
SELECT @sql = 'Declare @ID varchar(255) Select @ID = '''' SELECT @ID = @ID + ' + ''', ''' + ' + CAST (ID AS VARCHAR(3))
FROM tblStrategy_k
WHERE Strategy =''A3_Fund'' AND IsActive = ''Y'' AND IsDeleted = ''N''' --UPPER(@StrategyName)
IF @AssetType_Code IS NULL
BEGIN
SELECT @WHERE = 'AND AssetType_CodeIS NULL' + ' SELECT substring(@ID,2,len(@ID))'
END
ELSE
BEGIN
SELECT @WHERE = 'AND AssetType_Code= ' + @AssetType_Code + 'SELECT substring(@ID,2,len(@ID))'
END
print @sql+@WHERE
exec (@SQL+@WHERE)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Declare @ID varchar(255) Select @ID = '' SELECT @ID = @ID + ', ' + CAST (ID AS VARCHAR(3))
FROM tblStrategy_k
WHERE Strategy ='A3_Fund' AND IsActive = 'Y' AND IsDeleted = 'N'AND AssetType_CodeIS NULL SELECT substring(@ID,2,len(@ID))
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'tblStrategy_K'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 242 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 244 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
karthik
May 25, 2010 at 2:50 am
Karthik,
Grant Fritchey (5/24/2010)
In your case, the multi-statement function is what the constant scan represents, and it is indicative of poor performance.
The Constant Scan operator is an in-memory table of constants. It can have zero or more rows.
In this case, it provides the two scalars 'A1_Fund' and NULL.
The multi-statement user-defined function is implemented by the Compute Scalar.
UDF's are just dangerous, though useful, constructs.
This is broadly true for T-SQL scalar and multi-statement user-defined functions, especially those that do data access, as in this example.
The problem is that they are slow to call, and are always called once per row, much like a cursor.
In-line table-valued functions would be better named 'parameterised views' and are typically very efficient and useful.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply