Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm in a situation where I want to get the minimum value from of 6 columns.

I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.

The first method is to use a big case statement. Here's an example with 3 columns, based on the example in the link above. My case statement would be much longer since I will be looking at 6 columns.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2 
            Else Col3
            End As TheMin
From   MyTable

The second option is to use the UNION operator with multiple select statements. I would put this in an UDF that accepts an Id parameter.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

and

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

And the 3rd option I found was to use the UNPIVOT operator, which I didn't even know existed until just now

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from 
        cte 
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Because of the table size, and frequency in which this table is queried and updated, I am concerned about the performance impact these queries would have on the database.

This query will actually be used in a join to a table with a few million records, however the records returned will be reduced to around a hundred records at a time. It will get run many times throughout the day, and the 6 columns I am querying are frequently updated (they contain daily stats). I do not think there are any indexes on the 6 columns I am querying.

Which of these methods is better for performance when trying to get the minimum of multiple columns? Or is there another better method that I don't know of?

I am using SQL Server 2005

Sample Data & Results

If my data contained records like this:

Id    Col1    Col2    Col3    Col4    Col5    Col6
1        3       4       0       2       1       5
2        2       6      10       5       7       9
3        1       1       2       3       4       5
4        9       5       4       6       8       9

The end result should be

Id    Value
1        0
2        2
3        1
4        4
share|improve this question
    
Since this is (probably) going to have to be run for every row, you may consider writing a UDF to handle any number of inputs - IN DB2, the MIN() function actually allows this behavior (ie - when given multiple arguments, it doesn't act as an aggregate, it finds the minimum of the given columns for the row). –  Clockwork-Muse Jul 26 '12 at 15:55
1  
Your union all method is wrong.It will give you just one value but I am sure you are expecting one value per row.Thus you have to add the id column as well in all select of union all and then group by id and I am sure it should be quite fast atleast better than unpivot and your unpivt query doesnt need any extra join.. –  Gulli Meel Jul 26 '12 at 21:02
    
@GulliMeel I was wondering if someone would catch that :) For testing, I put the UNION query in a UDF that accepted the Id field as a parameter, and added a WHERE clause to the query –  Rachel Jul 26 '12 at 22:15

5 Answers 5

I guess that the first option is fastest (although it does not look very slick from programming perspective!). This is because It deals with exactly N rows (where N is the table size) and has to do no search or sort like method 2 or 3.

A test with large sample should prove the point.

As yet another option to consider (as if you need more!), is to create a materialized view over your table. if your table size is in 100s of thousands or more. This way, the min value is calculated while the row is changed and the entire table would not have to be processed with every query. In SQL Server, materialized views are called Indexed Views

share|improve this answer
    
Number of rows scanned are less but look at the number of manual computation (which means CPU time and which in turn means elapsed time) in case statement there are max 5 computations per row and min 2 computation and these are manual and are not optimized.Whereas sorts and min done by database engines are optimized using various sort techniques/hashing techniques and thus it is quite possible that 2nd and 3rd are fast.. Also, best way is to have a computed persisted column based on case statement which will make it much easier while querying. –  Gulli Meel Jul 26 '12 at 20:56
    
@GulliMeel, thanks for your comment. If we have C columns, the number of comparisons are C-1. For N rows, we need (N*C)-N=N(C-1) comparisons. if we have 3 columns and 1000,000 rows, we need 2000,000 comparisons. If we assume a quick sort runs on O(n log n) = k*1000,000*log(1000,000)=k*6000,000 (for some positive integer k). So k*6000,000 can't ever be <= 2000,000. The assumption here is that the database is using an algorithm similar to QuickSort, and this is not true of course. However, the difference is significant. Your theory could be correct if parallel sorting is used. –  Emmad Kareem Jul 27 '12 at 2:54
    
Look at her case statement she is not doing 2 computation..She in some cases doing 5 computations.I am not sure what database is using it could be using hashing techniques which could be fatser than sorting techniques.Or using efficient sort techniques. –  Gulli Meel Jul 27 '12 at 4:46
    
@GulliMeel, I checked again and you are correct. –  Emmad Kareem Jul 27 '12 at 15:35

Don't know about what is fastest but you could try something like this.

