ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
18 сообщений из 18, страница 1 из 1
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999067
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
Имеется несколько связных таблиц, для которых сервер приложения дергает запрос возвращающий их контрольную сумму.
В примере создана идентичная структура. Контрольная сумма возвращается примерно для 200..300 строк, работает довольно быстро, с точки зрения перфоманса такой вариант вполне устраивает.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
declare @tableMain table(pKey uniqueidentifier, valInt int, isAct int,  updateDate datetime)
declare @relTable1 table(pKey uniqueidentifier, valInt1 int, updateDate datetime)
declare @relTable2 table(pKey uniqueidentifier, valInt2 int, updateDate datetime)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, updateDate datetime)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, updateDate datetime)

declare @cn int = 0;

declare @pKey uniqueidentifier
declare @tKey uniqueidentifier

while @cn <= 40
begin
    set @pKey = newid();
    insert into @tableMain select @pKey, @cn, @cn%2, getdate()
    insert into @relTable1 select @pKey, @cn+2, getdate()
    insert into @relTable2 select @pKey, @cn+4, getdate()
    
    declare @cn2 int = 0;
    while @cn2 < 4
    begin
        set @tKey = newid();

        insert into @relTable3 select @tKey, @pKey, @cn+@cn2, getdate()
        insert into @relTable4 select @tKey, @pKey, @cn2+@cn/2, getdate()

        set @cn2 = @cn2 + 1;
    end

    set @cn = @cn + 1;

end

--select * from @tableMain
--select * from @relTable1
--select * from @relTable2
--select * from @relTable3
--select * from @relTable4

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @tableMain as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable1 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable2 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable3 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable4 as x join actList on actList.pKey = x.pKey
    group by x.pKey
)
select 'BeforeUpdate' as info, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list
-- select 'BeforeUpdate' as info,pKey, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list group by pKey

update @tableMain set updateDate = getdate()
where pKey in (select top 10 pKey from @tableMain where isAct = 1)


;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @tableMain as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable1 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable2 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable3 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable4 as x join actList on actList.pKey = x.pKey
    group by x.pKey
)
select 'AfterUpdate' as info, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list
-- select 'AfterUpdate' as info,pKey, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list group by pKey



В процессе отладки я обновлял UpdateDate нескольких строк и смотрел как подтягиваются изменения в сервер приложений.
в примере это такой код
Код: sql
1.
2.
update @tableMain set updateDate = getdate()
where pKey in (select top 10 pKey from @tableMain where isAct = 1)


В какой-то момент я заметил, что реакции на обновление UpdateDate нет.
Покопался, и понял что запрос вернул туже контрольную сумму что и до обновления. Можно было бы еще 10 раз обновить UpdateDate но результат не менялся.
я попробовал развернуть списки, в контексте примера, выбрать контрольные суммы для каждого pKey
они определенно разные (приложенный рисунок - скрин реальных данных до обновления UpdateDate и после).
Спустя пол часа, я повторил обновление - и получил тот же результат.

Но, если обновить не 10, а 11 строк - то контрольная сумма уже поменяется.
Код: sql
1.
2.
update @tableMain set updateDate = getdate()
where pKey in (select top 11 pKey from @tableMain where isAct = 1)


через какое-то время проблема возвращается, потом может пропасть.

либо если я поменяю набор полей по которому итоговый checksum_agg считается
Код: sql
1.
2.
3.
checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate))
-- меняем на
checksum_agg(binary_checksum(chkSum,MaxUpdateDate))


то код снова реагирует на изменения UpdateDate

Пример хоть и повторяет используемый подход, но повторить результат с одинаковой контрольной суммой не получилось.
Еще отличие, в реальном запросе все таблички с хинтами with(nolock).

Как альтернатива - получать из базы список контрольных сумм, которые всегда корректно меняются, но тягать один int много быстрее чем список Guid/Int из 300 строк, еще и каждые 15 секунд, хочется как-то этот запрос подпилить.
Также хочется чтоб все решение было в одном SQL запросе который сторится в сервере приложения, без триггеров, без механизмов профайлинга и отслеживания транзакций.

