Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 update и order by  [new]
saley
Member

Откуда:
Сообщений: 594
Доброго всем дня,

Проблема:
работающую БД с mssql 2017 RUS перенёс на mssql 2017 ENG

у меня проблема возникла с работой сервера, а именно

С помощью select создаю временную таблицу где есть столбец Idenity, в которой перед вставкой сортирую в нужном мне порядке.
Далее с помощью Update нужно обновлять строки именно в том порядке в котором я её создал, на прежнем сервере работает нормально, на новом перемешивает, всего строк в таблице 488, сначала выбирает строки с id_ 313-416 потом 1 - 104, всё остальное.
Может настройки есть какие-то?
14 авг 19, 15:14    [21949347]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36342
Настройка "сделать из неправильного запроса правильный"? Нет, тут программист нужен.
14 авг 19, 15:16    [21949351]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Владислав Колосов
Member

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

настройка в голове нужна. Начните с базиса, курите определение реляционного отношения до полного понимания того, что так, как Вы хотите, не должно работать.
14 авг 19, 15:42    [21949370]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
saley
Member

Откуда:
Сообщений: 594
Господа я понимаю что вы все умные,
объясните почему тогда на одном сервере работает, а на другом не хочет?
14 авг 19, 15:46    [21949373]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Владислав Колосов
Member

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

сервер читает страницы так, как ему удобно.
14 авг 19, 15:47    [21949374]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
invm
Member

Откуда: Москва
Сообщений: 8717
saley
Может настройки есть какие-то?
Настроек нет. Есть костыли.

1. Сделать по "столбец Idenity" кластерный индекс.
2.
update t
 set
  ...
from
 (select top (cast(0x7fffffffffffffff as bigint)) ... from [временная таблица] order by [столбец Idenity]) t


И нет гарантий, что данные костыли будут всегда работать корректно.
14 авг 19, 15:51    [21949378]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19152
saley
почему тогда на одном сервере работает, а на другом не хочет?
Хы... между прочим, в любой момент на первом может и перестать работать... как, впрочем, и на втором - начать работать как тебе хочется.

А вообще если бы кто-то показал структуру таблиц, тестовое наполнение и желаемый результат - давно бы проблема решилась, наверное.
14 авг 19, 15:53    [21949382]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1011
saley
Господа я понимаю что вы все умные,
объясните почему тогда на одном сервере работает, а на другом не хочет?

Изучайте, что такое параметры сортировки (collation), как они работают и применяются.
На разных серверах у вас наверняка используются разные collation.

https://docs.microsoft.com/ru-ru/sql/t-sql/statements/collations?view=sql-server-2017
14 авг 19, 15:53    [21949383]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19152
Minamoto, у него ж вроде как порядок задаёт "столбец Identity", число то бишь, к которому collation вообще никаким боком.
14 авг 19, 15:55    [21949384]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
msLex
Member

Откуда:
Сообщений: 6387
saley
Господа я понимаю что вы все умные,
объясните почему тогда на одном сервере работает, а на другом не хочет?

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

Если вы опишите, что же за "упорядоченные апдейты" вы делаете, вам, скорее всего, подскажут как сделать поведение детерминированным.


ЗЫ
Высока вероятность, что у вас задача "нарастающий итог". В SQL 2017 (да и раньше) это делается через sum() over(order by)
14 авг 19, 15:55    [21949386]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1011
Akina, вроде у него каша в голове и неспособность сформулировать вопрос.

saley
С помощью select создаю временную таблицу где есть столбец Idenity, в которой перед вставкой сортирую в нужном мне порядке.

Вот как это понять?
И что такое ниже?
saley
с помощью Update нужно обновлять строки именно в том порядке в котором я её создал


Поэтому первое предположение - для уровня базового непонимания просходящего - про collation.
Может я и ошибаюсь, и в вопросе есть какой то скрытый смысл, тогда мой ответ, конечно, не к месту.
14 авг 19, 16:11    [21949402]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
saley
Member

Откуда:
Сообщений: 594
как решение, это создание кластерного индекса. Вроде помогло. Но а с Collate надо поразбираться, наверняка здесь засада. Хотя проверял настройки на двух серверах, вроде одинаковые.
Всем спасибо за помощь.
14 авг 19, 16:56    [21949455]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36342
saley,

