• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

sql server - Simple CASE Expression

hi experts
i am reading about "Simple CASE Expression"
and this: Point out that a JOIN to a reference table will be a better solution

i have two querys

--query 1
SELECT orderid, custid, orderdate,
      CASE empid
            WHEN 1 THEN 'Buck'
            WHEN 2 THEN 'Cameron'
            WHEN 3 THEN 'Davis'
            WHEN 4 THEN 'Dolgopyatova'
            WHEN 5 THEN 'Funk'
            WHEN 6 THEN 'King'
            WHEN 7 THEN 'Lew'
            WHEN 8 THEN 'Peled'
            WHEN 9 THEN 'Suurs'
            ELSE 'Unknown Sales Rep'
      END AS salesrep
FROM Sales.Orders;

--query 2
select orderid, custid, orderdate,e.firstname
from Sales.Orders o inner join
.[Employees] e
      on o.empid = e.empid

which is better?

i attached the execution plan and statistics io


(830 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(830 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employees'. 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.

what it is the difference between query cost 29% and 71%.
has the fewest percentage is better?
executionPLAN.png
0
enrique_aeo
Asked:
enrique_aeo
2 Solutions
 
lwadwellCommented:
query 2 should be a LEFT JOIN to implement the ELSE in the CASE of query 1.

Ignoring performance and explain plans etc ... query 2 is more flexible and future proof than a hard-coded look up.  I say this for a 'production' code type of scenario ... if the employee's ever changed, it is far preferable to add rows to a reference table than crack open the code and change it.  In this context ... query 2 is a better solution.

Considering performance and explain plans etc ... query 1 would most likely win every time.  However, if the reference table is small ... the execution time difference between the two would hardly be noticeable.
0
 
dthoms000Commented:
I agree with everything lwadell had to say. This is most likely a good learning opportunity for you as I detect you are new to relational database theory.

First ask yourself if you are willing to modify the query each time you add a new user to the employee table, You can see that this would become tedious. Relational db's allow us to define the relationships in our data within the database then write our queries once and as as the data changes get predictable results. You might look up relational database normalization to get some ideas on this. The second query is realyu the only way to go since the relationships are already defined in the db for you.

Your execution plan compared the two queries as run together the second query requiring a lot more overhead because it relied on the rules in your database to obtain results.  But as lwadell points out the execution time will probably be the same. Learning to analyze execution plans would require some knowledge of relational theory. Comparing these two scripts this way and making decisions based on the individual batch cost does would not really fit this situation. Hope this helps. You question shows you are on the right track to becoming sql knowledgeable
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now