Home Is this statement quicker than the previous?
Reply: 1

Is this statement quicker than the previous?

Lynchie Published in 2018-02-13 12:07:35Z

I am running through some old code if I changed the logic of this CASE statement:

                CASE WHEN ClaimNo.ClaimNo IS NULL THEN '0'
                     WHEN ClaimNo.ClaimNo = 1 THEN '1'
                     WHEN ClaimNo.ClaimNo = 2 THEN '2'
                     WHEN ClaimNo.ClaimNo = 3 THEN '3'
                     WHEN ClaimNo.ClaimNo = 4 THEN '4'
                     ELSE '5+'
                END AS ClaimNo ,

If I changed it to:

                CASE WHEN ClaimNo.ClaimNo >= 5 THEN '5+'
                ELSE COALESCE(ClaimNo.ClaimNo,0) END 'ClaimNo' ,

Would the statement technically be quicker? Its obviously a lot shorter as a statement and appears that it wouldn't run as many statements to obtain the same result.

Gordon Linoff
Gordon Linoff Reply to 2018-02-13 12:13:00Z

These are not the same! The case expression returns one type and in this case you want the type to be a string (because '5+' is a string). However, mixing strings and integers in the wheres will result in a type conversion error.

Which is faster depends on the distribution of the data. If most of the data consists of 5 or more, then the second method would be faster . . . and work if written as:

(CASE WHEN ClaimNo.ClaimNo >= 5 THEN '5+'
      ELSE CAST(COALESCE(ClaimNo.ClaimNo, 0) as VARCHAR(255))
 END) as ClaimNo,

In fact, there is only one comparison, so from the perspective of doing the comparisons it will be faster.

The next question is whether the conversion from a number to a string is faster than the multiple comparisons with each value listed separately. Let me be honest: I do not know. And I have been concerned about query performance for a long time.

Why don't I know? Such micro-optimizations generally have basically no impact in the real world. You should use the version of the logic that works; readability and maintainability are also important. Of course performance is an issue, but the bit fiddling techniques that are important in other languages often have no place in SQL which is designed to handle much larger quantities of data, spread across multiple processors and disks.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.414104 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO