Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
aleks2
Guest
Knyazev Alexey
aleks2
не оправдывает НЕЧИТАЕМОСТИ этой хрени.

более чем оправдывает


- Армяне лучше, чем грузины!
- Чем лучше?
- Чем грузины!!!

ЗЫ. Научись аргументировать, чтоле?
2 сен 14, 19:36    [16525507]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
aleks2
Guest
Wlr-l
Как вариант решения o-o:

declare @t table ( id int identity, a int, b int, c int )
insert into @t
values( 1, 4, 2 )
    , ( 7, 6, 9 )
    , ( 3, 0, 1 )
    , ( 3, 3, 1 )

select *
  from @t s
  cross apply (select max(v)
                 from (values (s.a), (s.b), (s.c)) t(v)
              ) x(max_);

Cross apply то нафега тут?
Шоб було?
2 сен 14, 19:41    [16525516]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
Я сравнил самый лучший вариант с NATIVE_COMPILATION и INMEMORY таблицей на SQL 2014.

Правда пришлось изменить условие задачи -- максимум записывается в поле таблицы.

Но IN-MEMEORY дает 2-3 раза выигрыш. Причём лучший результат на выражении без сравнений -- арифметикой.

  UPDATE dbo.test_table1
  SET MaxVal=  a - (a-b)  *  (((a - b)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-c)  *  (((MaxVal - c)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-d)  *  (((MaxVal - d)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-e)  *  (((MaxVal - e)  & 0x80000000) / 0x80000000);


1. Выражение CASE (любая версия)
Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 6 мс.

Время работы SQL Server:
Время ЦП = 6661 мс, затраченное время = 3769 мс.
2. Выражение внутри Native_Compilation

Время работы SQL Server:
Время ЦП = 1155 мс, затраченное время = 1162 мс.
3. Выражение внутри Native_Compilation 2

Время работы SQL Server:
Время ЦП = 1482 мс, затраченное время = 1480 мс.


+
set nocount on;
go

--Генерим 10 млн строк
if object_id ( N'dbo.generate_10000000', N'P' ) is not null
DROP procedure dbo.generate_10000000 
GO 
 
if object_id ( N'dbo.calcMax', N'P' ) is not null
DROP procedure dbo.calcMax 
GO 

 
if object_id ( N'dbo.test_table1', N'U' ) is not null
  drop table dbo.test_table1;
go
 
create table dbo.test_table1 ( id int NOT NULL  primary key nonclustered
                            , a int
                            , b int
                            , c int
                            , d int
                            , e int
							,MaxVal INT
                            ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) ;
go
 
create procedure dbo.generate_10000000 
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level=snapshot, language=N'us_english')

  declare @i int = 1000000
  while @i > 0
  begin
	insert into dbo.test_table1
	VALUES( @i
		 ,  ( @i * 101 ) % 10001
		 ,  ( @i * 103 ) % 10002
		 ,  ( @i * 107 ) % 10003
		 ,  ( @i * 109 ) % 10004
		 ,  ( @i * 113 ) % 10005
		 ,NULL
	);
    set @i -= 1
  end

end
go

EXEC dbo.generate_10000000 
GO 

if object_id ( N'dbo.test_table', N'U' ) is not null
  drop table dbo.test_table;
go

create table dbo.test_table ( id int NOT NULL primary key clustered
                            , a int
                            , b int
                            , c int
                            , d int
                            , e int
							,MaxVal Int
                            );
go

insert into dbo.test_table
SELECT * FROM dbo.test_table1
GO 

declare @val int;
 
print '1. Выражение CASE (любая версия)';
set statistics time on;

 UPDATE dbo.test_table
	SET MaxVal =  case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
                    then a
                    when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
                    then b
                    when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
                    then c
                    when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
                    then d
                    else e
                 end
  from dbo.test_table;
 
set statistics time off;

GO 

if object_id ( N'dbo.calcMax', N'P' ) is not null
DROP procedure dbo.calcMax 
GO 

 
create procedure dbo.calcMax
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level=snapshot, language=N'us_english')


  UPDATE dbo.test_table1
  SET MaxVal=  a - (a-b)  *  (((a - b)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-c)  *  (((MaxVal - c)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-d)  *  (((MaxVal - d)  & 0x80000000) / 0x80000000);

  UPDATE dbo.test_table1
  SET MaxVal=  MaxVal - (MaxVal-e)  *  (((MaxVal - e)  & 0x80000000) / 0x80000000);
  
end
go

print '2. Выражение внуктри Native_Compilation';
set statistics time on;
 
EXEC dbo.calcMax
 
set statistics time off;

GO 

create procedure dbo.calcMax2
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level=snapshot, language=N'us_english')


  UPDATE dbo.test_table1
  SET MaxVal = a 

  UPDATE dbo.test_table1
  SET MaxVal = b 
  WHERE b > MaxVal

  UPDATE dbo.test_table1
  SET MaxVal = c 
  WHERE c > MaxVal

  UPDATE dbo.test_table1
  SET MaxVal = d 
  WHERE d > MaxVal

  UPDATE dbo.test_table1
  SET MaxVal = e 
  WHERE e > MaxVal

end
go

print '3. Выражение внуктри Native_Compilation 2';
set statistics time on;
 
EXEC dbo.calcMax2
 
set statistics time off;
GO 

DROP procedure dbo.generate_10000000 
GO 
DROP procedure dbo.calcMax 
GO 
DROP procedure dbo.calcMax2 
GO 
drop table dbo.test_table1;
GO 
drop table dbo.test_table;
GO
2 сен 14, 20:10    [16525593]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
o-o
Guest
a_voronin
Я сравнил самый лучший вариант с NATIVE_COMPILATION и INMEMORY таблицей на SQL 2014...

кто о чем, а вшивый о бане
грузины, армяне, ... главное -- хвост! NATIVE_COMPILATION и INMEMORY

К сообщению приложен файл. Размер - 1Kb
2 сен 14, 21:34    [16525839]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
aleks2
ЗЫ. Научись аргументировать, чтоле?

и это пишет человек, который без аргументов сделал вброс по поводу моей ссылки...
3 сен 14, 09:35    [16526975]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
aleks2
Guest
Knyazev Alexey
aleks2
ЗЫ. Научись аргументировать, чтоле?

и это пишет человек, который без аргументов сделал вброс по поводу моей ссылки...

1. Надо быть слепым и бездарным программистом, чтоб не видеть НЕЧИТАЕМОСТИ case.
2. И верить, что перебор полей ОДНОЙ строки в case "быстрее", чем тот же перебор другим оператором.
3 сен 14, 10:14    [16527162]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
aleks2
И верить, что перебор полей ОДНОЙ строки в case "быстрее", чем тот же перебор другим оператором.

очередное болобольство, на котором вы не первый раз попадаетесь на этом форуме, у меня по ссылке хоть маломальский тест, а у вас тупой вброс...

научитесь уже аргументировать свою позицию а не жить по принципу "потому что я так сказал"
3 сен 14, 10:25    [16527223]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
aleks2
Guest
Knyazev Alexey
aleks2
И верить, что перебор полей ОДНОЙ строки в case "быстрее", чем тот же перебор другим оператором.

очередное болобольство, на котором вы не первый раз попадаетесь на этом форуме, у меня по ссылке хоть маломальский тест, а у вас тупой вброс...

научитесь уже аргументировать свою позицию а не жить по принципу "потому что я так сказал"


Не, слепому программисту ничо не не докажешь.
Да я и не стремился.
3 сен 14, 10:28    [16527240]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
aleks2
Да я и не стремился.

ну я и не ждал другого ответа, удачи!
3 сен 14, 10:28    [16527247]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31785
Knyazev Alexey
тогда уж ещё варианты: http://t-sql.ru/post/max_from_several_fields.aspx

ну и CASE в данном случаи - рулит
Жаль, что там нет варианта с XML...
А насчёт CASE можно не сомневаться, что он быстрее, пусть и в ущерб читаемости.
3 сен 14, 10:53    [16527397]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
o-o
Guest
спокойствие, только спокойствие,
ссылку мы все заметили, почитали, сделали выводы (правда, у каждого они свои,
не стОит из-за этого ссориться)

и вообще за блог спасибо!

+
но вот возьмем слово "болобол".
судя по всему, это тот, кто дважды любит БОЛ, так что в контексте SQL Server-а как обзывательство не катит.
3 сен 14, 11:23    [16527647]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
o-o,

красавчег , па добраму :)
3 сен 14, 11:27    [16527694]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
o-o
(с) "болобол" - кто дважды любит БОЛ

записал!!! Спасибо!


да я и не хотел никого обозвать или обидеть!
3 сен 14, 11:39    [16527814]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
o-o
a_voronin
Я сравнил самый лучший вариант с NATIVE_COMPILATION и INMEMORY таблицей на SQL 2014...

кто о чем, а вшивый о бане
грузины, армяне, ... главное -- хвост! NATIVE_COMPILATION и INMEMORY


Рождённый ползать до NATIVE_COMPILATION не взлетит
3 сен 14, 13:36    [16528988]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
sigmov
Member

Откуда: Владивосток
Сообщений: 161
А не проще ли через Scalar UDF?
1. Определяем функцию на нужное число аргументов
CREATE FUNCTION [dbo].[FnMaxValue]
(
	@a FLOAT,
	@b FLOAT,
	@c FLOAT
	-- .... сколько надо
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @Max FLOAT = @a;
	If (@Max < @b) SET @Max = @b;
	If (@Max < @c) SET @Max = @c;
	-- ..... cколько надо
	RETURN @Max;
END
Которое возвращает максимальный аргумент

2. Используем функцию в запросе
SELECT [dbo].[FnMaxValue](a,b,c) as Value FROM T
3 сен 14, 16:15    [16530293]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31785
sigmov
А не проще ли через Scalar UDF?
1. Определяем функцию на нужное число аргументов
2. Используем функцию в запросе

1. Скалярная функция будет работать в тыщу раз медленее любого из предложенных вариантов, лучше уж тогда INLINE-табличную функцию
2. Для каждого количества аргументов и для каждого типа придётся писать свою функцию, и это для однократного использования (скорее всего).
3 сен 14, 16:22    [16530375]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
sigmov
А не проще ли через Scalar UDF?

а вы пробовали скалярную функцию использовать на больших объёмах?

тест на таблице из 10 млн. строк и 5 столбцов (ваша скалярка vs инлайн функция):

 SQL Server Execution Times:
   CPU time = 36583 ms,  elapsed time = 42205 ms.

 SQL Server Execution Times:
   CPU time = 9129 ms,  elapsed time = 1700 ms.
3 сен 14, 16:30    [16530446]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
sigmov
Member

Откуда: Владивосток
Сообщений: 161
Кашерное решение через CLR:
public partial class UDF_FnMaxesByCols
{
    [SqlFunction(DataAccess = DataAccessKind.Read, Name = "FnMaxesByCols", TableDefinition = "Value FLOAT", FillRowMethodName = "FillRow")]
    public static IEnumerable FnMaxesByCols([SqlFacet(MaxSize = 400, IsNullable = false)] string executeString)
    {
        List<double> maxList = new List<double>();
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(executeString, connection))
            using (SqlDataReader reader = command.ExecuteReader())
            {
                int count = reader.FieldCount;
                while (reader.Read())
                {
                    double max = double.MinValue;
                    for (int i = 0; i < count; i++)
                        max = Math.Max(max, Convert.ToDouble(reader.GetValue(i)));
                    maxList.Add((int)max);
                }
            }
        }
        return maxList;
    }

    public static void FillRow(object row, out double value)
    {
        value = (double)row;
    }
}

