sql server 2005中单表数据访问太慢
以下是表的脚本。从该表访问数据太慢。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emails](
[id] [int] IDENTITY(1,1) NOT NULL,
[datecreated] [datetime] NULL CONSTRAINT [DF_Emails_datecreated]
DEFAULT (getdate()),
[UID] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[From] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[To] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[Subject] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Body] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[HTML] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[AttachmentCount] [int] NULL,
[Dated] [datetime] NULL
) ON [PRIMARY]
以下查询需要 50 秒才能获取数据。
select id, datecreated, UID, [From], [To], Subject, AttachmentCount,
Dated from emails
如果我在选择中包含 Body 和 Html ,那么时间会更糟。
索引打开:
- id 唯一聚集
- 从非唯一非聚集
- 到 非唯一非聚集
表当前有 180000 多条记录。
每个月可能有 100,000 条记录,因此随着时间的推移,速度会变得更慢。
将数据拆分成两个表可以解决问题吗? 还应该有哪些其他索引?
Following is the script of table. Accessing data from this table is too slow.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emails](
[id] [int] IDENTITY(1,1) NOT NULL,
[datecreated] [datetime] NULL CONSTRAINT [DF_Emails_datecreated]
DEFAULT (getdate()),
[UID] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[From] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[To] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[Subject] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Body] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[HTML] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[AttachmentCount] [int] NULL,
[Dated] [datetime] NULL
) ON [PRIMARY]
Following query takes 50 seconds to fetch data.
select id, datecreated, UID, [From], [To], Subject, AttachmentCount,
Dated from emails
If I include Body and Html in select then time is event worse.
indexes are on:
- id unique clustered
- From Non unique non clustered
- To Non unique non clustered
Tabls has currently 180000+ records.
There might be 100,000 records each month so this will become more slow as time will pass.
Does splitting data into two table will solve the problem?
What other indexes should be there?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
几乎可以肯定是数据量造成了问题。因此,除非需要,否则不应获取
Subject
列。即使获取SUBSTRING(Subject, 100)
也可能会明显更快。这可能无关紧要,但如果 BLOB 列不是行中的最后一个列,旧版本的 SQL Server 就会受到影响,因此作为实验,我将移动
[AttachmentCount]
和[Dated ]
三列nvarchar(max)
上方。It's almost certainly the volume of the data that's causing a problem. Because of this, you should not fetch the
Subject
column until you are need it. Even fetchingSUBSTRING(Subject, 100)
may be noticeably faster.This may be irrelevant, but older versions of SQL Server suffered if the BLOB columns weren't the last in the row, so just as an experiment I'd move
[AttachmentCount]
and[Dated]
above the threenvarchar(max)
columns.