Constant Scan vs Clustered index scan

Question

Post reply

Constant Scan vs Clustered index scan

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

karthikeyan-444867 (5/25/2010)
I tried with 'DYNAMIC SQL'. But the ecexution time still remain same.

You know the drill...'actual' execution plan, table definitions, indexes, sample data.

By the way, what exact version of SQL Server is this running against? For example SQL Server Enterprise x64 10.0.2775

Finally, can I just check that the code is not running in a database with a case-sensitive collation? The use of the UPPER function earlier makes me wonder.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 2

And try this:

SELECT @ID =

STUFF

(

SELECT ',' + CAST (ID AS VARCHAR(3))

FROM dbo.tblStrategy_k

WHERE Strategy = 'A3_fund'

AND (@AssetType_Code IS NULL OR AssetType_Code = @AssetType_Code)

AND IsActive = 'Y'

AND IsDeleted = 'N'

FOR XML PATH('')

)

, 1, 1, SPACE(0))

OPTION (RECOMPILE);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Paul White NZ (5/25/2010)
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

So it should be faster than cluster index scan. Right?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Paul White NZ (5/25/2010)
karthikeyan-444867 (5/25/2010)
I tried with 'DYNAMIC SQL'. But the ecexution time still remain same.
You know the drill...'actual' execution plan, table definitions, indexes, sample data.
By the way, what exact version of SQL Server is this running against? For example SQL Server Enterprise x64 10.0.2775
Finally, can I just check that the code is not running in a database with a case-sensitive collation? The use of the UPPER function earlier makes me wonder.

Microsoft SQL Server 2005 - 9.00.4220.00 (Intel X86)

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

The use of the UPPER function earlier makes me wonder.

???

karthik

Paul White SSC Guru Points: 150468 More actions · Answer 6

karthikeyan-444867 (5/25/2010)
So it should be faster than cluster index scan. Right?

The Constant Scan just provides the literal constants in your query to the UDF that comes next - in the Compute Scalar.

All the cost of the query is in the Compute Scalar.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 7

karthikeyan-444867 (5/25/2010)
Microsoft SQL Server 2005 - 9.00.4220.00 (Intel X86)

Shame. SQL Server 2008 SP1 CU5 is required to make the OPTION (RECOMPILE) trick fly.

Never mind. Try it anyway, and post the stuff we need.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 8

karthikeyan-444867 (5/25/2010)
???

Not wasting any words tonight, are you? 😀

In a case sensitive collation 'a' <> 'A' so developers often throw a LOWER or UPPER on both sides to get around that.

It sucks a bit as a solution for reasons already given in this thread (not SARGable) but that doesn't stop people using it.

That's why it made me wonder.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Paul White NZ (5/25/2010)
It sucks a bit as a solution for reasons already given in this thread (not SARGable) but that doesn't stop people using it.

Also doesn't stop people using it in case-insensitive collations, for no particular reason, just because they can.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Paul White NZ (5/25/2010)
karthikeyan-444867 (5/25/2010)
Microsoft SQL Server 2005 - 9.00.4220.00 (Intel X86)
Shame. SQL Server 2008 SP1 CU5 is required to make the OPTION (RECOMPILE) trick fly.
Never mind. Try it anyway, and post the stuff we need.

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.

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 = 241 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

Paul White SSC Guru Points: 150468 More actions · Answer 11

GilaMonster (5/25/2010)
Paul White NZ (5/25/2010)
It sucks a bit as a solution for reasons already given in this thread (not SARGable) but that doesn't stop people using it.
Also doesn't stop people using it in case-insensitive collations, for no particular reason, just because they can.

Sadly, that is very true.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 12

Thanks for the execution plan, shame about the table definition, sample data, and index definitions, but I'll take what I can get.

You might consider an index like the following:

CREATE NONCLUSTERED INDEX

[IX HFDMG_dbo.tblStrategy_K Strategy, IsActive, IsDeleted (AssetType_Code, ID)]

ON HFDMG_dbo.tblStrategy_K

(Strategy, IsActive, IsDeleted)

INCLUDE (AssetType_Code, ID);

Only you know whether it is a generally useful index or not.

edit: spolling

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi