0

I want to talk about a scenario that we can use both nvl or case in the select statement. In PL-SQL using nvl will be easier that is true. But when it comes to query (in select statement)

will using nvl make it slower? I have a package that have loads of select statement with NVl. If I replace them with case will it work faster?

For example;

select  case sum(nvl(birikim,0)) when null then 0 else sum(nvl(birikim,0) end

instead of using this,

select nvl(sum(nvl(birikim, 0)), 0))
James Z
  • 12,209
  • 10
  • 24
  • 44
Atakan Günay
  • 27
  • 1
  • 3
  • `nvl(sum(birikim), 0)` is enough and test it. – forpas Jul 05 '21 at 07:12
  • 1
    Optimizing of the built-in function invocation is the last step you need to worry about. Is your query fast enough to see the difference? Is it processing single record? For test purpose you may spend some time to prepare appropriate setup with cold starts, buffer flush, multiple runs etc and compare plain select with no function at all and the same query with, for example, NVL. I bet timings will be statistically insignificant and impacted mostly by network and disk IO – astentx Jul 05 '21 at 08:17

2 Answers2

1

I doubt you will see a great performance increase. Built in functions are very optimized. On the other hand, any function that you use on SELECT statement will have impact in performance, unless your indexes take the function in consideration.

My suggestion: run an EXPLAIN PLAN on both queries to check the performance.

If there is not that much difference, take readability into consideration. Having NVL instead of CASE will make it more readable. Sometimes you have to balance performance with maintainability.

Rui Costa
  • 417
  • 2
  • 11
1

First, you are looking for a micro-optimization. The expense of a SQL query is usually in the data movement, not in the particular actions taken on the data within one row (unless you are working with large string or blobs or user-defined functions or something like that).

Second, running a function inside the SUM() is going to incur overhead for every row. That said, you may not be able to measure the effect unless you are measuring on a very large table.

Third, you don't need to worry about NULL values in the SUM(). So you can write your logic as:

select nvl(sum(birikim), 0)

Although I prefer coalesce() because it is the SQL Standard function for this purpose:

select coalesce(sum(birikim), 0)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786