October 2, 2012 at 6:59 am
Hi All,
First off im a TSQL Novice...so help is needed and much appreciated
within several tables i have a job type code which describes the job type from which we can deduce what type of job (install or revisit),
Everytime i use one of these tables i write one looooonnng case statement...
I have been playing with the idea of using TSQL to do this for my but im coming up with errors every time...
this is what i have:
ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin
Return
Select case When jdt_jty_code ='ISCO' Then 'Install'
When jdt_jty_code ='ISSP' Then 'Install'
When jdt_jty_code ='IECO' Then 'Install'
When jdt_jty_code ='IECM' Then 'Install'
When jdt_jty_code ='IESP' Then 'Install'
When jdt_jty_code ='IEHD' Then 'Install'
When jdt_jty_code ='ISHD' Then 'Install'
When jdt_jty_code ='FRSI' Then 'Install'
When jdt_jty_code ='SB42' Then 'Service Call'
When jdt_jty_code ='SB4W' Then 'Service Call'
When jdt_jty_code ='HD42' Then 'Service Call'
When jdt_jty_code ='HD4W' Then 'Service Call'
When jdt_jty_code ='SA2C' Then 'Service Call'
When jdt_jty_code ='SA2W' Then 'Service Call'
When jdt_jty_code ='HD2C' Then 'Service Call'
When jdt_jty_code ='HD2W' Then 'Service Call'
When jdt_jty_code ='SNCO' Then 'Service Call'
Else 'UNKNOWN'
END
END
Thanks in advance
October 2, 2012 at 7:04 am
Select case
When jdt_jty_code IN ('ISCO','ISSP','IECO','IECM','IESP','IEHD','ISHD','FRSI') Then 'Install'
When jdt_jty_code IN ('SB42','SB4W','HD42','HD4W','SA2C','SA2W','HD2C','HD2W','SNCO') Then 'Service Call'
Else 'UNKNOWN' END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 7:05 am
You're getting an error because the CASE statement needs to be enclosed in parentheses.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
October 2, 2012 at 7:18 am
Roland Alexander STL (10/2/2012)
You're getting an error because the CASE statement needs to be enclosed in parentheses.
I'm unaware of this as a requirement in TSQL.
I think it's more likely because the @ has been dropped from the variables in the CASE construct.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 7:19 am
You're better off using inline table-valued functions. Here's an example:
ALTER FUNCTION [dbo].[iTVF_Tester]
(@jdt_jty_code varchar(50))
RETURNS TABLE AS RETURN
SELECT ReturnValue
FROM (VALUES
('ISCO','Install'),
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
) x (jdt_jty_code, ReturnValue)
WHERE jdt_jty_code = @jdt_jty_code
GO
SELECT * FROM [dbo].[iTVF_Tester] ('IESP')
iTVF's behave like parameterised views. Google or search this site to find the advantages over scalar UDF's and multi-statement TVF's.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 7:20 am
Ok so my case statement should always read @jdt_jty_code?
October 2, 2012 at 7:23 am
waqqas.zia (10/2/2012)
Ok so my case statement should always read @jdt_jty_code?
Yes - the @ tells SQL server that the object is a variable. Your scalar UDF works with changes;
create FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin
Return
(
Select case When @jdt_jty_code ='ISCO' Then 'Install'
When @jdt_jty_code ='ISSP' Then 'Install'
When @jdt_jty_code ='IECO' Then 'Install'
When @jdt_jty_code ='IECM' Then 'Install'
When @jdt_jty_code ='IESP' Then 'Install'
When @jdt_jty_code ='IEHD' Then 'Install'
When @jdt_jty_code ='ISHD' Then 'Install'
When @jdt_jty_code ='FRSI' Then 'Install'
When @jdt_jty_code ='SB42' Then 'Service Call'
When @jdt_jty_code ='SB4W' Then 'Service Call'
When @jdt_jty_code ='HD42' Then 'Service Call'
When @jdt_jty_code ='HD4W' Then 'Service Call'
When @jdt_jty_code ='SA2C' Then 'Service Call'
When @jdt_jty_code ='SA2W' Then 'Service Call'
When @jdt_jty_code ='HD2C' Then 'Service Call'
When @jdt_jty_code ='HD2W' Then 'Service Call'
When @jdt_jty_code ='SNCO' Then 'Service Call'
Else 'UNKNOWN'
END
)
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 7:42 am
I think I would create a lookup table, like this:
CREATE TABLE CodeActions (
Code char(4)
,TheActionvarchar(12)
)
INSERT INTO CodeActions (Code,TheAction)
VALUES
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
That way you don't have to play about with lengthy function definitions, nor rewrite them every time a code changes.
Your function becomes:
ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))
Returns varchar(50)
as
Begin
Return
SELECT
COALESCE(TheAction,'UNKNOWN')
FROM
CodeActions
WHERE
Code = @jdt_jty_code
END
which is so trivial that you probably don't even need a function for it. If you decide to keep it, bear in mind what Chris said about table-valued vs scalar functions. If you're going to use this function on large amounts of data, you'll take a performance hit if you leave it like it is.
John
October 2, 2012 at 4:11 pm
SQL Server is optimized to do table lookups, whereas CASE statements are comparatively very slow.
Therefore, I suggest using a lookup table, as suggested by others. You absolutely want to make the lookup code the unqiue clustering key to the table, to speed up SQL's table search. You can make it an actual PRIMARY KEY also, if you want to, but that's not required.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2012 at 1:50 am
CELKO (10/2/2012)
...NO! We hate UDFs; they are not declarative, do not optimize ...
Nonsense. The OP has explained that (s)he is a novice, Joe, and is willing to learn or wouldn't be contributing on this thread. Feeding new pupils with dogma is unethical and unprofessional. You're wrong about optimisation too - iTVF's are subbed into the plan just like a view. Look no further than the two articles by Paul White referenced in my signature block.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2012 at 1:51 am
John Mitchell-245523 (10/2/2012)
I think I would create a lookup table, like this:
CREATE TABLE CodeActions (Code char(4)
,TheActionvarchar(12)
)
INSERT INTO CodeActions (Code,TheAction)
VALUES
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
That way you don't have to play about with lengthy function definitions, nor rewrite them every time a code changes.
Your function becomes:
ALTER FUNCTION [dbo].[Tester] (@jdt_jty_code varchar(50))Returns varchar(50)
as
Begin
Return
SELECT
COALESCE(TheAction,'UNKNOWN')
FROM
CodeActions
WHERE
Code = @jdt_jty_code
END
which is so trivial that you probably don't even need a function for it. If you decide to keep it, bear in mind what Chris said about table-valued vs scalar functions. If you're going to use this function on large amounts of data, you'll take a performance hit if you leave it like it is.
John
+1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2012 at 1:53 am
Yep this is the method ive decieded to use so thumbs up to everyone for helping out 🙂
October 3, 2012 at 3:45 am
ScottPletcher (10/2/2012)
SQL Server is optimized to do table lookups, whereas CASE statements are comparatively very slow.Therefore, I suggest using a lookup table, as suggested by others. You absolutely want to make the lookup code the unqiue clustering key to the table, to speed up SQL's table search. You can make it an actual PRIMARY KEY also, if you want to, but that's not required.
That's interesting. I know from experience that CROSSTAB queries using CASE can be accelerated if the data is preaggregated before applying the aggregate across the CASE statements. I never really considered that CASE statements might significantly slow up a straightforward SELECT without aggregation. So here's a quick and dirty test:
-- crude test of cost of CASE
DROP TABLE #Temp
SELECT TOP 200000 rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
INTO #Temp
FROM sys.columns a, sys.columns b, sys.columns c
DROP TABLE #temp2
DROP TABLE #temp3
PRINT 'Simple SELECT'
SET STATISTICS TIME ON
SELECT rn, rn2 = CAST(rn AS VARCHAR(6))
INTO #temp2
FROM #Temp
SET STATISTICS TIME OFF
PRINT 'SELECT with 60 CASE alternatives'
SET STATISTICS TIME ON
SELECT rn,
rn2 = CASE rn
WHEN 1 THEN CAST(rn AS VARCHAR(6))
WHEN 2 THEN CAST(rn AS VARCHAR(6))
WHEN 3 THEN CAST(rn AS VARCHAR(6))
WHEN 4 THEN CAST(rn AS VARCHAR(6))
WHEN 5 THEN CAST(rn AS VARCHAR(6))
WHEN 6 THEN CAST(rn AS VARCHAR(6))
WHEN 7 THEN CAST(rn AS VARCHAR(6))
WHEN 8 THEN CAST(rn AS VARCHAR(6))
WHEN 9 THEN CAST(rn AS VARCHAR(6))
WHEN 10 THEN CAST(rn AS VARCHAR(6))
WHEN 11 THEN CAST(rn AS VARCHAR(6))
WHEN 12 THEN CAST(rn AS VARCHAR(6))
WHEN 13 THEN CAST(rn AS VARCHAR(6))
WHEN 14 THEN CAST(rn AS VARCHAR(6))
WHEN 15 THEN CAST(rn AS VARCHAR(6))
WHEN 16 THEN CAST(rn AS VARCHAR(6))
WHEN 17 THEN CAST(rn AS VARCHAR(6))
WHEN 18 THEN CAST(rn AS VARCHAR(6))
WHEN 19 THEN CAST(rn AS VARCHAR(6))
WHEN 20 THEN CAST(rn AS VARCHAR(6))
WHEN 100 THEN CAST(rn AS VARCHAR(6))
WHEN 200 THEN CAST(rn AS VARCHAR(6))
WHEN 300 THEN CAST(rn AS VARCHAR(6))
WHEN 400 THEN CAST(rn AS VARCHAR(6))
WHEN 500 THEN CAST(rn AS VARCHAR(6))
WHEN 600 THEN CAST(rn AS VARCHAR(6))
WHEN 700 THEN CAST(rn AS VARCHAR(6))
WHEN 800 THEN CAST(rn AS VARCHAR(6))
WHEN 900 THEN CAST(rn AS VARCHAR(6))
WHEN 1000 THEN CAST(rn AS VARCHAR(6))
WHEN 1100 THEN CAST(rn AS VARCHAR(6))
WHEN 1200 THEN CAST(rn AS VARCHAR(6))
WHEN 1300 THEN CAST(rn AS VARCHAR(6))
WHEN 1400 THEN CAST(rn AS VARCHAR(6))
WHEN 1500 THEN CAST(rn AS VARCHAR(6))
WHEN 1600 THEN CAST(rn AS VARCHAR(6))
WHEN 1700 THEN CAST(rn AS VARCHAR(6))
WHEN 1800 THEN CAST(rn AS VARCHAR(6))
WHEN 1900 THEN CAST(rn AS VARCHAR(6))
WHEN 2000 THEN CAST(rn AS VARCHAR(6))
WHEN 10000 THEN CAST(rn AS VARCHAR(6))
WHEN 20000 THEN CAST(rn AS VARCHAR(6))
WHEN 30000 THEN CAST(rn AS VARCHAR(6))
WHEN 40000 THEN CAST(rn AS VARCHAR(6))
WHEN 50000 THEN CAST(rn AS VARCHAR(6))
WHEN 60000 THEN CAST(rn AS VARCHAR(6))
WHEN 70000 THEN CAST(rn AS VARCHAR(6))
WHEN 80000 THEN CAST(rn AS VARCHAR(6))
WHEN 90000 THEN CAST(rn AS VARCHAR(6))
WHEN 100000 THEN CAST(rn AS VARCHAR(6))
WHEN 110000 THEN CAST(rn AS VARCHAR(6))
WHEN 120000 THEN CAST(rn AS VARCHAR(6))
WHEN 130000 THEN CAST(rn AS VARCHAR(6))
WHEN 140000 THEN CAST(rn AS VARCHAR(6))
WHEN 150000 THEN CAST(rn AS VARCHAR(6))
WHEN 160000 THEN CAST(rn AS VARCHAR(6))
WHEN 170000 THEN CAST(rn AS VARCHAR(6))
WHEN 180000 THEN CAST(rn AS VARCHAR(6))
WHEN 190000 THEN CAST(rn AS VARCHAR(6))
WHEN 200000 THEN CAST(rn AS VARCHAR(6))
ELSE
CAST(rn AS VARCHAR(6))
END
INTO #temp3
FROM #Temp
SET STATISTICS TIME OFF
I ran the statements a number of times, returning the results to screen or to #temp table from my local instance. Here are the average values for 10 runs, returning to #temp tables:
Simple SELECT
SQL Server Execution Times:
CPU time = 123.4 ms, elapsed time = 123.2 ms.
(200000 row(s) affected)
SELECT with 60 CASE alternatives
SQL Server Execution Times:
CPU time = 112.3 ms, elapsed time = 160.0 ms.
(200000 row(s) affected)
Adding loads of CASE alternatives doesn't appear to change the CPU time very much at all but appears to have a quite significant effect on the elapsed time – increasing it by about 30%.
I switched to using startdatetime/enddatetime, like this:
DECLARE @Startdate DATETIME
PRINT 'Simple SELECT'
SET @Startdate = GETDATE()
--SET STATISTICS TIME ON
SELECT rn, rn2 = CAST(rn AS VARCHAR(6))
INTO #temp2
FROM #Temp
--SET STATISTICS TIME OFF
PRINT DATEDIFF(MILLISECOND,@Startdate,GETDATE()) --@MSDuration
and here are the averaged results from 10 runs:
Simple SELECT
(200000 row(s) affected)
136.3
SELECT with 60 CASE alternatives
(200000 row(s) affected)
159.7
The difference this time is a little less than 20%. The conclusion I’m going to take home from this is – “you can add quite a few options into a CASE statement before it will significantly affect the run time of your query”. What it doesn’t do is account for the relative cost of each option evaluated, i.e. what happens if the CASE options are computationally much more expensive than casting an INT to a VARCHAR? I think you can guess 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2012 at 8:57 am
ChrisM@Work (10/3/2012)
ScottPletcher (10/2/2012)
SQL Server is optimized to do table lookups, whereas CASE statements are comparatively very slow.Therefore, I suggest using a lookup table, as suggested by others. You absolutely want to make the lookup code the unqiue clustering key to the table, to speed up SQL's table search. You can make it an actual PRIMARY KEY also, if you want to, but that's not required.
That's interesting. I know from experience that CROSSTAB queries using CASE can be accelerated if the data is preaggregated before applying the aggregate across the CASE statements. I never really considered that CASE statements might significantly slow up a straightforward SELECT without aggregation. So here's a quick and dirty test:
-- crude test of cost of CASEDROP TABLE #Temp
SELECT TOP 200000 rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
INTO #Temp
FROM sys.columns a, sys.columns b, sys.columns c
DROP TABLE #temp2
DROP TABLE #temp3
PRINT 'Simple SELECT'
SET STATISTICS TIME ON
SELECT rn, rn2 = CAST(rn AS VARCHAR(6))
INTO #temp2
FROM #Temp
SET STATISTICS TIME OFF
PRINT 'SELECT with 60 CASE alternatives'
SET STATISTICS TIME ON
SELECT rn,
rn2 = CASE rn
WHEN 1 THEN CAST(rn AS VARCHAR(6))
WHEN 2 THEN CAST(rn AS VARCHAR(6))
WHEN 3 THEN CAST(rn AS VARCHAR(6))
WHEN 4 THEN CAST(rn AS VARCHAR(6))
WHEN 5 THEN CAST(rn AS VARCHAR(6))
WHEN 6 THEN CAST(rn AS VARCHAR(6))
WHEN 7 THEN CAST(rn AS VARCHAR(6))
WHEN 8 THEN CAST(rn AS VARCHAR(6))
WHEN 9 THEN CAST(rn AS VARCHAR(6))
WHEN 10 THEN CAST(rn AS VARCHAR(6))
WHEN 11 THEN CAST(rn AS VARCHAR(6))
WHEN 12 THEN CAST(rn AS VARCHAR(6))
WHEN 13 THEN CAST(rn AS VARCHAR(6))
WHEN 14 THEN CAST(rn AS VARCHAR(6))
WHEN 15 THEN CAST(rn AS VARCHAR(6))
WHEN 16 THEN CAST(rn AS VARCHAR(6))
WHEN 17 THEN CAST(rn AS VARCHAR(6))
WHEN 18 THEN CAST(rn AS VARCHAR(6))
WHEN 19 THEN CAST(rn AS VARCHAR(6))
WHEN 20 THEN CAST(rn AS VARCHAR(6))
WHEN 100 THEN CAST(rn AS VARCHAR(6))
WHEN 200 THEN CAST(rn AS VARCHAR(6))
WHEN 300 THEN CAST(rn AS VARCHAR(6))
WHEN 400 THEN CAST(rn AS VARCHAR(6))
WHEN 500 THEN CAST(rn AS VARCHAR(6))
WHEN 600 THEN CAST(rn AS VARCHAR(6))
WHEN 700 THEN CAST(rn AS VARCHAR(6))
WHEN 800 THEN CAST(rn AS VARCHAR(6))
WHEN 900 THEN CAST(rn AS VARCHAR(6))
WHEN 1000 THEN CAST(rn AS VARCHAR(6))
WHEN 1100 THEN CAST(rn AS VARCHAR(6))
WHEN 1200 THEN CAST(rn AS VARCHAR(6))
WHEN 1300 THEN CAST(rn AS VARCHAR(6))
WHEN 1400 THEN CAST(rn AS VARCHAR(6))
WHEN 1500 THEN CAST(rn AS VARCHAR(6))
WHEN 1600 THEN CAST(rn AS VARCHAR(6))
WHEN 1700 THEN CAST(rn AS VARCHAR(6))
WHEN 1800 THEN CAST(rn AS VARCHAR(6))
WHEN 1900 THEN CAST(rn AS VARCHAR(6))
WHEN 2000 THEN CAST(rn AS VARCHAR(6))
WHEN 10000 THEN CAST(rn AS VARCHAR(6))
WHEN 20000 THEN CAST(rn AS VARCHAR(6))
WHEN 30000 THEN CAST(rn AS VARCHAR(6))
WHEN 40000 THEN CAST(rn AS VARCHAR(6))
WHEN 50000 THEN CAST(rn AS VARCHAR(6))
WHEN 60000 THEN CAST(rn AS VARCHAR(6))
WHEN 70000 THEN CAST(rn AS VARCHAR(6))
WHEN 80000 THEN CAST(rn AS VARCHAR(6))
WHEN 90000 THEN CAST(rn AS VARCHAR(6))
WHEN 100000 THEN CAST(rn AS VARCHAR(6))
WHEN 110000 THEN CAST(rn AS VARCHAR(6))
WHEN 120000 THEN CAST(rn AS VARCHAR(6))
WHEN 130000 THEN CAST(rn AS VARCHAR(6))
WHEN 140000 THEN CAST(rn AS VARCHAR(6))
WHEN 150000 THEN CAST(rn AS VARCHAR(6))
WHEN 160000 THEN CAST(rn AS VARCHAR(6))
WHEN 170000 THEN CAST(rn AS VARCHAR(6))
WHEN 180000 THEN CAST(rn AS VARCHAR(6))
WHEN 190000 THEN CAST(rn AS VARCHAR(6))
WHEN 200000 THEN CAST(rn AS VARCHAR(6))
ELSE
CAST(rn AS VARCHAR(6))
END
INTO #temp3
FROM #Temp
SET STATISTICS TIME OFF
I ran the statements a number of times, returning the results to screen or to #temp table from my local instance. Here are the average values for 10 runs, returning to #temp tables:
Simple SELECTSQL Server Execution Times:
CPU time = 123.4 ms, elapsed time = 123.2 ms.
(200000 row(s) affected)
SELECT with 60 CASE alternatives
SQL Server Execution Times:
CPU time = 112.3 ms, elapsed time = 160.0 ms.
(200000 row(s) affected)
Adding loads of CASE alternatives doesn't appear to change the CPU time very much at all but appears to have a quite significant effect on the elapsed time – increasing it by about 30%.
I switched to using startdatetime/enddatetime, like this:
DECLARE @Startdate DATETIMEPRINT 'Simple SELECT'
SET @Startdate = GETDATE()
--SET STATISTICS TIME ON
SELECT rn, rn2 = CAST(rn AS VARCHAR(6))
INTO #temp2
FROM #Temp
--SET STATISTICS TIME OFF
PRINT DATEDIFF(MILLISECOND,@Startdate,GETDATE()) --@MSDuration
and here are the averaged results from 10 runs:
Simple SELECT(200000 row(s) affected)
136.3
SELECT with 60 CASE alternatives
(200000 row(s) affected)
159.7
The difference this time is a little less than 20%. The conclusion I’m going to take home from this is – “you can add quite a few options into a CASE statement before it will significantly affect the run time of your query”. What it doesn’t do is account for the relative cost of each option evaluated, i.e. what happens if the CASE options are computationally much more expensive than casting an INT to a VARCHAR? I think you can guess 😉
I consider 20-30% relatively much slower, particularly given how basic the task was.
A 5 min query then takes ~6+ mins instead. Not tragic, obviously, but significant.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2012 at 9:04 am
ScottPletcher (10/3/2012)
<<snip>>
I consider 20-30% relatively much slower, particularly given how basic the task was.
A 5 min query then takes ~6+ mins instead. Not tragic, obviously, but significant.
The figures are certainly worth remembering (20 computationally simple CASE options could slow your code by as much as 10%) - but in any case thanks, Scott, for the stimulation to code up the test.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply