February 1, 2015 at 9:48 pm
Hello everybody,
I have a task to be accomplished at work and would like to ask you a question about it.
To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.
example:
create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from vCommunications
alternatively, this can also be achieved by the following:
create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from vCommunications
Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.
Your insights on this will be much appreciated. Look forward to hearing from you all.
Cheers,
Shadab Khan.
February 1, 2015 at 10:00 pm
Performance of both will be same. I tested both queries on my test database. I got same execution plan for both ISNULL and Case statement
February 1, 2015 at 11:08 pm
Quick thought, isnull is implemented as a case statement under the bonnet/hood, performance tends to be the same for equal statements.
😎
February 1, 2015 at 11:26 pm
shadabkhan87 (2/1/2015)
Hello everybody,I have a task to be accomplished at work and would like to ask you a question about it.
To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.
example:
create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from vCommunications
alternatively, this can also be achieved by the following:
create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from vCommunications
Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.
Your insights on this will be much appreciated. Look forward to hearing from you all.
Cheers,
Shadab Khan.
The expressions are not the same.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2015 at 12:02 am
FYI you are missing out the COALESCE function, which you can consider a recursive ISNULL.
Personally I prefer Isnull and Coalesce over a Case expression to accomplish the same functionality.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2015 at 1:06 am
Bhushan Kulkarni (2/1/2015)
Performance of both will be same. I tested both queries on my test database. I got same execution plan for both ISNULL and Case statement
I agree. Even if you happen to notice some differences they would be tiny and they would be most likely due to cached pages or plan recreation or something else.
Igor Micev,My blog: www.igormicev.com
February 2, 2015 at 3:53 am
I think this is down to clarity of code versus actual functional differences. I'd go with the ISNULL and/or COALESCE over a CASE statement.
But, as with anything, test it yourself. See if there are differences in the execution plans. Capture query metrics using extended events and see if you spot differences in the amount of I/O or the execution time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2015 at 4:06 am
I'd expect them to pretty much identical in terms of query speed, but this is true:
ChrisM@home (2/1/2015)
The expressions are not the same.
The ISNULL version will only return 'Automatically Filed' if the comm_url field happens to be that. Given it's name I'd be surprised if it did.
February 2, 2015 at 6:30 am
Bhushan Kulkarni (2/1/2015)
Performance of both will be same. I tested both queries on my test database. I got same execution plan for both ISNULL and Case statement
If you look at the properties sheet of the clustered index scan in each case, you will see that the plans are not identical. The queries are functionally the same though. Neither is sargable and there is a sargable logical equivalent. Putting the expressions into the WHERE clause:
DROP TABLE #Temp
SELECT *
INTO #Temp
FROM (
SELECT n = CAST(NULL AS INT)
UNION ALL
SELECT TOP(199999) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM sys.columns a, sys.columns b
) d
CREATE CLUSTERED INDEX cx_n ON #Temp (n)
SELECT n
FROM #Temp WHERE 0 = ISNULL(n,0)
SELECT n
FROM #Temp WHERE 0 = CASE WHEN n IS NULL THEN 0 ELSE n END
-- this IS sargable
SELECT n
FROM #Temp WHERE n IS NULL OR n = 0
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
February 2, 2015 at 7:45 am
The expressions are not the same.
I agree, but no one has addressed that. The CASE statement says if null do x if not do y. The other says if null do x. There is no y. Presumably the original values are desired instead. But in any case, they are not the same.
February 3, 2015 at 8:25 am
Heh, heh, sounds like a Yoda quote. "Do or do not, there is no y". Perhaps in the original poster's system, field [comm_url] is always either null or 'Automatically Filed', in which case they would behave the same, but it looks extremely unlikely given the field name.
February 4, 2015 at 10:07 am
Try all three methods (CASE, ISNULL, and COALESCE) and see which is faster in your specific scenario. If you're encountering performance issues, then it's probably the sargability of the query itself, and not the computation of the 'Filing' column.
I don't know if this helps, but I was once tasked with optimizing the performance of a 200+ user call center application. Specifically there was this one screen that took about 15 seconds to refresh on a good day, and periodically could take more than a minute. The stored procedure was driven by something like a @RunDate parameter, and it only returned data for the previous day, so actually the results should have always been static. However, because of the way the application workflow was designed, the users were constantly navigating away from the screen and then returning back to it, and each time the dataset would be refreshed by another procedure call.
Of course, the best solution would have been for the application to cache the dataset and re-use it, but that was outside my area, and executive management perceived as a database performance issue. So I coded the stored procedure to cache the resultset to a table keyed on RunDate. If @RunDate was before the current work day, then the same @RunDate input would always return the same cached resultset. Once implemented, the data refresh for the screen was virtually instantaneous, and there was never a complaint about it again.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply