关于提高查询性能的建议~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 Stack Overflow用户
发布于 2017-09-26 03:01:41
我同意其他人的观点，清理这些字符串值将是一个好主意。但是，由于您仍然需要完成这项任务，而且我非常讨厌循环和标量函数，所以我决定使用一个内联表值函数，而不是这两个嵌套的标量函数。我在这里没有使用任何循环，性能可能会让你大吃一惊。
为此，我使用了一个计数表或数字表。我喜欢把其中的一个放在周围作为风景。下面是我使用的视图的代码。
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO然后，您可以使用此计数表来派生一种基于集合的方法，以适应您用于确定两个值是否匹配的业务规则。这里也不需要逗号分隔符。在您的示例中，您需要删除值列表中几乎每隔一个字符就有一个逗号。每个字符的单个实例就足够了。
create function [dbo].[fn_TESTMatcher_Sean]
(
@Field1 nvarchar(max)
, @Field2 nvarchar(max)
, @CharsToRemove nvarchar(max)
) returns table as
RETURN
with MyValues1 as
(
select substring(@Field1, N, 1) as MyChar
, t.N
from cteTally t
where N <= len(@Field1)
and charindex(substring(@Field1, N, 1), @CharsToRemove) = 0
)
, MyValues2 as
(
select substring(@Field2, N, 1) as MyChar
, t.N
from cteTally t
where N <= len(@Field2)
and charindex(substring(@Field2, N, 1), @CharsToRemove) = 0
)
select convert(bit, case when mv1.MyResult = mv2.MyResult then 1 else 0 end) as IsMatch
from
(
select distinct MyResult =
replace(
replace(replace(STUFF((select MyChar + ''
from MyValues1 mv2
order by mv2.N
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 0, '')
, ' RD ', ' ROAD ')
, ' ST ', ' STREET ')
, ' ', '')
from MyValues1 mv
) mv1
cross join
(
select distinct MyResult =
replace(
replace(replace(STUFF((select MyChar + ''
from MyValues2 mv2
order by mv2.N
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 0, '')
, ' RD ', ' ROAD ')
, ' ST ', ' STREET ')
, ' ', '')
from MyValues2 mv
) mv2
;试一试，让我知道这是否适用于您的环境。
例如：
select *
from fn_TESTMatcher_Sean('123 any st rd or something', '123 any street road or something', '%[~,@#^_+-$%&/|\*().!`:<>?]%')以上返回1，因为它们在您定义的规则下是匹配的。
https://stackoverflow.com/questions/46409138
复制相似问题
腾讯云开发者
