DataHistoryAnalyze/Analyze.sql at master · 240596448/DataHistoryAnalyze · GitHub

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
--drop table #DataHistoryAnalyze

-- Сбор данных
SELECT
	q.Type						as Type,
	q.Name						as Name,
	q.MetadataId				as MetadataId,
	q.UserName					as UserName,
	Count(Distinct q.DataId)	as CountDataId,
	Count(q.VersionNumber)		as CountVer,
	Max(q.VersionNumber)		as MaxVer,
	Sum(q.Size)/1024			as SizeKb,
	Min(q.Date)					as MinDate,
	Max(q.Date)					as MaxDate
INTO #DataHistoryAnalyze
FROM (
	SELECT
		n.Type					as Type,
		n.Name					as Name,
		md._MetadataId			as MetadataId,
		l._DataId				as DataId,
		v._UserName				as UserName,
		v._VersionNumber		as VersionNumber,
		16 + 9 + 9 + 10 + 5 + 16
			+ DATALENGTH(v._UserName)*2
			+ DATALENGTH(v._UserFullName)*2
			+ DATALENGTH(v._Comment)*2
			+ 16 + 1 + 4 + 16
			+ DATALENGTH(v._Content)
								as Size,
		v._Date					as Date
	FROM [dbo].[_DataHistoryMetadata] as md
		left join [dbo].[_DataHistoryMetadataName] as n
			on md._MetadataId = n._MetadataId
			--and md._Fld2983 = 0   -- имя разделителя
		inner join [dbo].[_DataHistoryLatestVersions] as l
			on md._MetadataId = l._MetadataId
		left join [dbo].[_DataHistoryVersions] as v
			on v._HistoryDataId = l._HistoryDataId
				and v._MetadataVersionNumber = md._MetadataVersionNumber

	Where md._IsActual = 0x01

	UNION ALL

	-- закомментировать если таблица _DataHistoryLatestVersions1 отсутствует
	SELECT
		n.Type,
		n.Name,
		md._MetadataId,
		l._DataId,
		v._UserName,
		v._VersionNumber,
		16 + 9 + 9 + 10 + 5 + 16
			+ DATALENGTH(v._UserName)*2
			+ DATALENGTH(v._UserFullName)*2
			+ DATALENGTH(v._Comment)*2
			+ 16 + 1 + 4 + 16
			+ DATALENGTH(v._Content),
		v._Date
	FROM [dbo].[_DataHistoryMetadata] as md
		left join [dbo].[_DataHistoryMetadataName] as n
			on md._MetadataId = n._MetadataId
			--and md._Fld2983 = 0   -- имя разделителя
		inner join [dbo].[_DataHistoryLatestVersions1] as l
			on md._MetadataId = l._MetadataId
			--and md._Fld2983 = l._Fld2983
		left join [dbo].[_DataHistoryVersions] as v
			on v._HistoryDataId = l._HistoryDataId
				and v._MetadataVersionNumber = md._MetadataVersionNumber

	Where md._IsActual = 0x01

	UNION ALL

	-- закомментировать если таблица _DataHistoryLatestVersions2 отсутствует
	SELECT
		n.Type,
		n.Name,
		md._MetadataId,
		l._DataId,
		v._UserName,
		v._VersionNumber,
		16 + 9 + 9 + 10 + 5 + 16
			+ DATALENGTH(v._UserName)*2
			+ DATALENGTH(v._UserFullName)*2
			+ DATALENGTH(v._Comment)*2
			+ 16 + 1 + 4 + 16
			+ DATALENGTH(v._Content),
		v._Date
	FROM [dbo].[_DataHistoryMetadata] as md
		left join [dbo].[_DataHistoryMetadataName] as n
			on md._MetadataId = n._MetadataId
		inner join [dbo].[_DataHistoryLatestVersions2] as l
			on md._MetadataId = l._MetadataId
			--and md._Fld2983 = l._Fld41535
		left join [dbo].[_DataHistoryVersions] as v
			on v._HistoryDataId = l._HistoryDataId
				and v._MetadataVersionNumber = md._MetadataVersionNumber

	Where md._IsActual = 0x01

) as q

Group by
	q.Type,
	q.Name,
	q.MetadataId,
	q.UserName

option (maxdop 8)
GO
;

-- Анализ
SELECT TOP (1000)
	q.Type					as Type,
	q.Name					as Name,
	--q.UserName			as _UserName,
	Sum(q.CountVer)			as CountVer,
	Sum(q.CountDataId) 		as CountDataId,
	Max(q.MaxVer)			as MaxVer,
	Sum(q.SizeKb)				as SizeKb,
	Min(q.MinDate)			as MinDate,
	Max(q.MaxDate)			as MaxDate

from #DataHistoryAnalyze as q

Group by
	q.Type,
	q.Name
	--,q.UserName

Order by
	--CountVer desc,
	SizeKb desc