SQL Server - Performance test against huge dataset

为了测试SQL SERVER的性能,这里创建了两个表,如下所示:

+-----  tSeries  -----+       +-----  tImage  -----+
| SeriesUID    : guid | <>--- | ImageUID    : guid |
| SeriesNumber : int  |       | SeriesUID   : guid |
+---------------------+       | ImageNumber : int  |
                              +--------------------+

创建测试表

CREATE TABLE [dbo].[tSeries](
    [SeriesUID] [UNIQUEIDENTIFIER] NOT NULL,
    [SeriesNumber] [INT] NULL,
 CONSTRAINT [PK_tSeries] PRIMARY KEY CLUSTERED 
(
    [SeriesUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[tImage](
    [ImageUID] [UNIQUEIDENTIFIER] NOT NULL,
    [SeriesUID] [UNIQUEIDENTIFIER] NOT NULL,
    [ImageNumber] [INT] NULL,
 CONSTRAINT [PK_tImage] PRIMARY KEY CLUSTERED 
(
    [ImageUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[tImage]  WITH CHECK ADD  CONSTRAINT [FK_tImage_tSeries] FOREIGN KEY([SeriesUID])
REFERENCES [dbo].[tSeries] ([SeriesUID])
GO
 
ALTER TABLE [dbo].[tImage] CHECK CONSTRAINT [FK_tImage_tSeries]
GO

插入一亿条数据,耗时大约90分钟

SET NOCOUNT ON
 
DECLARE @i AS INT
SET @i = 0

INSERT INTO tSeries(SeriesUID, SeriesNumber) VALUES(NEWID(), 6)

DECLARE @SeriesUID AS UNIQUEIDENTIFIER
SELECT @SeriesUID = SeriesUID FROM dbo.tSeries WHERE SeriesNumber = 6
PRINT 'SeriesUID: ' + CONVERT(nvarchar(MAX), @SeriesUID)
 
WHILE (@i < 100000000)
BEGIN
    INSERT tImage (ImageUID, SeriesUID, ImageNumber) VALUES (
        NEWID(),
        @SeriesUID,
        @i
    )
    SET @i += 1
END

重启SQL SERVER服务,并执行一次Count查询,耗时25秒,内存消耗从5.7GB上升到12.8GB

SELECT COUNT(*) FROM tImage a
JOIN dbo.tSeries b ON a.SeriesUID = b.SeriesUID
WHERE b.SeriesNumber = 6

执行一次删除操作,耗时1秒

DELETE a FROM dbo.tImage a
JOIN dbo.tSeries b ON a.SeriesUID = b.SeriesUID
WHERE b.SeriesNumber = 6 AND a.ImageNumber = 9999

再次查询总条数,耗时4秒

SELECT COUNT(*) FROM tImage a
JOIN dbo.tSeries b ON a.SeriesUID = b.SeriesUID
WHERE b.SeriesNumber = 6

执行一次插入,耗时为0秒

INSERT dbo.tImage
(
    ImageUID,
    SeriesUID,
    ImageNumber
)
VALUES
(   
    NEWID(),
    'ED65F63B-E657-4404-93E2-178EF106D8BF',
    1
)