Вопросы:
- я понимаю что checksum в принципе функция которая может повторяться, но возможно ли сделать какие-то модификации запроса чтоб эти повторения случались намного реже?
- какие есть альтернативы комбинации checksum_agg+binary_checksum?
...
Рейтинг: 0 / 0
15.09.2020, 22:23
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999083
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Вы прежде, чем изобретать велосипеды, изучите матчасть.
Для отслеживания изменений данных в таблицах давно придумали rowversion.
...
Рейтинг: 0 / 0
15.09.2020, 23:14
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999102
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
Гавриленко Сергей Алексеевич
Вы прежде, чем изобретать велосипеды, изучите матчасть.
Для отслеживания изменений данных в таблицах давно придумали rowversion.

Без велосипедов не интересно )
тыкался уже с rowversion, вот еслиб это поле еще обновлялось когда запись удаляется

как из нескольких таблиц собрать rowversion в одно число на выходе?
на вскидку в голову пришло так, но что-то мне подсказывает что это не айс вариант
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
declare @tableMain table(pKey uniqueidentifier, valInt int, isAct int,  updateDate datetime, ver rowversion)
declare @relTable1 table(pKey uniqueidentifier, valInt1 int, updateDate datetime, ver rowversion)
declare @relTable2 table(pKey uniqueidentifier, valInt2 int, updateDate datetime, ver rowversion)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, updateDate datetime, ver rowversion)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, updateDate datetime, ver rowversion)

declare @cn int = 0;

declare @pKey uniqueidentifier
declare @tKey uniqueidentifier

while @cn <= 10
begin
    set @pKey = newid();
    insert into @tableMain select @pKey, @cn, @cn%2, getdate(), null
    insert into @relTable1 select @pKey, @cn+2, getdate(), null
    insert into @relTable2 select @pKey, @cn+4, getdate(), null
    
    declare @cn2 int = 0;
    while @cn2 < 4
    begin
        set @tKey = newid();

        insert into @relTable3 select @tKey, @pKey, @cn+@cn2, getdate(), null
        insert into @relTable4 select @tKey, @pKey, @cn2+@cn/2, getdate(), null

        set @cn2 = @cn2 + 1;
    end

    set @cn = @cn + 1;

end

-- select * from @tableMain
-- select * from @relTable1
-- select * from @relTable2
-- select * from @relTable3
-- select * from @relTable4

-- select * from @tableMain
-- select * from @relTable4

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 insert into @tableMain select newid(), 654, 987, getdate(), null

 
;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after ins' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 update @relTable4 set valInt4 = 12345 where tKey in (select top 1 tKey from @relTable4)

 ;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after upd' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max ' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 delete from @relTable4 where tKey in (select top 1 tKey from @relTable4)

 ;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
     select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after del' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

...
Рейтинг: 0 / 0
16.09.2020, 01:05
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999103
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Кифирчиктыкался уже с rowversion, вот еслиб это поле еще обновлялось когда запись удаляетсяЗавести флажок is_deleted и заменить удаление на апдейт.

Кифирчиккак из нескольких таблиц собрать rowversion в одно число на выходе?Зачем вы все это городите?.. Достаточно запомнить максимальное значение rowversion для таблицы. И далее если в таблице текущее максимальное больше ранее запомненного, то в ней были изменения. Их даже элементарно можно будет выбрать. А если еще и индексы сделать...
...
Рейтинг: 0 / 0
16.09.2020, 01:39
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999194
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
Гавриленко Сергей Алексеевич
Зачем вы все это городите?

У меня в памяти приложения "документ" состоящий из нескольких таблиц в БД и мне интересно ловить изменения в этих нескольких таблицах.
И по этому городить нужно, либо в SQL либо в C#. В SQL предпочтительно. Хочется один запрос, и на выходе одно INT число.
Еще в примере приведено 4 запроса в 3х вариантах, для сравнения , возможно вам это "нагорожено" показалось.

Задумка с тем чтоб использовать rowversion вместо binary_checksum(*) однозначно лучше, и такое вариант будет точно лучше по производительности. хотя это требует внести изменения в генератор классов, чтоб не тянуть эти поля в EF.
То есть, можно сказать ваша рекомендация (в контексте тестового примера) такая:
Код: sql
1.
2.
3.
4.
5.
6.
select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4



Но это не ловит удаления строк.
А идея с отказом от физического удаления мне не нравится. То что удаляется - оно заказчику не нужно, а сложность определенную вносит. Как в том анекдоте "не... эти хохмочки я уже видел")))
На предыдущих проектах на практике это не несло практической ценности и создавало больше неудобств, в текущем решили оставить IsDeleted только для справочников, либо если заказчик скажет "нужны вот эти удаленные документы" (зная тему заказчика, предсказываю вероятность этого - 0.1% ).
Получается, на одной чаше весов "перешерстить весь код где эти таблицы используются, все отчеты" на другой "в одной из нескольких подсистем будет проще запрос для отслеживания изменений в этих 5 таблицах". Малова-то будет )))

если бы вариант с rowversion научить ловить удаления и собирать в одно число - было бы идеально
...
Рейтинг: 0 / 0
16.09.2020, 09:45
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999205
Фотография HandKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HandKot
Участник
Кифирчик

если бы вариант с rowversion научить ловить удаления и собирать в одно число - было бы идеально

не совсем понял что нужно на выходе, но для отслеживания имеются CDC и, если позволяет версия, TEMPORAL TABLE
...
Рейтинг: 0 / 0
16.09.2020, 09:56
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999215
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Кифирчик,

https://docs.microsoft.com/ru-ru/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency
...
Рейтинг: 0 / 0
16.09.2020, 10:13
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999597
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
попробую обобщить...

вариант особенностиchecksum_agg+binary_checksum гуру MSSQL судя по всему такой подход удачным не кажется + видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д.rowversion требует поля IsDeleted и отказа от физического удаленияChange Tracking позволяет ловить изменения в конкретной таблице; по описанию microsoft прям рекомендует это как обеспечивающее эффективный механизм отслеживания изменений для приложений; доступно с SQL Server 2014 (если поставить обновление);требует дополнительных манипуляций с базами данных после развертыванияChange Data Capture что-то более "масштабное" чем Change Tracking; позволяет заглянуть прям в журнал SQL; требует агента (express версии видимо отметаются) и создает свою схему в базе с данными и ХПTemporal tables доступна с 2016й версии; автоматически позволяет вести всю историю измененийSqlDependency через SqlCommand позволяет подписаться на изменения в таблице; требует отдельных ролей & пользователей и постоянно висящего коннекта; судя по тому небольшому количеству информации в сети заводится не с пол пинка и в целом не очень популярна

ничего не упустил?
...
Рейтинг: 0 / 0
16.09.2020, 23:14
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999614
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Кифирчик
ничего не упустил?

Все смешалось в доме Облонских.

SqlDependency - это РЕАЛИЗАЦИЯ на .NET конкретной технологии слежения за изменениями.

Она неизбежно использует какие-то механизмы сервера.
Только мелкософт не рассказывает.
Профайлер расскажет.


"rowversion требует поля IsDeleted и отказа от физического удаления "
Только если у вас фантазии не хватает.

rowversion + count(*)

"checksum_agg+binary_checksum видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д."

Вы бредите.
Все хэши и чексуммы ГАРАНТИРОВАННО дают коллизии.
Ибо невозможно пронумеровать миллиард вариантов данных с помощью 256 значений одного байта.
...
Рейтинг: 0 / 0
17.09.2020, 06:00
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999615
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ах да, чуть сам не забыл.
Банальный триггер на таблице.
...
Рейтинг: 0 / 0
17.09.2020, 06:12
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999668
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
aleks222
"rowversion требует поля IsDeleted и отказа от физического удаления "
Только если у вас фантазии не хватает.

rowversion + count(*)

вы не поверите, пробовал, и binary_checksum(rowversion, count(*)) и binary_checksum(rowversion + count(*))
не помогает. более того, с rowversion я дупы начал ловить даже в тестовом примере
для отдельной строки все работает отлично, но когда хэши всех строк собираются в одну контрольную сумму, то получается не то что ожидаешь.

aleks222

"checksum_agg+binary_checksum видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д."

Вы бредите.
Все хэши и чексуммы ГАРАНТИРОВАННО дают коллизии.
Ибо невозможно пронумеровать миллиард вариантов данных с помощью 256 значений одного байта.

эх.. а я то только вчера узнал как hash/checksum функции работают ...
во-первых не 256 а INT, от -2147483648 до +2147483648
во-вторых я ожидал не уникальности, а её очень низкой вероятности, чего не получается.

