January 16, 2009 at 12:22 pm
GilaMonster (1/16/2009)
Jeff Moden (1/16/2009)
I'm trying to be as nice as pie... I don't understand why people post code and ask which will be faster... that's pretty lazy...Cause it's easier than setting up a test environment?
That would be true, huh? 😉 I just forgot that sometimes people don't have access to the large amounts of production data and they don't know how to build, say, a million rows of test data quickly.
Then, if you don't understand why, post the results, the code, and ask why. 😉
I wonder how many of the people who think that one or the other will be faster can explain why they say that. 😉
BWAA-HAA!!! I'm thinking that answer would be "more than you think, fewer than should, and a whole lot less than either took the time".
--Jeff Moden
January 16, 2009 at 12:38 pm
ok now that things are returning to normal do we think ISNULL performing better than COALESCE for checking a single operand is no co-incidence?
January 16, 2009 at 1:08 pm
leonp (1/16/2009)
ok now that things are returning to normal do we think ISNULL performing better than COALESCE for checking a single operand is no co-incidence?
I don't believe they intentionally did that, if that's what you mean. I think it's just a matter of computational requirements. If you wrote split code in T-SQL, I'm absolutely posititve that the code you wrote to spit two known (ie. guaranteed to be there) parameters would be much shorter and faster than something that was required to split up to "n" parameters.
--Jeff Moden
January 16, 2009 at 1:30 pm
leonp (1/16/2009)
ok now that things are returning to normal do we think ISNULL performing better than COALESCE for checking a single operand is no co-incidence?
It would be interesting to see what happens with a CASE as well. I may do that test.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 2:18 pm
Jeff Moden (1/16/2009)
leonp (1/16/2009)
ok now that things are returning to normal do we think ISNULL performing better than COALESCE for checking a single operand is no co-incidence?I don't believe they intentionally did that, if that's what you mean. I think it's just a matter of computational requirements. If you wrote split code in T-SQL, I'm absolutely posititve that the code you wrote to spit two known (ie. guaranteed to be there) parameters would be much shorter and faster than something that was required to split up to "n" parameters.
Actually in most 3GL & OO languages, you can write the longer one so that it is almost exactly the same speed as the shorter one (maybe 1% difference).
I suspect that the real cause of the performance difference between ISNULL and COALESCE has to do with their type-coercion differences. Even just having to check for it can be a computational PITA in many architectures.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 16, 2009 at 4:11 pm
Okay, I loaded up the following tables with 1,000,000 rows using RedGate SQLDataGenerator (I needed to play with it since I have it).
CREATE TABLE [dbo].[test_a]
(
[id] [int] IDENTITY(1, 1) NOT NULL,
[id_2] [int] NOT NULL,
[char_1] [char](5) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_test_a_id_2] ON [dbo].[test_a] ([id_2] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[test_b]
(
[id] [int] IDENTITY(1, 1)
NOT NULL,
[id_2] [int] NOT NULL,
[char_1] [char](5) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_test_b_id_2] ON [dbo].[test_b] ([id_2] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE FUNCTION dbo.fnCoalesce
(
@char_1 NVARCHAR(25)
)
RETURNS NVARCHAR(25)
--WITH ENCRYPTION|SCHEMABINDING, ...
AS
BEGIN
DECLARE @retval NVARCHAR(25)
SET @retval = CASE when @char_1 = '' THEN '9' ELSE ISNULL(@char_1, '*') end
RETURN @retval
END
Then I ran these queries:
SET STATISTICS IO ON;
SELECT
TA.id,
TA.id_2,
MIN(ISNULL(ISNULL(Nulllif(TB.char_1, ''), '9') '*')) AS char_1
FROM
dbo.test_a AS TA JOIN
dbo.test_b AS TB
ON TA.id = TB.id AND
TA.id_2 = TB.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2 ;
SELECT
TA.id,
TA.id_2,
MIN(CASE WHEN TB.char_1 = '' THEN '9'
ELSE COALESCE(TB.char_1, '*')
END) AS char_1
FROM
dbo.test_a AS TA JOIN
dbo.test_b AS TB
ON TA.id = TB.id AND
TA.id_2 = TB.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2 ;
SELECT
TA.id,
TA.id_2,
MIN(tb.char_1)
FROM
dbo.test_a AS TA JOIN
(
SELECT
id,
id_2,
CASE WHEN char_1 = '' THEN '9'
ELSE COALESCE(char_1, '*')
END AS char_1
FROM
dbo.test_b
) AS tb
ON TA.id = tb.id AND
TA.id_2 = tb.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2 ;
SELECT
TA.id,
TA.id_2,
MIN(CASE WHEN TB.char_1 = '' THEN '9'
ELSE IsNull(TB.char_1, '*')
END) AS char_1
FROM
dbo.test_a AS TA JOIN
dbo.test_b AS TB
ON TA.id = TB.id AND
TA.id_2 = TB.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2;
SELECT
TA.id,
TA.id_2,
MIN(CASE WHEN TB.char_1 = '' THEN '9'
ELSE CASE WHEN TB.Char_1 IS NULL THEN '*' End
END) AS char_1
FROM
dbo.test_a AS TA JOIN
dbo.test_b AS TB
ON TA.id = TB.id AND
TA.id_2 = TB.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2 ;
SELECT
TA.id,
TA.id_2,
MIN(tb.char_1)
FROM
dbo.test_a AS TA JOIN
(
SELECT
id,
id_2,
dbo.fnCoalesce(char_1) AS char_1
FROM
dbo.test_b
) AS tb
ON TA.id = tb.id AND
TA.id_2 = tb.id_2
WHERE
TA.id <= 100000
GROUP BY
TA.id,
TA.id_2 ;
SET STATISTICS IO Off
Here are the stats IO results
(100000 row(s) affected)
Table 'test_b'. Scan count 1, logical reads 742, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_a'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(100000 row(s) affected)
Table 'test_b'. Scan count 1, logical reads 742, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_a'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(100000 row(s) affected)
Table 'test_b'. Scan count 1, logical reads 742, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_a'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(100000 row(s) affected)
Table 'test_b'. Scan count 1, logical reads 742, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_a'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(100000 row(s) affected)
Table 'test_b'. Scan count 1, logical reads 742, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_a'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Attached are the execution plans in one file. Only the last one with the UDF has a different execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 4:34 pm
I must have something whacky going on with my machine... it gives me an XML error when I try to open the excecution plan.
I have no doubt that the IO on all of these will be similar. What would be interesting to see is what all that code turns out to be for CPU usage and duration. Only one you won't be able to measure with SET STATISTICS TIME ON will be the UDF one...
--Jeff Moden
January 16, 2009 at 4:46 pm
Probably having an issue with execution plan because I am running 2008 on my laptop now.
Here's the Statistics Time On Stats:
--IsNull(IsNull(Nullif()))
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 1085 ms.
--Case When Then Else Coalesce()
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 1323 ms.
--Derived table with Case When Then Else Coalesce()
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 1102 ms.
--Case When Then Else IsNull()
SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 1154 ms.
--Case When Then Else Case When Then Else
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 954 ms.
--Derived table with UDF
SQL Server Execution Times:
CPU time = 7192 ms, elapsed time = 10530 ms.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 7:32 pm
Thanks Jack... appreciate your time.
--Jeff Moden
February 3, 2009 at 12:07 pm
I tried the queries and it looks to me Query 2 is better compared to Query 1.
But again There is very slight differernce between Query 1 and 2. this is allmost negligible 🙂
Thanks -- Vj
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply