March 9, 2016 at 12:51 am
case when AR=1 then ' Calling'
when AR=2 then 'Closed'
when AR=3 then 'Completed'
when AR=5 then 'Resend to A'
when AR=6 then 'Resend to C'
when AR=7 then 'At ACompleted'
when AR=8 then 'S Completed'
when AR=9 then 'Re Completed'
when AR=11 then 'ReA Audit Completed'
when AR=12 then 'ReC Completed'
when AR=13 then 'Calli Completed'
when AR=14 then 'A Completed'
when AR=15 then 'A Completed'
End,"SubProcess"=
case when SubProcess is null then ''
when SubProcess=1 then 'D'
when SubProcess=2 then 'U'
when SubProcess=3 then 'C'
when SubProcess=4 then 'M'
when SubProcess=5 then 'Me'
when SubProcess=6 then 'Rej'
when SubProcess=7 then 'App'
when SubProcess=8 then 'AR'
end
Looking for some better code on performance prespective
March 9, 2016 at 2:14 am
ganapathy.arvindan (3/9/2016)
case when AR=1 then ' Calling'when AR=2 then 'Closed'
when AR=3 then 'Completed'
when AR=5 then 'Resend to A'
when AR=6 then 'Resend to C'
when AR=7 then 'At ACompleted'
when AR=8 then 'S Completed'
when AR=9 then 'Re Completed'
when AR=11 then 'ReA Audit Completed'
when AR=12 then 'ReC Completed'
when AR=13 then 'Calli Completed'
when AR=14 then 'A Completed'
when AR=15 then 'A Completed'
End,"SubProcess"=
case when SubProcess is null then ''
when SubProcess=1 then 'D'
when SubProcess=2 then 'U'
when SubProcess=3 then 'C'
when SubProcess=4 then 'M'
when SubProcess=5 then 'Me'
when SubProcess=6 then 'Rej'
when SubProcess=7 then 'App'
when SubProcess=8 then 'AR'
end
Looking for some better code on performance prespective
Use lookup tables, #temporary or permanent.
Speaking very generally, CASE blocks like this aren't a performance problem if they are in the SELECT part of the query - which these look like they might be.
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
March 9, 2016 at 2:32 am
Thanks !!! Same Case statement(same code) used in 4 select statements in single SP so i thought instead of writing 4 times,whether there is anyway i can write this case statement code single time and use it wherever required, say like CTE will help it? please help me in this
March 9, 2016 at 6:01 am
ganapathy.arvindan (3/9/2016)
Thanks !!! Same Case statement(same code) used in 4 select statements in single SP so i thought instead of writing 4 times,whether there is anyway i can write this case statement code single time and use it wherever required, say like CTE will help it? please help me in this
Not a CTE, but an iTVF would do this for you.
But, as Chris suggested, this is data, not code. Hard-coding lookup data like this is bad practice. It belongs in two lookup tables.
1,Calling
2,Closed
etc
and
1,D
2,U
etc
Once you have that set up, a simple join is all that is required to retrieve your data and your code instantly becomes more readable and easier to maintain.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 9, 2016 at 6:22 am
I'm going to STRONGLY disagree with any notion anyone might have that putting this lookup stuff into a table (of any flavor) and joining to it in in a query is going to be more efficient. There is NO FASTER WAY to do this operation than a CASE statement, and the math isn't even close.
Note that the above says nothing about right/wrong/best. If you DO this type of operation then managing it becomes part of your coding process (and in fact you run the possibility of getting the wrong answer out of the query unless you ELSE it with a default that says "missed a match"), which is absolutely something to be taken into consideration. But over the years as a consultant I have done this type of hard-coding MANY times for my clients to make queries (usually very large data warehouse ones, occasionally blitzing-the-server-thousands-of-times-a-second tiny ones) to tweak out the absolute best performance possible.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 9, 2016 at 6:24 am
Phil Parkin (3/9/2016)
ganapathy.arvindan (3/9/2016)
Thanks !!! Same Case statement(same code) used in 4 select statements in single SP so i thought instead of writing 4 times,whether there is anyway i can write this case statement code single time and use it wherever required, say like CTE will help it? please help me in thisNot a CTE, but an iTVF would do this for you.
But, as Chris suggested, this is data, not code. Hard-coding lookup data like this is bad practice. It belongs in two lookup tables.
1,Calling
2,Closed
etc
and
1,D
2,U
etc
Once you have that set up, a simple join is all that is required to retrieve your data and your code instantly becomes more readable and easier to maintain.
Even if this data is only used in this stored procedure I'd be tempted to put it into permanent tables. Next choice would be #temp tables, and last would be something like this - which works fine with the number of rows in your script but gets ugly with larger data sets:
SELECT v1.Name, t.*
FROM MyTable t
LEFT JOIN (VALUES
(1,'Calling'),
(2,'Closed'),
(3,'Completed')
) v1 (AR, Name) ON v1.AR = t.AR
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
March 9, 2016 at 6:33 am
TheSQLGuru (3/9/2016)
I'm going to STRONGLY disagree with any notion anyone might have that putting this lookup stuff into a table (of any flavor) and joining to it in in a query is going to be more efficient. There is NO FASTER WAY to do this operation than a CASE statement, and the math isn't even close.Note that the above says nothing about right/wrong/best. If you DO this type of operation then managing it becomes part of your coding process (and in fact you run the possibility of getting the wrong answer out of the query unless you ELSE it with a default that says "missed a match"), which is absolutely something to be taken into consideration. But over the years as a consultant I have done this type of hard-coding MANY times for my clients to make queries (usually very large data warehouse ones, occasionally blitzing-the-server-thousands-of-times-a-second tiny ones) to tweak out the absolute best performance possible.
You are 'strongly disagreeing' with something which I neither said, nor intended to imply.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 9, 2016 at 7:24 am
Phil Parkin (3/9/2016)
TheSQLGuru (3/9/2016)
I'm going to STRONGLY disagree with any notion anyone might have that putting this lookup stuff into a table (of any flavor) and joining to it in in a query is going to be more efficient. There is NO FASTER WAY to do this operation than a CASE statement, and the math isn't even close.Note that the above says nothing about right/wrong/best. If you DO this type of operation then managing it becomes part of your coding process (and in fact you run the possibility of getting the wrong answer out of the query unless you ELSE it with a default that says "missed a match"), which is absolutely something to be taken into consideration. But over the years as a consultant I have done this type of hard-coding MANY times for my clients to make queries (usually very large data warehouse ones, occasionally blitzing-the-server-thousands-of-times-a-second tiny ones) to tweak out the absolute best performance possible.
You are 'strongly disagreeing' with something which I neither said, nor intended to imply.
Didn't say you did Phil.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 9, 2016 at 1:57 pm
If your 4 SQL statements were potentially part of a UNION or UNION all construct, you could push this on-the-fly lookup table into a WITH clause. (Not to make this example confusing, but there's also a UNION ALL in the WITH clause in order to construct the contents of the "lookup table", not to be confused with the potential 4 queries joined by UNION [ALL] in the main SQL, in your case):
SELECT *
FROM mv_movie;
name code_mv_genre_vv
------------------------------ ----------------
Galaxy Quest C
Man on Fire D
------------------------------------------
;with lookup_t AS (
select 'C' as code, 'Comedy' as description union all
select 'D' as code, 'Drama' as description
)
select m.name, lt.description
from mv_movie m
inner join lookup_t lt on (m.code_mv_genre_vv = lt.code)
name description
------------------------------ -----------
Galaxy Quest Comedy
Man on Fire Drama
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply