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
| IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56]') AND type in (N'U'))
DROP TABLE [dbo].[test56]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56_alt]') AND type in (N'U'))
DROP TABLE [dbo].[test56_alt]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56_1]') AND type in (N'U'))
DROP TABLE [dbo].[test56_1]
GO
CREATE TABLE test56 (
cle INT
, valeur INT
)
GO
CREATE TABLE test56_alt (
cle INT
, valeur INT
, erased BIT NOT NULL
)
GO
CREATE NONCLUSTERED INDEX IDX_cle ON test56(cle)
GO
DECLARE @D DATETIME;
DECLARE @DD DATETIME;
SET @D = GETDATE();
SET @DD = @D;
INSERT test56 (
cle
, valeur
)
SELECT
1
, n
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
DELETE TOP(33) PERCENT test56
WHERE cle = 1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
DELETE TOP(55) PERCENT test56
WHERE cle = 1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
DELETE test56
WHERE cle = 1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D;
INSERT test56_alt (
cle
, valeur
, erased
)
SELECT
1
, n
, 0
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
UPDATE TOP(33) PERCENT test56_alt
SET erased = 1
WHERE cle = 1
AND erased = 0
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
UPDATE TOP(55) PERCENT test56_alt
SET erased = 1
WHERE cle = 1
AND erased = 0
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
DELETE test56_alt
WHERE cle = 1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D;
SELECT
n
INTO test56_1
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
DELETE TOP(33) PERCENT test56_1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
DELETE TOP(55) PERCENT test56_1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
DROP TABLE test56_1
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D; |
Partager