У себя сделал, временно и как самое быстрое решение - загружаю на сервер список и считаю контрольную сумму в C#
Код: c#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
            var result = 0;

            var orderedList = list.OrderBy(c => c.Key);

            foreach( var item in orderedList)
            {
                result = (result * 397) ^ item.Key.GetHashCode();
                result = (result * 397) ^ item.Checksum.GetHashCode();
            }

            return result;


и все заработало.

+ новый тикет что нужно сделать через Change Tracking + проверку из "сервера приложения" что эта фитча включена и вывод предупреждения если включить забыли.

и дополню резюме по топику

вариант особенности в контексте проблемы топикаchecksum_agg+binary_checksum хорошо работает построчно (с низкой вероятностью повторения результата при изменении данных строки) но плохо работает когда checksum нескольких строк агрегируешь в одну (высокая вероятность повторения результата при изменении данных одной из строк или их количества);более ресурсоемко по сравнению с вариантами ниже; но можно сильно ускорить если брать checksum не от всех полей а от rowversion; но не требует ничего настраивать в БД ненадежный велосипедtrigger требует поле в таблице либо отдельную таблицу плюс сам триггер подходит; требует модификации БДrowversion требует поля IsDeleted и отказа от физического удаления подходит; требует более серьезной модификации БД для учета поля IsDeletedChange Tracking позволяет ловить изменения в конкретной таблице; по описанию microsoft прям рекомендует это как обеспечивающее эффективный механизм отслеживания изменений для приложений; доступно с SQL Server 2014 (если поставить обновление);требует дополнительных манипуляций с базами данных после развертывания судя по всему самый true way вариант; но требует усилия чтоб при развертывании не забывали про включение этой фитчиChange Data Capture что-то более "масштабное" чем Change Tracking; позволяет заглянуть прям в журнал SQL; требует агента (express версии видимо отметаются) и создает свою схему в базе с данными и ХП из пушки по воробьямTemporal tables доступна с 2016й версии; автоматически позволяет вести всю историю изменений из пушки по воробьямSqlDependency фитча ADO.NET позволяющая через SqlCommand позволяет подписаться на изменения в таблице; требует отдельных ролей & пользователей и постоянно висящего коннекта подходит; но смущает то что нужно держать подключение и требует усилия чтоб при развертывании не забывали про включение этой фитчи
...
Рейтинг: 0 / 0
17.09.2020, 10:37
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999684
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Вот статья имеется здесь уже очень давно: Журналирование изменений структуры БД и данных
Многого нового нет, конечно...
...
Рейтинг: 0 / 0
17.09.2020, 10:57
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999734
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Кифирчик
вы не поверите, пробовал, и binary_checksum(rowversion, count(*)) и binary_checksum(rowversion + count(*))
не помогает. более того, с rowversion я дупы начал ловить даже в тестовом примере
для отдельной строки все работает отлично, но когда хэши всех строк собираются в одну контрольную сумму, то получается не то что ожидаешь.


Ты, страдалец, ничего не понял.
Разъясняю для непонятливых.

Проверка

максимума rowversion и значения count(*)

дает искомое без отмены удалений.

Кифирчик

эх.. а я то только вчера узнал как hash/checksum функции работают ...
во-первых не 256 а INT, от -2147483648 до +2147483648
во-вторых я ожидал не уникальности, а её очень низкой вероятности, чего не получается.

Знать и понимать, и грамотно применять - это, как показывает твой пример, очень разные вещи.
Иначе, хотя бы, хэш подлиннее выбрал.

Поэтому твоя табличка, страдалец, бесполезна чуть более, чем полностью.
...
Рейтинг: 0 / 0
17.09.2020, 12:07
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999785
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
aleks222

Ты, страдалец, ничего не понял.
Разъясняю для непонятливых.

Проверка

максимума rowversion и значения count(*)

дает искомое без отмены удалений.

