我有一个拥有500万行的数据库表。聚集索引是自增长标识列。主键是一个256字节的VARCHAR
代码,该代码是URL的SHA256哈希值,这是表上的非聚集索引。
表格如下:
CREATE TABLE [dbo].[store_image](
[imageSHAID] [nvarchar](256) NOT NULL,
[imageGUID] [uniqueidentifier] NOT NULL,
[imageURL] [nvarchar](2000) NOT NULL,
[showCount] [bigint] NOT NULL,
[imageURLIndex] AS (CONVERT([nvarchar](450),[imageURL],(0))),
[autoIncID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_imageSHAID] PRIMARY KEY NONCLUSTERED
(
[imageSHAID] 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 CLUSTERED INDEX [autoIncPK] ON [dbo].[store_image]
(
[autoIncID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
imageSHAID
是一个图像URL的SHA256哈希,例如 "http://blah.com/image1.jpg",它被哈希成长度为256的varchar。imageGUID
是一个生成的代码guid,用于标识图像(稍后将用作索引,但目前我已将此列省略为索引)。imageURL
是图像的完整URL(最多2000个字符)。showCount
是显示图像的次数,每次显示此特定图像时都会增加此计数器。imageURLIndex
是一个由450个字符限制的计算列,这样我可以对imageURL进行文本搜索(如果我选择),它是可索引的(索引也因简洁而省略)。autoIncID
是聚集索引,应该允许更快地插入数据。
定期地,我会从临时表合并到store_image
表中。 临时表结构如下(与store_image表非常相似):
CREATE TABLE [dbo].[store_image_temp](
[imageSHAID] [nvarchar](256) NULL,
[imageURL] [nvarchar](2000) NULL,
[showCount] [bigint] NULL,
) ON [PRIMARY]
GO
当合并过程运行时,我使用以下代码将
DataTable
写入临时表:using (SqlBulkCopy bulk = new SqlBulkCopy(storeConn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null))
{
bulk.DestinationTableName = "[dbo].[store_image_temp]";
bulk.WriteToServer(imageTableUpsetDataTable);
}
然后我运行合并命令,根据imageSHAID
从临时表中合并更新store_image
表中的showCount
。如果该图像当前不存在于store_image
表中,我会创建它:
merge into store_image as Target using [dbo].[store_image_temp] as Source
on Target.imageSHAID=Source.imageSHAID
when matched then update set
Target.showCount=Target.showCount+Source.showCount
when not matched then insert values (Source.imageSHAID,NEWID(), Source.imageURL, Source.showCount);
我通常尝试将临时表中的2k-5k行合并到store_image
表中的任何一个合并过程中。
我曾经在SSD上运行这个数据库(只连接了SATA 1),速度非常快(低于200毫秒)。由于SSD上的空间不足,我将数据库换成了1TB的7200缓存旋转硬盘,自那时以来完成时间超过了6-100秒(6000-100000毫秒)。当批量插入正在运行时,我可以看到大约1MB-2MB/sec的磁盘活动,CPU使用率很低。
这是这个数据量的典型写入时间吗?对我来说似乎有点慢,是什么导致了性能下降?毕竟,由于imageSHAID
被索引,我们应该期望比这更快的搜索时间,对吗?
如果有任何帮助,将不胜感激。
谢谢你的时间。