sql - SQL Server MERGE 语句中的性能调用标量函数
问题描述
我的代码带有MERGE调用标量函数的语句，但性能很差。
这段代码可能需要我 30 分钟或更长时间，有时它可能会在中间失败。
我的代码：
MERGE INTO Table1 AS md
USING (SELECT * FROM Table2 edb
WHERE edb.FileId = @FileId
AND edb.IsRowError = 0) edbTable ON dbo.GetId(edbTable.Filed1, edbTable.Filed2, edbTable.Filed3, edbTable.Filed4, edbTable.Filed5, edbTable.Filed6) = md.Id
AND md.IsActive = 1
WHEN MATCHED THEN
UPDATE
SET .
.
.
功能：
ALTER FUNCTION [dbo].[GetId]
(@Filed1 NVARCHAR(9),
@Filed2 NVARCHAR(9),
@Filed3 NVARCHAR(13),
@Filed4 INT,
@Filed5 DATE,
@Filed6 INT)
RETURNS INT
AS
BEGIN
DECLARE @Id INT = NULL
IF ((@Filed5 IS NULL) OR (@Filed6 = 0))
BEGIN
SET @Id = (SELECT TOP 1 md.Id
FROM Table1 md with(NOLOCK)
JOIN Table3 mb with(NOLOCK) ON md.Table3Id = mb.Id
WHERE (mb.Filed1 = @Filed1 AND mb.Filed2 = @Filed2
OR mb.Filed1 = @Filed2 AND mb.Filed2 = @Filed1)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
ORDER BY md.LastUpdateDate DESC)
IF @Id IS NOT NULL
RETURN @Id
ELSE
SET @Id = (SELECT TOP 1 md.Id
FROM Table1 md with(NOLOCK)
JOIN Table3 mb with(NOLOCK) ON md.Table3Id = mb.Id
WHERE (mb.Filed1 = @Filed1 OR mb.Filed1 = @Filed1)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND Filed4 = md.Filed4
AND @Filed1 > 0
ORDER BY md.LastUpdateDate DESC)
IF @Id IS NOT NULL
RETURN @Id
ELSE
set @Id =(select top 1 md.Id from Table1 md with(NOLOCK)
join Table3 mb with(NOLOCK) on md.Table3Id = mb.Id
where (mb.Filed1 = @Filed2
or mb.Filed2 = @Filed2 )
and mb.Filed7 = 1
and @Filed3 = md.Filed3
and @Filed4 = md.Filed4
and @Filed2 >0
order by md.LastUpdateDate desc)
end
else
begin
set @Id = (select top 1 md.Id from Table1 md with(NOLOCK)
join Table3 mb with(NOLOCK) on md.Table3Id = mb.Id
where (mb.Filed1 = @Filed1 and mb.Filed2 = @Filed2
or mb.Filed1 = @Filed2 and mb.Filed2 = @Filed1 )
and mb.Filed7 = 1
and @Filed3 = md.Filed3
and @Filed4 = md.Filed4
and (@Filed5 = md.ContractDate or (@Filed5 is null and md.ContractDate is null))
and @Filed6 = md.PurchasePrice)
if @Id is not NULL
RETURN @Id
else
set @Id =(select top 1 md.Id from Table1 md with(NOLOCK)
join Table3 mb with(NOLOCK) on md.Table3Id = mb.Id
where (mb.Filed1 = @Filed1
or mb.Filed2 = @Filed1 )
and mb.Filed7 = 1
and @Filed3 = md.Filed3
and @Filed4 = md.Filed4
and (@Filed5 = md.ContractDate or (@Filed5 is null and md.ContractDate is null))
and @Filed6 = md.PurchasePrice
and @Filed1 >0)
if @Id is not NULL
RETURN @Id
else
set @Id =(select top 1 md.Id from Table1 md with(NOLOCK)
join Table3 mb with(NOLOCK) on md.Table3Id = mb.Id
where (mb.Filed1 = @Filed2
or mb.Filed2 = @Filed2 )
and mb.Filed7 = 1
and @Filed3 = md.Filed3
and @Filed4 = md.Filed4
and (@Filed5 = md.ContractDate or (@Filed5 is null and md.ContractDate is null))
and @Filed6 = md.PurchasePrice
and @Filed2 >0)
if @Id is not NULL
RETURN @Id
end
RETURN @Id
END
我需要改变什么？如何更改代码？
我需要所有的If状态，因为对检索哪个记录有偏好，我无法将其转换为join. 因为如果有的话join，他们会给我一个肯定的答案，所以它会给我一个从人群中出来的每个人的名单，重要的是要知道这是因为第一次还是第二次或第三次加入，因为我有优先事项选择。
如果我这样做而不是ifto join，有没有办法拉出第一条出去的线？也就是说：如果我加入并且第一个没有结果而第二个有结果，并且在第三个中，那将只返回第二个而不是第三个的结果 - 有这样的事情吗？
解决方案
好的，这是您的逻辑的精确复制，但作为内联表值函数。在要求相同的情况下，这很可能会进一步削减。例如mb.File7 = 1 AND @Filed3 = md.Filed3 AND @Filed4 = md.Filed4出现在每个 WHEN子句中，所以应该从所有WHEN子句中移出并放在WHERE. 这可能会使查询更高效，也更容易阅读。
无论如何，这也是同样的逻辑。我在代码中添加了一些注释，您可能想看看：
ALTER FUNCTION dbo.GetID (@Filed1 nvarchar(9), --Honestly, give these better names. These parameters don't tell you anything about what they are
--which'll make it harder for people who don't know your function to use it.
@Filed2 nvarchar(9),
@Filed3 nvarchar(13),
@Filed4 int,
@Filed5 date,
@Filed6 int)
RETURNS TABLE AS RETURN
SELECT TOP 1 --This may be a bad idea, we'll see
CASE WHEN @Filed5 IS NULL OR @Filed6 = 0 THEN CASE WHEN (mb.Filed1 = @Filed1 AND mb.Filed2 = @Filed2
OR mb.Filed1 = @Filed2 AND mb.Filed2 = @Filed1)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4 THEN md.Id
WHEN mb.Filed1 = @Filed1
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
AND @Filed1 > 0 THEN md.ID
WHEN @filed2 IN (mb.Filed1, mb.Filed2)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
AND @Filed2 >0 THEN md.ID
END
--None of these in your original had an ORDER BY clause, was this intentional?
--If so, I assume you want a random row, instead. So you'll need a further
--CASE expression in your ORDER BY for these, and then the return either NEWID or md.LastUpdateDate
ELSE CASE WHEN (mb.Filed1 = @Filed1 --Your parenthesis seem wrong here
AND mb.Filed2 = @Filed2 --YOu have an OR in the middle
OR mb.Filed1 = @Filed2 --So I doubt this does what you want
AND mb.Filed2 = @Filed1 ) --But I haven't correct, as I don't know what it should do
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
AND (@Filed5 = md.ContractDate
OR (@Filed5 IS NULL AND md.ContractDate IS))
AND @Filed6 = md.PurchasePrice THEN md.ID
WHEN (mb.Filed1 = @Filed1
OR mb.Filed2 = @Filed1)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
AND (@Filed5 = md.ContractDate
OR (@Filed5 IS NULL AND md.ContractDate IS NULL))
AND @Filed6 = md.PurchasePrice
AND @Filed1 >0 THEN md.Id
WHEN (mb.Filed1 = @Filed2
OR mb.Filed2 = @Filed2)
AND mb.Filed7 = 1
AND @Filed3 = md.Filed3
AND @Filed4 = md.Filed4
AND (@Filed5 = md.ContractDate
OR (@Filed5 IS NULL AND md.ContractDate IS NULL))
AND @Filed6 = md.PurchasePrice
AND @Filed2 >0 THEN md.Id
END
END AS Id
FROM dbo.Table1 md --Why md when the table is called "Table1". I assume this is anonymised
JOIN dbo.Table3 md ON md.Table3Id = mb.Id
ORDER BY md.LastUpdateDate DESC;
GO
推荐阅读
- python - 将类的实例存储在字符串中
- swift - CGDisplayCreateImage doesn't have cursor
- journal - Is "Results in engineering; ISSN : 2590-1230" a Scopus indexed journal?
- sql - Join two Json arrays to one with key and value postgresql
- linux - Why downloaded file docx is broken?
- java - 如何从日志文件中拆分消息？
- django - not able to post images through rest framework django but says status code 200
- javascript - Error: Process exited with code 16 google cloud functions
- http-status-code-302 - 是否可以在 angular (>=5) 拦截器上捕获状态代码 302 及其响应标头？
- python-3.x - 如何使用python找出齿轮噪声文件的幅度调制指数