звучит слишком категорично.
для одной таблицы - ок, согласен.
но условия применения могут быть очень разные.
вот вам притянутый пример когда искомое по max(rowversion) & count(*) не верно.
это довольно близко к моему кейсу, и конкретно в моем случае rowversion + count не самый удачный вариант, как и checksum_agg.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
declare @tableMain table(pKey uniqueidentifier,                        valInt  int, isAct int, updateDate datetime, ver rowversion)
declare @relTable1 table(pKey uniqueidentifier,                        valInt1 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable2 table(pKey uniqueidentifier,                        valInt2 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, isAct int, updateDate datetime, ver rowversion)

declare @cn int = 0;

declare @pKey1 uniqueidentifier = newid();
declare @pKey2 uniqueidentifier = newid();
declare @pKey3 uniqueidentifier = newid();
declare @pKey4 uniqueidentifier = newid();
declare @pKey5 uniqueidentifier = newid();

insert into @tableMain select @pKey1, 1, 1, getdate(), null
insert into @relTable1 select @pKey1, 2, 1, getdate(), null
insert into @relTable2 select @pKey1, 3, 1, getdate(), null

insert into @tableMain select @pKey2, 1, 1, getdate(), null
insert into @relTable1 select @pKey2, 2, 1, getdate(), null
insert into @relTable2 select @pKey2, 3, 1, getdate(), null

insert into @tableMain select @pKey3, 1, 1, getdate(), null
insert into @relTable1 select @pKey3, 2, 1, getdate(), null
insert into @relTable2 select @pKey3, 3, 1, getdate(), null

        insert into @relTable3 select newid(), @pKey1, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey1, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey1, 2, 1, getdate(), null
        insert into @relTable4 select newid(), @pKey1, 2, 0, getdate(), null

        insert into @relTable3 select newid(), @pKey2, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey2, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey2, 2, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey2, 2, 1, getdate(), null

        insert into @relTable3 select newid(), @pKey3, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey3, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey3, 2, 1, getdate(), null
        insert into @relTable4 select newid(), @pKey3, 2, 0, getdate(), null

-------------------------------------------------

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], max(ver) as chkSum, count(*) as cn  
    from @tableMain as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table1' as [table], max(ver) as chkSum, count(*)
    from @relTable1 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table2' as [table], max(ver) as chkSum, count(*)
    from @relTable2 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table3' as [table], max(ver) as chkSum, count(*) as cn    
    from @relTable3 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
    union
    select x.pKey, 'table4' as [table], max(ver) as chkSum, count(*) as cn 
    from @relTable4 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
)
select 'before change' as info, max(chkSum), sum(cn) as Count from list

----------------------------------
-- модификация данных

declare @a uniqueidentifier  = (select top 1 tKey from @relTable4 where pKey = @pKey1 and isAct = 0)

print @a

update @relTable4 set isAct = 1 where tKey = @a

select 'updated rows '+isnull(cast(@@rowcount as nvarchar(1)),'null')

delete from @relTable4 where tKey = @a

select 'deleted rows '+isnull(cast(@@rowcount as nvarchar(1)),'null')

------------------------------------

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], max(ver) as chkSum, count(*) as cn  
    from @tableMain as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table1' as [table], max(ver) as chkSum, count(*)
    from @relTable1 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table2' as [table], max(ver) as chkSum, count(*)
    from @relTable2 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table3' as [table], max(ver) as chkSum, count(*)    
    from @relTable3 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
    union
    select x.pKey, 'table4' as [table], max(ver) as chkSum, count(*)
    from @relTable4 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
)
select 'after change'as info, max(chkSum), sum(cn) as Count from list



и пожалуйста больше мне ни чего не советуйте и не разъясняйте. вашу картину мира я уже понял.
...
Рейтинг: 0 / 0
17.09.2020, 13:58
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #39999991
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
Как мне кажется вы зря мешаете в кучу вычисление хэша по строке и вычисления хэша по группе строк (агрегат)
Для первого случая есть HASHBYTES https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
Для второго придется писать CREATE AGGREGATE. Почти уверен что assembly для этого по алгоритму MD5 или SHA2 кто-то уже написал.
...
Рейтинг: 0 / 0
17.09.2020, 17:36
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #40000454
Кифирчик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кифирчик
Участник
SERG1257
Как мне кажется вы зря мешаете в кучу вычисление хэша по строке и вычисления хэша по группе строк (агрегат)
Для первого случая есть HASHBYTES https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
Для второго придется писать CREATE AGGREGATE. Почти уверен что assembly для этого по алгоритму MD5 или SHA2 кто-то уже написал.