Ждем вас с нетерпением снова, когда у вас случится неупорядоченный скан кластерного индекса.
14 авг 19, 17:05    [21949467]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
invm
Member

Откуда: Москва
Сообщений: 8717
Гавриленко Сергей Алексеевич
Ждем вас с нетерпением снова, когда у вас случится неупорядоченный скан кластерного индекса.
При update такого не случится. По крайней мере, на текущий момент.
14 авг 19, 18:16    [21949537]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
uaggster
Member

Откуда:
Сообщений: 676
invm
(select top (cast(0x7fffffffffffffff as bigint)) ... from [временная таблица] order by [столбец Idenity]) t


И нет гарантий, что данные костыли будут всегда работать корректно.

invm, а почему так?
А не
Select top (select 100) percent * from ...

?
14 авг 19, 20:35    [21949615]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
invm
Member

Откуда: Москва
Сообщений: 8717
uaggster
invm, а почему так?
А не
Select top (select 100) percent * from ...
drop table if exists #t;
go

create table #t (id int identity, s varchar(8000));
insert into #t
values ('a'), ('b'), ('c');

set statistics profile on;

select top (cast(0x7fffffffffffffff as bigint)) * from #t order by id;
select top ((select 100)) percent * from #t order by id;

set statistics profile off;
go

select top (cast(0x7fffffffffffffff as bigint)) * from #t order by id
|--Sort(TOP -1, ORDER BY:([tempdb].[dbo].[#t].[id] ASC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t]))

select top ((select 100)) percent * from #t order by id
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1004]))
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(float(53),[Expr1003],0)))
| |--Constant Scan(VALUES: (((100))))
|--Top(TOP EXPRESSION:([Expr1004]) PERCENT)
|--Sort(ORDER BY:([tempdb].[dbo].[#t].[id] ASC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t]))
14 авг 19, 20:59    [21949632]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
uaggster
Member

Откуда:
Сообщений: 676
(продолжаю упорствовать)
На первый взгляд - планы кажутся одинаковыми. Ну, добавилось одно вычисление скаляра.
На сколько нибудь больших выборках - разница будет ничтожной.
Разве не так?
14 авг 19, 21:35    [21949642]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
invm
Member

Откуда: Москва
Сообщений: 8717
uaggster
(продолжаю упорствовать)
Да пожалуйста. Кто ж вам запретит? :)
uaggster
Ну, добавилось одно вычисление скаляра.
А Nested Loops и Constant Scan?
uaggster
На сколько нибудь больших выборках - разница будет ничтожной.
Разве не так?
Размер "ничтожности" может зависеть от разных факторов.
+ Определяем цену Nested Loops + Constant Scan
use tempdb;
go

drop table if exists dbo.t;
go

create table dbo.t (id int identity, s varchar(8000));
insert into dbo.t
select top (5000000)
 'a'
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare
 @q1 nvarchar(max) = N'update t set s = ''aaa'' from (select top (cast(0x7fffffffffffffff as bigint)) * from dbo.t order by id) t where s > ''z'' option (maxdop 1);',
 @q2 nvarchar(max) = N'update t set s = ''aaa'' from (select top ((select 100)) percent * from dbo.t order by id) t where s > ''z'' option (maxdop 1);'

declare @c int = 10;

while @c > 0
 begin
  exec sys.sp_executesql @q1;
  exec sys.sp_executesql @q2;

  set @c -= 1;
 end;
go

select
 st.text, qp.query_plan, (qs.total_worker_time * 1.) / qs.execution_count / 1000 as avg_worker_time_ms
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
 sys.dm_exec_sql_text(qs.sql_handle) st
where
 st.text like N'%update t set s = ''aaa''%'
option
 (recompile);
go

drop table dbo.t;
go
14 авг 19, 23:10    [21949683]     Ответить | Цитировать Сообщить модератору
 Re: update и order by  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6639
Гарантировать порядок обновления записей можно только итератором, например, курсором. В любом случае быстрых гарантированных способов нет. Надо подумать все же о соответствии своих решений реляционной механике. Изучать базовые принципы обработки данных.
15 авг 19, 11:19    [21949900]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить