代码之家 › 专栏 › 技术社区 › OrganicPanda

大表建议(SQL Server)

sql-server-2000 coldfusion sql-server

1

OrganicPanda · 技术社区 · 16 年前

CREATE TABLE [dbo].[log](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [logfile_id] [int] NOT NULL,
    [test_id] [int] NOT NULL,
    [timestamp] [datetime] NOT NULL,
    [value] [float] NOT NULL,
CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

SET NOCOUNT ON

CREATE TABLE ##RowNumber ( RowNumber int IDENTITY (1, 1), log_id char(9) )

INSERT ##RowNumber (log_id)
SELECT l.id
FROM log l, logfile lf
WHERE lf.server_id = #arguments.server_id#
and l.test_id = #arguments.test_id#"
and l.timestamp >= #arguments.report_from#
and l.timestamp < #arguments.report_to#
and l.logfile_id = lf.id
order by l.timestamp asc

select rn.RowNumber, l.value, l.timestamp
from log l, logfile lf, ##RowNumber rn
where lf.server_id = #arguments.server_id#
and l.test_id = #arguments.test_id#
and l.logfile_id = lf.id
and rn.log_id = l.id
and ((rn.rownumber % #modu# = 0) or (rn.rownumber = 1)) 
order by l.timestamp asc

DROP TABLE ##RowNumber

SET NOCOUNT OFF

(对于非CF开发人员) #value# 插入 value 和 ## 映射到 # )

我基本上创建了一个临时表,这样我就可以使用rownumber来选择每x行。这样,我只选择可以显示的行数。这有帮助,但仍然非常缓慢。

SQL Server Management Studio告诉我,我的索引如下(我几乎不知道如何正确使用索引):

IX_logfile_id (Non-Unique, Non-Clustered)
IX_test_id (Non-Unique, Non-Clustered)
IX_timestamp (Non-Unique, Non-Clustered)
PK_log (Clustered)

干杯(抱歉发了这么长的帖子)

3 回复 | 直到 13 年前

1

4

Remus Rusanu 16 年前

您的问题是选择了错误的群集密钥。从来没有人对按ID检索某个特定日志值感兴趣。如果您的系统与我见过的任何其他系统一样,那么所有查询都会要求:

日期范围内所有服务器的所有计数器
一系列日期的所有服务器上的特定计数器值
一台服务器在一系列日期内的所有计数器
特定服务器在一系列日期内的特定计数器

index tipping point

我要假装我真的知道你的要求。您必须忘记关于存储的常识,在每个非聚集索引中实际复制所有数据。以下是我的建议:

在[id]上删除聚集索引是一个毫无用处的方法。
NC索引on(日志文件id,时间戳)包括(值)
NC索引打开(测试id,时间戳)包括(值)
添加维护任务以定期重新组织所有索引,因为它们容易出现碎片

您注意到我没有对您的查询脚本发表任何评论。这是因为在这个世界上,您无法做任何事情使查询在现有的表结构上运行得更快。

假装我知道你的要求。但实际上我没有。我只是举了一个可能结构的例子。您真正应该做的是研究主题,并找出适合您需求的正确索引结构:

此外,谷歌的“覆盖索引”也会带来很多好文章。

2

1

marc_s MisterSmith 10 年前

我想到了几件事。

你需要保留那么多数据吗?如果没有,请考虑创建一个存档表,如果您想保留它(但不要每次运行查询时只将它与主表连接)。

http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

服务器id字段上似乎缺少索引。我会考虑使用这个字段和其他创建覆盖索引。这里也有一篇关于这方面的文章。

http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx

编辑

在如此短的时间内,由于表中有这么多行,我还将检查索引是否存在碎片,这可能是导致速度缓慢的原因。在SQLServer2000中,可以使用 DBCC SHOWCONTIG

有关详细信息,请参阅此链接 http://technet.microsoft.com/en-us/library/cc966523.aspx

另外,请注意,我已经将这些项目编号为1,2,3,4,但是编辑器会自动重置它们

3

0

Adriaan Stander 16 年前

DECLARE @Table TABLE(
        TimeVal DATETIME
)

DECLARE @StartVal INT
DECLARE @EndVal INT

SELECT  @StartVal = 51, @EndVal = 100

SELECT  *
FROM    (
            SELECT  TOP (@EndVal - @StartVal + 1)
                    *
            FROM    (
                        --select up to end number
                        SELECT  TOP (@EndVal)
                                *
                        FROM    @Table
                        ORDER BY TimeVal ASC
                    ) PageReversed
            ORDER BY TimeVal DESC
        ) PageVals
ORDER BY TimeVal ASC

例如

SELECT  *
FROM    (
            SELECT  TOP (@EndVal - @StartVal + 1)
                    *
            FROM    (
                        SELECT TOP (@EndVal)
                                l.id,
                                l.timestamp
                        FROM log l, logfile lf
                        WHERE lf.server_id = #arguments.server_id#
                        and l.test_id = #arguments.test_id#"
                        and l.timestamp >= #arguments.report_from#
                        and l.timestamp < #arguments.report_to#
                        and l.logfile_id = lf.id
                        order by l.timestamp asc
                    ) PageReversed ORDER BY timestamp DESC
        ) PageVals
ORDER BY timestamp ASC