Согласен, сейчас именно агрегат не айс работает. и если добавить assembly с хэш функцией которая дает лучшее покрытие - это решило бы проблему не изменяя текущий запрос. Еще один вариант решения проблемы!

но это вариант я отношу к категории "доп настройка базы", 99% новый разработчик (не базист) не прочитает документацию (которую можно забыть обновить) или упустит этот момент, развернет у себя базу, через пару часов заметит что что-то работает не так или версия сервер что-то не поддерживает, агент у express не работает или откопает ошибку в логах сервера приложения, еще пару часов будет гуглить а потом меня спросит - а чего у меня тут не фурычит.
благо если эта "доп настройка" сохранится в базе и будет серверо независима, иначе придется дополнять скрипты которые автоматом тестовые базы поднимают. в общем это дополнительная сложность, которая как бы выносится из SQL запроса и сервера приложения но появляется в другом месте, усложняя сопровождение.

У хэш суммы есть заманчивая возможность написать в аргументе binary_checksum - '*'.
Это проще, при перечислении можно чего-то и забыть, и дабы не морочиться в случае изменении структуры таблицы, можно просто забыть что при добавлении/удалении поля к таблице нужно его добавить/удалить и в этом запросе.
hashbytes аргументом требует строку, то есть сперва нужно либо все поля откастить в строку а потом сложить, либо выбрать в for xml (удобно что в нем тоже можно указать "все поля").
hashbytes и так работает наверно на порядок медленнее чем checksum, так и 'cast' or 'for xml' внесут заметный оверхэд.
на практике, пока масштабность этого еще не осознали, получили неприятный опыт - hashbytes+for xml на таблице (примерно в 15К) зависало в джобе на 40..70сек, это конечно больше к ресурсоемкости for xml.

по этому я и ошибочно посмотрел в сторону того что быстрее и более простой вид имеет - checksum, к нему еще есть checksum_agg.

с 11 года, видимо, утверждение
>the CHECKSUM and BINARY_CHECKSUM functions are very poor hash functions.
>Even with 1M input and 64K large buckets, they don’t even fill the hash space.
осталось верным

другой ошибкой было что когда пробовал варианты агрегата я по невнимательности при его получении по всем строкам писал count(cn) вместо sum(cn) , возможно показатели агрегата были бы более разнообразны

вообще, исходя из того что мне в этом топике озвучили все же можно попробовать пойти по пути rowversion
- заменить checksum строки на rowversion & max(rowversion) + count(*)
- для агрегата собирать таблицу из key & rowversion & count в строку и из неё получать hashbytes, это будет происходить для ограниченного набора (порядка 300 строк) и будет заметно быстрее чем происходит сейчас, нужно на реальных данных попробовать.
но это не решит проблему того что могут исторические данные снова попадать в фильтр "активных" (без изменения rowversion), хоть и редкий кейс но хорошо бы и его учесть в SQL запросе или исключить появление такого варианта в сервере приложения.

iap
Вот статья имеется здесь уже очень давно: Журналирование изменений структуры БД и данных
Многого нового нет, конечно...

просмотрел... ух... 2005й год... времена MSDE & SQL2000 и Enterprise Manager & Query analyzer
очень много воды утекло...
я бы сказал не "много нового нет" а фитчи которые там описываются в части журналирования данных уже появились как стандартный функционал, в частности - Change Tracking, Change Data Capture, Temporal tables
...
Рейтинг: 0 / 0
19.09.2020, 10:46
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #40000616
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Чтобы сильно снизить вероятность коллизий binary_checksum, нужно ее рассчитывать три раза: по всем полям, по четным полям и нечетным полям.
Вероятность совпадения, в результате, будет равна произведению вероятностей совпадения одного хеша.
...
Рейтинг: 0 / 0
20.09.2020, 08:58
| Ответить | Цитировать | Написать  
Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
    #40000624
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
uaggster
Чтобы сильно снизить вероятность коллизий binary_checksum, нужно ее рассчитывать три раза: по всем полям, по четным полям и нечетным полям.
Вероятность совпадения, в результате, будет равна произведению вероятностей совпадения одного хеша.

Бред.
Проще выбрать хэш подлиннее.
...
Рейтинг: 0 / 0
20.09.2020, 11:32
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39999597&tid=1685632]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 62ms
get tp. blocked users:
 1ms
others: 16ms
total:  163ms
созд. / загр.: 163ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]