MS SQL - 查询运行时间太长

I have a database trigger, than runs a stored procedure.

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

So this trigger runs, every time a new data is inserted to the table.

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

here is the stored procedure query:

这是存储过程查询:

EDITED: NEW QUERY

编辑:新查询

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

Let me tell you a story how it runs. The data is coming from an android app. It is an app for merchandisers in supermarkets. The app converts the data to SMS and sends the data to the SQL database.

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

Now, if the sent data from app is inserted to the database, the trigger fires, and the stored procedure above runs.

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

The stored procedure runs in this order:

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

Check the transaction type (in the above query it is 3 which is inventory)

检查交易类型(在上面的查询中它是3,这是库存)

Check all the record from tbl_sentdata that is not into tbltransactionheader, if not on tbltransactionheader, insert it there.

检查tbl_sentdata中没有进入tbltransactionheader的所有记录,如果不是tbltransactionheader,则将其插入那里。
Check all the record from tblsentdata that is not into tbltransactiondetail, parse the delimited data, and insert each as one row.

检查tblsentdata中没有进入tbltransactiondetail的所有记录,解析分隔数据,并将每个记录插入一行。

tblsentdata - data from app

tblsentdata - 来自应用程序的数据

tbltransactionheader - header table

tbltransactionheader - 标头表

tbltransactiondetail - detail table

tbltransactiondetail - 详细信息表

Here is the raw data from the app:

以下是该应用的原始数据:

Now, as you can see in the screenshot, the data from app is delimited. So I need to parse it one by one.(in the query above, the parsing starts from the comment "/* Loop delimited data */")

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

Now, if I run the stored procedure manually, for TransTyp 3(inventory), it takes too long.

现在,如果我手动运行存储过程,对于TransTyp 3(库存),则需要很长时间。

In my guess, my query above, checks all the data from tblsentdata one by one. so if I have 100, 000 records, it checks it every time even if the data is already on the transaction table.

在我的猜测中,我上面的查询逐个检查来自tblsentdata的所有数据。所以如果我有100,000条记录,它每次都会检查它,即使数据已经在事务表上。

Here are my questions:

这是我的问题:

Am I correct to assume this? If the trigger fires, the query runs. Then another text message is sent, the trigger runs again, even if the first query is not finished. Then another text message is sent, the trigger runs again.

我认为这是正确的吗?如果触发器触发,则运行查询。然后发送另一条文本消息,即使第一个查询未完成,触发器也会再次运行。然后发送另一条文本消息,触发器再次运行。
Is my query above the best way to do this? Is there any way to this faster? I cannot afford to run this query it takes a lot of time.

我的查询是否是最佳方法?有没有办法更快?我负担不起运行这个查询需要花费很多时间。

Hope you can enlighten me I am new to SQL query. Or at least guide me where to look.

希望你能赐教我,我是SQL查询的新手。或者至少引导我去哪里看。

Hope I explained myself well. I would appreciate any help and suggestion. Thank You.

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

UPDATE: 11/04/2018

Thanks to PSK. With his help, I am able to resolve my problem and minimize while loops in my query. I just removed the stored procedure. I run the query directly from the trigger and, just selected data from inserted.

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

Please see the edited query above.

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

Thankyou guys.

1 个解决方案

#1

You should avoid using WHILE in your queries, it slows down you query badly. For your case, you can easily avoid the while loop, this will improve the performance of the query.

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

For example

Existing code

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

Modified (without 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
    )

Using similar approach you can implement for the second while loop.

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

#1