关于提高查询性能的建议~1天
我有5个表-每个表有数万条记录
1个主要/非常重要的表(表A) 2个仍然重要但不如表重要的其他表(表B/C)
包含A<=>B和A<=>C之间的主键的两个侧表(表D/E)，即每个表只有两列
这3个主表各有大约140列，它们都有相同的列名
我的查询的目的是在一个查询中执行所有表A<=>D<=>B和A<=>E<=>C之间的列级匹配
最终查询将具有大约286列(来自每个主表的两个ID列，
select tableA.ID1 as [TABLEAID1],
tableA.ID2 as [TABLEAID2],
tableB.ID1 as [TABLEBID1],
tableB.ID2 as [TABLEBID2],
tableC.ID1 as [TABLECID1],
tableC.ID2 as [TABLECID2],
fn_TESTMatcher(tableA.[postCode], tableB.[postCode],) as
[TABLEAB.postCode.RESULT],
fn_TESTMatcher(tableA.[CityCode], tableB.[CityCode],) as
[TABLEAB.CityCode.RESULT],
.
.
. x238 more 'fn_TESTMatcher(...) as xyz' columns
.
INTO #Results
From tableA WITH (NOLOCK)
FULL JOIN tableD WITH (NOLOCK) ON tableA.ID1 = tableD.A
) FULL JOIN tableB WITH (NOLOCK) ON tableD.B = tableB.ID1
) FULL JOIN tableE WITH (NOLOCK) ON tableA.ID1 = tableE.A
) FULL JOIN tableC WITH (NOLOCK) ON tableE.B = tableC.IDfn_TESTMatcher是一个函数，它从两个主表输入相同的列，然后删除/替换特殊字符/缩写，然后尝试匹配它们，如果匹配，则返回位“1”，如果不匹配，则返回位“0”。
目前它需要大约一天的时间来运行(我不能用某种查询计时器对它进行计时)，我可以注释掉除最后一列之外的所有列并运行它，而且运行起来相当快，但我不认为我可以简单地扩展它
有人有什么建议吗？我的第一个假设是开始用谷歌搜索什么是索引和...maybe。将它应用于每个表的ID1，尽管我对a)弄乱我的表和b)添加一个最终无用的索引有一点犹豫
===========================================更新2:表结构方面所有主表的所有列都是可变字符，长度为100-250个字符，其中ID (主键)不可为空
对于两边的表，它们只有两列，都是varchar，100个字符的限制(它们都是外键)。其中最重要的表的ID不可为空
对于函数，从技术上讲我有两个：
FUNCTION [dbo].[fn_TESTStripCharacters]
(
@String NVARCHAR(MAX) ,
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @expres VARCHAR(50) = '%[~,@,#,^,_,+,-,$,%,&,/,|,\,*,(,),.,!,`,:,<,>,?]%'
WHILE PATINDEX( @expres, @String ) > 0
SET @String = REPLACE(REPLACE(REPLACE( @String, SUBSTRING( @String, PATINDEX( @expres, @String ), 1 ),''),';',''),'-','')
RETURN @String
END和第二个函数
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_TESTMatcher](@Field1 NVARCHAR(MAX), @Field2
NVARCHAR(MAX))
RETURNS BIT
BEGIN
SET @Field1 = UPPER(LTRIM(RTRIM(REPLACE(dbo.fn_TESTStripCharacters(@Field1,@SpecialCharacters),'-',''))))
SET @Field2 = UPPER(LTRIM(RTRIM(REPLACE(dbo.fn_TESTStripCharacters(@Field2,@SpecialCharacters),'-',''))))
SET @Field1 = REPLACE(@Field1,' RD ',' ROAD ')
SET @Field2 = REPLACE(@Field2,' RD ',' ROAD ')
SET @Field1 = REPLACE(@Field1,' ST ',' STREET ')
SET @Field2 = REPLACE(@Field2,' ST ',' STREET ')
SET @Field1 = REPLACE(@Field1,' ','')
SET @Field2 = REPLACE(@Field2,' ','')
RETURN
CASE WHEN @Field1=@Field2
THEN '1'
ELSE '0'
END
END=============================更新2
示例表数据-假设所有3个表中都存在相同的两条记录(并不总是如此)
TableA (main + most important table):
ID1 ID2 postCode, cityCode, ................
10001 1221 IG11PJ London ................
10230 1022 IG22PJ Nottingham ................
tableB (slightly less important table)
ID1 ID2 postCode, cityCode, ................
10031 1011 IG1 1PJ london ................
10980 982 IG2 2PJ nottingham ................
tableC (slightly less important table)
ID1 ID2 postCode, cityCode, ................
10551 1011 iG1 1pj london ................
20980 982 iG2 2pJ nottingham ................
tableD (side table)
A B
10001 10031
10230 10980
table E (side table)
A B
10001 10551
10230 20980 回答 4
Stack Overflow用户
发布于 2017-09-26 00:34:23
如果表A、B和C应该是相同的，除了格式差异，我建议您创建3个CTE，第一个选择TableA ID和所有其他列的HASHBYTES (列将需要转换为char/varchar，以便任何格式和替换都可以在那里进行)，第二个CTE对表B相同，第三个CTE用于表C。
然后只需匹配HASHBYTES值。正如已经说过的，没有样本数据，表结构，函数的DDL等，我们只是猜测。
Sean和Milney在标量vs内联表函数和NOLOCK的使用方面都提出了非常好的观点
Stack Overflow用户
发布于 2017-09-26 01:31:54
我认为这些任务不属于一个查询。我会创建一组新的表(或者这些表是备份的/不需要保留数据)，然后对这些新表执行数据清理步骤。
一旦您对数据进行了标准化，然后执行一次查询来比较这些表。
试图将所有这些放在一个查询中不会带来任何好处，也不会一步步地取得进展。例如，如果你发现你忘记了从一次字段中去掉空格，你必须重做所有的事情。如果您使用“清理过的”数据创建新的表，您可以逐步投入时间来清理数据(这显然是此过程中最慢的部分)，直到数据完美为止，然后运行快速比较。忘记了一些东西--这是一个相对快速的更新和运行。
Stack Overflow用户
发布于 2017-09-26 01:58:46
我建议这样做，而不是让您头疼地运行，复制所有内容，然后尝试基于无法优化的函数进行解析。您声明您有一个列被剥离了特殊字符。我会为每个表和表示的列添加一个"CleanKey“列。然后，通过可能的表触发器，或者在添加/保存之前，将该值预先清除到"CleanKey“列中，这样就完成了。然后在这些"Clean“列上建立索引，并进行直接连接。
由于系统的其余部分不知道这些" clean“列，因此您可以添加这些列，从您拥有的任何功能中清除它们，而不必担心复制或以其他方式破坏其他数据。
是的，可能需要花一点时间来“清理”这些列，但之后就完成了。在此之后，您的查询应该会很快。
https://stackoverflow.com/questions/46409138
复制相似问题
腾讯云开发者
