sql-server - MS SQL - 查询运行时间太长

Question

我有一个数据库触发器，而不是运行一个存储过程。

因此，每次将新数据插入表中时，都会运行此触发器。

这是存储过程查询:

已编辑:新查询

declare @transaction_type int
set @transaction_type = (select  TransTyp from inserted)


declare @transaction_ctr bigint
set @transaction_ctr = (select TransCtr from inserted)

declare @transaction_no nvarchar(15)
set @transaction_no = (select TransNum from inserted)

declare @transaction_date datetime
set @transaction_date = (select TransDt from inserted)

declare @customer_code nvarchar(10)
set @customer_code = (select CustCode from inserted)

declare @contact nvarchar(15)
set @contact = (select CellNum  from inserted)


declare @transaction_info nvarchar(130)
set	@transaction_info = (select TransInfo from inserted)


declare @date_received datetime
set	@date_received = (select DtRcv from inserted)

declare @is_active int
set @is_active= (select	IsActive
				 from	BigESentData.dbo.tbl_sentRegistration
				 where CellNum = @contact)

declare @value nvarchar(130)
declare @pos int
declare @len int

if @is_active= 0 OR @is_active is null
begin
	delete from BigESentData.dbo.tbl_sentTransaction where TransCtr = @transaction_ctr
end

else
begin
	--exec parseSentData @transType

	/** Inventory */
	if @transaction_type = 3
	begin
		if not exists(select TransCtr from BigEMerchandiser.dbo.tbl_Inventory_H where TransCtr = @transaction_ctr)
		begin
			insert into BigEMerchandiser.dbo.tbl_Inventory_H
			Values(@transaction_ctr,@customer_code, @date_received, @contact)

			set @pos = 0
			set @len = 0
				while charindex('-', @transaction_info, @pos + 1) > 0
				begin
					set @len = charindex('|', @transaction_info, @pos + 1) - @pos
					set @value = substring(@transaction_info, @pos, @len)
						insert into BigEMerchandiser.dbo.tbl_Inventory_D(TransCtr, Material, Qty, ExpDt)
						select	@transaction_ctr,
								ltrim(rtrim(left(@value, charindex('-', @value) - 1))) as Material,
								replace(substring(@value,charindex('-',@value),len(@value) - charindex('-', reverse(@value)) - charindex('-', @value) + 1),'-', '') as Qty,
								reverse(left(reverse(@value),charindex('-', reverse(@value)) - 1))as ExpDt
					set @pos = charindex('|', @transaction_info, @pos + @len) + 1
				end
		end		
	end
	
	/** Delivery */
	if @transaction_type = 2
	begin
		if not exists(select TransCtr from BigEMerchandiser.dbo.tbl_Delivery_H where TransCtr = @transaction_ctr)
		begin
			insert into BigEMerchandiser.dbo.tbl_Delivery_H
			Values(@transaction_ctr, @transaction_no, @transaction_date, @customer_code, @date_received, @contact)

			set @pos = 0
			set @len = 0
				while charindex('-', @transaction_info, @pos + 1) > 0
				begin
					set @len = charindex('|', @transaction_info, @pos + 1) - @pos
					set @value = substring(@transaction_info, @pos, @len)
						insert into BigEMerchandiser.dbo.tbl_Delivery_D(TransCtr, Material, Qty, ExpDt)
						select	@transaction_ctr,
								ltrim(rtrim(left(@value, charindex('-', @value) - 1))) as Material,
								replace(substring(@value,charindex('-',@value),len(@value) - charindex('-', reverse(@value)) - charindex('-', @value) + 1),'-', '') as Qty,
								reverse(left(reverse(@value),charindex('-', reverse(@value)) - 1))as ExpDt
					set @pos = charindex('|', @transaction_info, @pos + @len) + 1
				end
		end		
	end

	/** ABIS */
	if @transaction_type = 1
	begin
		if not exists(select TransCtr from BigEMerchandiser.dbo.tbl_Abis_H where TransCtr = @transaction_ctr)
		begin
			insert into BigEMerchandiser.dbo.tbl_Abis_H
			Values(@transaction_ctr, @transaction_no, @customer_code, @date_received, @contact)

			set @pos = 0
			set @len = 0
				while charindex('-', @transaction_info, @pos + 1) > 0
				begin
					set @len = charindex('|', @transaction_info, @pos + 1) - @pos
					set @value = substring(@transaction_info, @pos, @len)
						insert into BigEMerchandiser.dbo.tbl_Abis_D(TransCtr, Material, Qty, ExpDt)
						select	@transaction_ctr,
								ltrim(rtrim(left(@value, charindex('-', @value) - 1))) as Material,
								replace(substring(@value,charindex('-',@value),len(@value) - charindex('-', reverse(@value)) - charindex('-', @value) + 1),'-', '') as Qty,
								reverse(left(reverse(@value),charindex('-', reverse(@value)) - 1))as ExpDt
					set @pos = charindex('|', @transaction_info, @pos + @len) + 1
				end
		end		
	end

	/** end of transaction */
