我的情况是这样的:
我有这些table:
CREATE TABLE [dbo].[HeaderResultPulser]
(
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ReportNumber] CHAR(255) NOT NULL,
[ReportDescription] CHAR(255) NOT NULL,
[CatalogNumber] NCHAR(255) NOT NULL,
[WorkerName] NCHAR(255) DEFAULT ('') NOT NULL,
[LastCalibrationDate] DATETIME NOT NULL,
[NextCalibrationDate] DATETIME NOT NULL,
[MachineNumber] INT NOT NULL,
[EditTime] DATETIME NOT NULL,
[Age] NCHAR(255) DEFAULT ((1)) NOT NULL,
[Current] INT DEFAULT ((-1)) NOT NULL,
[Time] BIGINT DEFAULT ((-1)) NOT NULL,
[MachineName] NVARCHAR(MAX) DEFAULT ('') NOT NULL,
[BatchNumber] NVARCHAR(MAX) DEFAULT ('') NOT NULL,
CONSTRAINT [PK_HeaderResultPulser]
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[ResultPulser]
(
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[ReportNumber] CHAR(255) NOT NULL,
[BatchNumber] CHAR(255) NOT NULL,
[DateTime] DATETIME NOT NULL,
[Ocv] FLOAT(53) NOT NULL,
[OcvMin] FLOAT(53) NOT NULL,
[OcvMax] FLOAT(53) NOT NULL,
[Ccv] FLOAT(53) NOT NULL,
[CcvMin] FLOAT(53) NOT NULL,
[CcvMax] FLOAT(53) NOT NULL,
[Delta] BIGINT NOT NULL,
[DeltaMin] BIGINT NOT NULL,
[DeltaMax] BIGINT NOT NULL,
[CurrentFail] BIT DEFAULT ((0)) NOT NULL,
[NumberInTest] INT NOT NULL
);
每行 HeaderResultPulser
我有多行 ResultPulser
我的键是[headerresultpulser].[reportnumber],用于获取数据列表 ResultPulser
,并且对于每一行都有相同的 [ResultPulser].[ReportNumber]
它有多个 [ResultPulser].[NumberInTest]
价值观
例如:在 ResultPulser
表数据可以如下所示:
ReportNumber | NumberInTest
-------------+-------------
0000006211 | 1
0000006211 | 2
0000006211 | 3
0000006211 | 4
0000006211 | 5
0000006211 | 6
0000006212 | 1
0000006212 | 2
0000006212 | 3
0000006212 | 4
0000006212 | 5
``` `NumberInTest` 可能是200,500,10000甚至更多。。
report number列包含两个字符,前7个字符是机器的数字,其余的是递增的数字。
例如,0000006212是[0000006][212]==[机器编号][递增编号]
我的查询例如:
select
[HeaderResultPulser].[ReportNumber],
max(NumberInTest) as TotalCells
from
ResultPulser, HeaderResultPulser
where
((([ResultPulser].[ReportNumber] like '0000006%' and
CONVERT(INT, SUBSTRING([ResultPulser].[ReportNumber], 8, LEN([ResultPulser].[ReportNumber]))) BETWEEN '211' AND '815')
and ([HeaderResultPulser].[ReportNumber] = [ResultPulser].[ReportNumber])))
group by
[HeaderResultPulser].[ReportNumber]
实际上,我想得到机器0000006上的所有行,这个数字是211到815(包括两者)
此查询大约需要6-7秒
有大量的数据(在数亿和数十亿的数据中,将来在resultpulser表中可能会有更多,甚至更多),它可以在headerresultpulser表中获得数万行数据
在获得接收时,我只能选择几百个,最坏的情况是一千个或两千个左右,如果我想走得更远。。。但是(在数量上)要得到 `max(NumberInTest)` 从 `ResultPulser` 我拿了大约(可以到几百万行)
有没有办法优化我的查询?或者当有这么多数据的时候,这一次就必须这么做(就是这样)
4条答案
按热度按时间o0lyfsai1#
执行连接的方式不再是标准的。它也很难阅读,如果你需要使用左连接的话也很危险。而不是以这种方式加入:
请改用ansi-92联接语法:
你说你的“钥匙”是
ReportNumber
. 为什么不在你的模式中声明呢?听起来你想对HeaderResultPulser.ReportNumber
,和foreign key
在ReportPulser
table,这样ReportNumber references HeaderResultPulser (ReportNumber)
因为您的report number列似乎包含两个不同的值,所以您的表不是第一范式。这让你很为难。输入数据时,为什么不将“报告编号”的两部分分成两个不同的列?这将显著提高查询性能,因为您不再需要在查询时对表中的数据执行表达式来分隔数据ReportNumber
变成原子值。你的评论说
ReportNumber
是MachineNumber
. 但你已经有了MachineNumber
在HeaderReportPulser
table。那么,为什么不单独为Increment
? 如果你还需要ReportNumber
要作为列存在,可以将其作为计算列,作为MachineNumber
以及Increment
.如果您不想触及“现有”模式,我们可以反过来做类似的事情。除非您能对模式做些什么,否则您的查询将不完全可搜索,因为您必须对模式中的数据执行某种表达式
ReportNumber
列。但也许你可以选择使用一个计算列来提前完成:现在我们将增量本身作为一列。但它仍然在查询时计算,因为它不是持久的。我们可以做到:
我们还可以索引计算列。由于所需的表达式是确定性和精确的(请参见计算列上的索引),因此实际上我们不必将其标记为持久化:
您可以执行一组类似的操作来创建
Increment
以及MachineNumber
上ReportPulser
table。如果您总是想同时使用这两个值,请在这两个值的组合上创建一个索引(MachineNumber, Increment)
uoifb46i2#
最大的性能提升可能是消除了外部
group by
通过使用相关子查询或横向联接:你的逻辑似乎只需要最后三个字符
ReportNumber
,所以我简化了逻辑。我并不是百分之百这么认为的——这似乎是合理的。无论如何,不需要将值转换为整数,然后作为字符串进行比较。类似的逻辑甚至可以用于更长的报告编号。你还需要一个索引
ResultPulser(ReportNumber, NumberInTest)
:编辑:
实际上,我注意到两个表之间的报表编号是匹配的。所以这看起来很简单:
你还是要确定你有上面的索引
ResultPulser
.如果
ReportNumber
不是固定的10位数,则可以使用:这还应该使用索引并准确返回所需内容。
mftmpeh83#
任何查询的性能优化都取决于许多因素,包括承载和运行查询的环境。硬件和软件在繁重的数据库查询优化中起着重要的作用。在您的案例中,您可以研究以下内容:
使用ansi 92连接语法,而不是默认的交叉连接,例如,选择*from t1 join t2 on t1.column=t2.column
在[reportnumber][numberTest]这样的列上放置索引注意:对于连接区域中不是主键的每一列,可能都需要索引。
记住,max的使用总是很繁重,这可能是查询中的主要问题。
最后,您可以使用以下在线工具进一步优化查询语法,您可以指定实际的查询和使用的环境:
https://www.eversql.com/
希望对你有帮助。
ckocjqey4#
如果您真的想优化性能,我建议在sql结构之外添加一些逻辑。reportnumber的特定值是否可能存在于表resultpulser中,而不存在于表headerresultpulser中?如果不是,我也这么认为,没有理由加入表headerresultpulser。然后,我建议利用事实,reportnumber上的条件可以等价地表示,而不需要在子字符串中进行划分。例如,条件
相当于:
所以建议是:
在表resultpulser(reportnumber,NumberTest)上创建索引
使用与此类似的选项:
(请根据您的喜好添加括号或双引号和大写)
我希望好的数据库能够通过索引访问来执行这个查询,并且从执行的Angular 来看它是最佳的。性能不仅取决于执行路径,还取决于设置和硬件。请确保您的数据库有足够的缓存和快速磁盘访问。并发负载也是非常重要的。
简单地将字段reportnumber拆分为[machine number]和[incrementing number]可能不会提高我建议的表单中查询的性能。但是对于其他形式的访问(其他where类)可能非常方便。它将反映案件的结构。更重要的是:它会把你从强加的限制中释放出来。目前,[递增数字]有3位数字。你确定一台[机器编号]不需要超过999台吗?
当只使用10个字符时,为什么字段reportnumber的类型为char(255)?char(255)的长度是固定的,所以这会严重浪费空间。只有数据库压缩才有帮助。已用空间对性能有很大影响–请考虑上面关于数据库缓存的评论。
如果这两个字段,[机器编号],[递增编号]都是整数,为什么不拆分reportnumber并使用整数类型呢?
旁注:字段名建议您搜索表resultpulser中的行总数,这些行属于表headerresultpulser中的单个条目。仅当numbertest中的数字是连续的,没有空格时,建议的查询才会提供此信息。如果没有提供,则必须计算它们,而不是求最大值。