Пример использования:
SELECT * FROM [lel].[FnMaxesByCols] 
(N'
	SELECT 1, 2, 3
	UNION ALL
	SELECT -1, -2, -3
')
GO
3 сен 14, 17:08    [16530707]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
sigmov
Member

Откуда: Владивосток
Сообщений: 161
a_voronin,
CASE вполне можно "обрезать" до треугольного
UPDATE dbo.test_table
	SET MaxVal =  case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
                    then a
                    when ( b >= c ) and ( b >= d ) and ( b >= e )
                    then b
                    when ( c >= d ) and ( c >= e )
                    then c
                    when ( d >= e )
                    then d
                    else e
                 end
  from dbo.test_table;

Кроме того - при использовании CASE нет смысла запускать UPDATE а потом SELECT все данные (причем это нужно будет делать в транзакции при должном уровне изоляции, а то получишь грязно/фантомное чтение)
3 сен 14, 17:21    [16530807]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
sigmov,

а что там у нас с возможными значениями NULL?
И предположим, что полей не 3, а 30.
3 сен 14, 17:24    [16530827]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
o-o
Guest
sigmov
А не проще ли через Scalar UDF?
...
Кашерное решение через CLR


ето что, конкурс на "лучшее черезж.пное" уже пошел?
даешь анти-писькомерство на звание самого тормозного в номинации "императивщина против декларативщины"!
3 сен 14, 17:27    [16530858]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
sigmov
А не проще ли через Scalar UDF?
1. Определяем функцию на нужное число аргументов
CREATE FUNCTION [dbo].[FnMaxValue]
(
	@a FLOAT,
	@b FLOAT,
	@c FLOAT
	-- .... сколько надо
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @Max FLOAT = @a;
	If (@Max < @b) SET @Max = @b;
	If (@Max < @c) SET @Max = @c;
	-- ..... cколько надо
	RETURN @Max;
END
Которое возвращает максимальный аргумент

2. Используем функцию в запросе
SELECT [dbo].[FnMaxValue](a,b,c) as Value FROM T


Если вы смотрели ссылку http://t-sql.ru/post/max_from_several_fields.aspx , то там этот вариант рассматривался и он далеко не самых быстрый. 7-й номер. CASE быстрее всего, перебить его может только NATIVE_COMPILATION.

1. Выражение CASE (любая версия)

SQL Server Execution Times:
CPU time = 3416 ms, elapsed time = 3416 ms.
----------------------------------------------------------------------

2. Подзапрос и оператор UNION (любая версия)

SQL Server Execution Times:
CPU time = 12777 ms, elapsed time = 3848 ms.
----------------------------------------------------------------------

3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)

SQL Server Execution Times:
CPU time = 13182 ms, elapsed time = 3968 ms.
----------------------------------------------------------------------

4. Оператор UNPIVOT (SQL Server 2005 и выше)

SQL Server Execution Times:
CPU time = 22246 ms, elapsed time = 15966 ms.
----------------------------------------------------------------------

5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)