end

让我告诉你一个它是如何运行的故事。数据来自 Android 应用程序。这是一款专为超市商家设计的应用程序。该应用程序将数据转换为 SMS 并将数据发送到 SQL 数据库。

现在，如果从应用程序发送的数据插入到数据库中，则会触发触发器，并运行上面的存储过程。

存储过程按以下顺序运行:

1. 检查交易类型(在上面的查询中，3 是库存)

2. 检查tbl_sentdata中所有不在tbltransactionheader中的记录，如果不在tbltransactionheader中，则将其插入那里。

3. 检查tblsentdata中未进入tbltransactiondetail的所有记录，解析分隔数据，并将每条记录插入一行。

   <

tblsentdata - 来自应用程序的数据

tbltransactionheader - header 表

tbltransactiondetail - 明细表

这是来自应用程序的原始数据:

现在，正如您在屏幕截图中看到的，来自应用程序的数据已被分隔。所以我需要一一解析。(在上面的查询中，解析是从注释“/* Loop delimited data */”开始的)

现在，如果我手动运行存储过程，对于 TransTyp 3(inventory) 来说，需要很长时间。

根据我的猜测，上面的查询会一一检查 tblsentdata 中的所有数据。因此，如果我有 100、000 条记录，即使数据已经在事务表中，它每次都会检查它。

这是我的问题:

1. 我的假设正确吗？如果触发器触发，则查询运行。然后发送另一条短信，触发器再次运行，即使第一个查询尚未完成。然后发送另一条短信，触发器再次运行。

2. 上面的查询是执行此操作的最佳方法吗？有什么方法可以更快吗？我无法承担运行此查询所需的大量时间。

希望你能启发我，我是 SQL 查询的新手。或者至少指导我去哪里寻找。

希望我能很好地解释自己。我将不胜感激任何帮助和建议。谢谢。

更新:2018 年 11 月 4 日

感谢PSK。在他的帮助下，我能够解决我的问题并最大限度地减少查询中的 while 循环。我刚刚删除了存储过程。我直接从触发器运行查询，并且只选择插入的数据。

请参阅上面编辑后的查询。

谢谢大家。

Answer 1

您应该避免在查询中使用WHILE，它会严重减慢您的查询速度。对于您的情况，您可以轻松避免 while 循环，这将提高查询的性能。

例如

现有代码

WHILE @headerCount > 0
BEGIN
    SET @transCtr = (SELECT TOP 1 TransCtr FROM BigESentData.dbo.tbl_sentTransaction WHERE TransCtr NOT IN (SELECT TransCtr FROM BigEMerchandiser.dbo.tbl_Inventory_H) and TransTyp = 3)

    IF NOT EXISTS(SELECT TransCtr FROM BigEMerchandiser.dbo.tbl_Inventory_H where TransCtr = @transCtr)
    BEGIN
        INSERT INTO BigEMerchandiser.dbo.tbl_Inventory_H
        SELECT TOP 1 TransCtr, CustCode, DtRcv, CellNum
        FROM BigESentData.dbo.tbl_sentTransaction where TransCtr = @transCtr
    END
    SET @headerCount = @headerCount - 1
END

修改(无 while)

 INSERT INTO BigEMerchandiser.dbo.tbl_Inventory_H
    SELECT TOP 1 TransCtr, CustCode, DtRcv, CellNum
    FROM BigESentData.dbo.tbl_sentTransaction A

    WHERE EXISTS
    (
        SELECT 1 FROM 
        BigESentData.dbo.tbl_sentTransaction T WHERE TransCtr NOT IN 
            (SELECT TransCtr FROM BigEMerchandiser.dbo.tbl_Inventory_H) and TransTyp = 3)
        AND T.TransCtr = A.TransCtr
    ) 

使用类似的方法，您可以实现第二个 while 循环。