gpt4 book ai didi

SQL Server : MERGE performance

转载 作者:行者123 更新时间:2023-12-04 00:41:38 24 4
gpt4 key购买 nike

我有一个包含 500 万行的数据库表。聚集索引是自增标识列。还有PK是生成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 table 。 temp表结构如下(与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);
    }

    然后我运行合并命令来更新 showCountstore_image通过从基于 imageSHAID 的临时表合并来创建表.如果图像当前不存在于 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 - 100000MS)。当批量插入运行时,我可以看到大约 1MB-2MB/秒的磁盘事件,低 CPU 使用率。

    这是这种数据量的典型写入时间吗?对我来说似乎有点慢,是什么导致了性能缓慢?当然与 imageSHAID被索引,我们应该期待比这更快的搜索时间?

    任何帮助,将不胜感激。

    谢谢你的时间。

    最佳答案

    您的 UPDATE MERGE中的条款更新 showCount .这需要对聚集索引进行键查找。

    但是,聚集索引也被声明为非唯一的。即使底层列是唯一的,这也会向优化器提供信息。

    所以,我会做出这些改变

  • 集群主键为 autoIncID
  • 当前PK上imageSHAID成为独立的唯一索引(非约束)并为 showCount 添加 INCLUDE .唯一约束不能有 INCLUDE

  • 更多观察:
  • 你不需要nvarchar用于哈希或 URL 列。这些不是 unicode。
  • 散列也是固定长度的,所以可以是 char(64) (对于 SHA2-512)。
  • 列的长度定义了分配给查询的内存量。查看更多信息:is there an advantage to varchar(500) over varchar(8000)?
  • 关于SQL Server : MERGE performance,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18143533/

    24 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com