SQL Server Execution Times:
CPU time = 153957 ms, elapsed time = 239071 ms.
----------------------------------------------------------------------

6. Оператор CROSS APPLY (SQL Server 2005 и выше)

CROSS APPLY 1

SQL Server Execution Times:
CPU time = 22963 ms, elapsed time = 6273 ms.

CROSS APPLY 2

SQL Server Execution Times:
CPU time = 33244 ms, elapsed time = 9270 ms.
----------------------------------------------------------------------

7. Inline-функция и CTE (SQL Server 2005 и выше)

SQL Server Execution Times:
CPU time = 12246 ms, elapsed time = 3787 ms.
3 сен 14, 17:31    [16530890]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
Wlr-l
Member

Откуда:
Сообщений: 523
a_voronin,

если в 6. Оператор CROSS APPLY (SQL Server 2005 и выше) CROSS APPLY 1, заменить тор 1... order by... на Max ... group by ..., то такое решение по производительности будет на уровне решений 2 и 3 (будет отсутствовать сортировка). Это решение приведено выше.
3 сен 14, 17:42    [16531004]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
o-o
Guest
a_voronin
CASE быстрее всего, перебить его может только NATIVE_COMPILATION.


ой, мы сдаемся, чесслово,
мы не хотим летать, нас уже от одного слова NATIVE_COMPILATION укачивает!!!

+
а знаете, почему NATIVE_COMPILATION все еще в калькуляторы не встроили?
мало примелькалось. вы его в каждый десятый топик воткнули?
маловато будет, надо хотя бы в каждый третий


К сообщению приложен файл. Размер - 1Kb
3 сен 14, 17:44    [16531020]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать максимум из нескольких полей одной записи  [new]
sigmov
Member

Откуда: Владивосток
Сообщений: 161
iap
а что там у нас с возможными значениями NULL?
И предположим, что полей не 3, а 30.

NULL - не предусмотрены. Вылетит System.InvalidCastException
30 полей - не проблема, хоть 900, главное чтоб все были числовыми
3 сен 14, 17:49    [16531052]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить