Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Constant Scan vs Clustered index scan Expand / Collapse
Author
Message
Posted Monday, May 24, 2010 3:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
(
ID INT identity,
IsActive char(1),
IsDeleted char(1),
Strategy varchar(100),
AssetType_Code int 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]
(
@StrategyName VARCHAR(100)
,@AssetType_Code INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @IdString VARCHAR(1000)
DECLARE @StrategyId INT
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_Cursor INTO @StrategyId
SET @IdString = @IdString + CONVERT(VARCHAR(100), @StrategyId) + ','

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Strategy_Cursor INTO @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
Post #926694
Posted Monday, May 24, 2010 4:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
http://scarydba.wordpress.com/2008/07/01/constant-scan-in-execution-plans/

Pls find the attached query plan.


karthik

  Post Attachments 
My_Query.sqlplan (13 views, 12.73 KB)
cursor_query_plan.sqlplan (6 views, 2.81 KB)
Post #926697
Posted Monday, May 24, 2010 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
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



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #926704
Posted Monday, May 24, 2010 4:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #926707
Posted Monday, May 24, 2010 5:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #926738
Posted Monday, May 24, 2010 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #926741
Posted Monday, May 24, 2010 6:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 15,738, Visits: 28,146
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #926761
Posted Monday, May 24, 2010 8:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #926894
Posted Monday, May 24, 2010 9:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 15,738, Visits: 28,146
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #926901
Posted Monday, May 24, 2010 9:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
you mean UPPER() function ?

karthik
Post #926902
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse