-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSQL_PERFORMANCE.sql
More file actions
226 lines (161 loc) · 6.42 KB
/
SQL_PERFORMANCE.sql
File metadata and controls
226 lines (161 loc) · 6.42 KB
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
213
214
215
216
217
218
219
220
221
222
223
224
225
226
USE MASTER
GO
IF DB_ID('SQL_PERFORMANCE') IS NOT NULL
BEGIN
DROP DATABASE SQL_PERFORMANCE
CREATE DATABASE SQL_PERFORMANCE
END
ELSE
CREATE DATABASE SQL_PERFORMANCE
GO
USE SQL_PERFORMANCE
-- CREATING TABLES FOR EXAMPLE
-- DROP TABLE USERS
CREATE TABLE USERS(
id BIGINT IDENTITY(1,1),
nameuser VARCHAR(300),
number1 DECIMAL(9,2),
number2 INT,
number3 INT
)
-- DROP TABLE DATA_MASS01
CREATE TABLE DATA_MASS01(
id BIGINT,
msg1 VARCHAR(500),
msg2 VARCHAR(300),
msg3 VARCHAR(300),
msg4 VARCHAR(300),
msg5 VARCHAR(300),
number1 BIT,
number2 DECIMAL(15,2),
number3 INT,
number4 INT,
number5 INT,
number6 INT,
number7 INT
)
-- DROP TABLE DATA_MASS02
CREATE TABLE DATA_MASS02(
id BIGINT IDENTITY(1,1),
msg1 VARCHAR(500),
msg2 VARCHAR(300),
number1 BIT,
number2 BIGINT,
number3 INT
)
BEGIN TRAN
-- INSERTING DATA ON TABLES
DECLARE @i BIGINT = 1,
@nameuser VARCHAR(300),
@number1 DECIMAL(9,2),
@number2 INT,
@number3 INT,
@numberBIT BIT,
@numberDM2 DECIMAL(15,2),
@numberDM3 INT,
@numberDM4 INT,
@numberDM5 INT,
@numberDM6 INT,
@numberDM7 INT;
WHILE @i <= 3500000
BEGIN
BEGIN TRY
SET @nameuser = (SELECT CONVERT(varchar(255), NEWID()))
SET @number1 = (SELECT RAND())
SET @number2 = @number1 * 10
SET @number3 = CAST(REPLACE(@number1, '.','') AS INTEGER)
IF @i % 2 = 0
SET @numberBIT = 0
ELSE
SET @numberBIT = 1
INSERT INTO dbo.USERS (nameuser, number1, number2, number3)
VALUES(@nameuser, @number1, @number2, @number3)
SET @numberDM3 = (SELECT CAST(REPLACE((RAND() * 11), '.','') AS INT))
SET @numberDM4 = (SELECT CAST(REPLACE((RAND() * 12), '.','') AS INT))
SET @numberDM5 = (SELECT CAST(REPLACE((RAND() * 13), '.','') AS INT))
SET @numberDM6 = (SELECT CAST(REPLACE((RAND() * 14), '.','') AS INT))
SET @numberDM7 = (SELECT CAST(REPLACE((RAND() * 15), '.','') AS INT))
INSERT INTO dbo.DATA_MASS01 (id, msg1, msg2, msg3, msg4, msg5,
number1, number2, number3, number4, number5, number6, number7)
-- BIT DECIMAL(15,2)
VALUES(
@i, -- ID
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG1
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) +
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG2
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG3
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG4
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG5
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
@numberBIT, -- @number1
RAND() * 10, -- @number2
@numberDM3, -- @number3
@numberDM4, -- @number4
@numberDM5, -- @number5
@numberDM6, -- @number6
@numberDM7 -- @number7
)
INSERT INTO dbo.DATA_MASS02 (msg1, msg2, number1, number2, number3)
VALUES(
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG1
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) +
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + -- MSG2
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()),
@numberBIT, -- @number1
CAST(REPLACE((RAND() * 35), '.','') AS INT), -- @number2
CAST(REPLACE((RAND() * 35), '.','') AS INT) -- @number3
)
IF ((@number1 * 10) < 7)
SET @i = @i + 1
ELSE
SET @i = @i + 2
PRINT '================> ID: ' +CAST(@i AS VARCHAR(8000))
END TRY
BEGIN CATCH
PRINT '================> @numberDM3: ' + CAST(@numberDM3 AS VARCHAR(8000))
PRINT '================> @numberDM4: ' + CAST(@numberDM4 AS VARCHAR(8000))
PRINT '================> @numberDM5: ' + CAST(@numberDM5 AS VARCHAR(8000))
PRINT '================> @numberDM6: ' + CAST(@numberDM6 AS VARCHAR(8000))
PRINT '================> @numberDM7: ' + CAST(@numberDM7 AS VARCHAR(8000))
END CATCH
END
/*
DELETE USERS
DELETE DATA_MASS01
DELETE DATA_MASS02
TRUNCATE TABLE USERS
TRUNCATE TABLE DATA_MASS01
TRUNCATE TABLE DATA_MASS02
ROLLBACK
COMMIT
*/
-- RANDOM CARACTER
SELECT CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID())
-- RANDOM NUMBER
DECLARE @A DECIMAL(9,2)
SET @A = (SELECT RAND())
SELECT @A, @A*10, CASE WHEN ((@A*10) > 5) THEN 'MAIOR QUE 5' ELSE 'MENOR OU 5' END
SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder
select CAST(REPLACE((RAND() * 55), '.','') AS INT)
SELECT COUNT(ID) FROM USERS -- 2682672
SELECT COUNT(ID) FROM DATA_MASS01 -- 2682558
SELECT COUNT(ID) FROM DATA_MASS02 -- 2682551
SELECT CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) +
CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID()) + CONVERT(varchar(255), NEWID())
SELECT RAND() * 1000
DELETE DATA_MASS01
SELECT TOP 100 * FROM USERS
SELECT TOP 5 * FROM DATA_MASS01 WHERE number1 = 0
SELECT COUNT(ID) FROM DATA_MASS01 WHERE number1 = 0
UPDATE DATA_MASS01
SET number1 = CASE WHEN (id % 2 = 0) THEN 1 ELSE 0 END
-- ROWS
-- TIME
SELECT max(id)
FROM DATA_MASS01 M01
INNER JOIN USERS USR ON USR.id = M01.id