declare @T table
(
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T values(1, 2, 3, 4, 5, 6)
insert into @T values(2, 3, 1, 4, 5, 6)

select T4.ColName, T4.ColValue
from @T as T1
  cross apply (
                select T3.ColValue, T3.ColName
                from (
                       select row_number() over(order by T2.ColValue) as rn,
                              T2.ColValue,
                              T2.ColName
                       from (
                              select T1.Col1, 'Col1' union all
                              select T1.Col2, 'Col2' union all
                              select T1.Col3, 'Col3' union all
                              select T1.Col4, 'Col4' union all
                              select T1.Col5, 'Col5' union all
                              select T1.Col6, 'Col6'
                            ) as T2(ColValue, ColName)
                     ) as T3
                where T3.rn = 1
              ) as T4

Result:

ColName ColValue
------- -----------
Col1    1
Col3    1

Update
If you are not interested in what column has the min value you can use this instead.

declare @T table
(
  Id int,
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T
select 1,        3,       4,       0,       2,       1,       5 union all
select 2,        2,       6,      10,       5,       7,       9 union all
select 3,        1,       1,       2,       3,       4,       5 union all
select 4,        9,       5,       4,       6,       8,       9

select T.Id, (select min(T1.ColValue)
              from (
                      select T.Col1 union all
                      select T.Col2 union all
                      select T.Col3 union all
                      select T.Col4 union all
                      select T.Col5 union all
                      select T.Col6
                    ) as T1(ColValue)
             ) as ColValue
from @T as T

Update 2

A simplified unpivot query.

select Id, min(ColValue) as ColValue
from @T
unpivot (ColValue for Col in (Col1, Col2, Col3, Col4, Col5, Col6)) as U
group by Id
share|improve this answer
    
If the table has N rows and C columns, the UNION will produce N * C rows for each query. That, in theory, can't be faster than scanning all the N rows of the original table as in 1st option. –  Emmad Kareem Jul 26 '12 at 14:25
    
I tested the first query against the other 3 methods outlined in the question, and it ran slower in most cases. (The exception was vs the UNION ALL query in large recordsets). I did like the way you returned the column name instead of just the lowest value though. The 2nd query is pretty much the same as my 2nd option (the UNION ALL one). I learned some new things about SQL syntax from your answer though, so thanks :) –  Rachel Jul 26 '12 at 15:25
    
@Rachel, thanks for the feedback. Your unpivot version could be simplified a bit. I understand that the queries you execute does not look exactly like the queries in your question but it might have an impact for you. I will ad that to my answer. It removes table scan from the query plan and that should be a good thing. –  Mikael Eriksson Jul 26 '12 at 15:28
    
@MikaelEriksson Thanks! The performance seems about the same, but I find the syntax much easier to read. There's still a lot about SQL I don't know :) –  Rachel Jul 26 '12 at 15:34

Add a persisted computed column that uses a CASE statement to do the logic you need.

The minimum value will then always be efficiently available when you need to do a join (or whatever else) based on that value.

share|improve this answer
1  
Calculated on insert you mean? If the primary usage of the table is reads with a very minor amount of inserts, sure. If What are we thinking if it has a high number of writes, or even just equal write/read ratio? –  jcolebrand Jul 26 '12 at 14:11
1  
@jcolebrand: It would be recomputed every time any of the source values change (INSERT/UPDATE/MERGE). I'm not saying this is necessarily the best solution for the workload, I merely offer it as a solution, just like the other answers. Only the OP can determine which is best for the workload. –  Jon Seigel Jul 26 '12 at 14:14
    
Aye, hence my clarification via comments, for future readers. I still think this is valid, depending on workload, which we can't possibly determine. –  jcolebrand Jul 26 '12 at 14:42
up vote 14 down vote accepted

I tested the performance of all 3 methods, and here's what I found:

  • 1 record: No noticeable difference
  • 10 records: No noticeable difference
  • 1,000 records: No noticeable difference
  • 10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
  • 100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
  • 500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

  • With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.

  • Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.

  • The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

Edit: I also ran some tests with the solution Mikael suggested, however it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

share|improve this answer
1  
The union all query you have is very different from the version in my answer. You are using the table for each union. Did you do that in your tests or was the test more like what I had? I actually don't understand how you can use your union all query to get the min value for a row. As it is it will combine all the values in the table. –  Mikael Eriksson Jul 26 '12 at 14:32
    
@MikaelEriksson I'm actually testing your method now. I'm fairly new to some of the syntax you've used, so it's taking me a bit longer to understand and build a test case for it. –  Rachel Jul 26 '12 at 14:48
    
Looking forward to see the results. I honestly have no idea how it will perform against the other methods. Please let me know if you want me to explain a bit about what the queries does. –  Mikael Eriksson Jul 26 '12 at 15:00
    
+1, for providing the answer. I find it hard to believe that option 3 with join and group by is faster than scanning the table once! –  Emmad Kareem Jul 26 '12 at 15:12
    
@EmmadKareem I'm not a dba, so I may not have optimized my tests and missed something. In addition, I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results :) –  Rachel Jul 26 '12 at 15:41

Your case statement is not efficient.You are doing 5 computation in worst case and 2 in best case whereas finding the minimu in n hsould do max n-1 computation.Thuse for each row on avg you are doing 3.5 computation instead of 2.Thus it is taking more cpu time and is slow.Thus try your tests again and use below case statemet. It is just using 2 computation per row and should be more efficient than unpivot and union alll

Select Id, 
       Case 
           When Col1 <= Col2 then case when Col1 <= Col3 Then Col1  else col3 end
            When  Col2 <= Col3 Then Col2  
            Else Col3 
            End As TheMin 
From   YourTableNameHere

Union all method is wrong in your case as you are getting the min value not per row but for whole table.Also, it wont be efficient as you are going to scan then same table 3 times when the size of table is small then IO diff ownt make much diff but for large table it will make lots of diff. Thus do not use that method.

Unpivot is good and give a try to manual unpivot as well by using cross join your table with (select 1 union all select 2 union all select 3).It should be as efficient as the unpivot.

Best solution would be having a computed persisted column if you do not have space issues.as it will add the size of the row by 4 bytes(i suppose you will have int type). Which in turn will increase the size of the table.

However, space and memory is issue in your system and CPU is not then do not make it persisted but use simple computed column using the case statement.It will make the code simplerr.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.