MS SQL - Query takes too long to run

Question

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.

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:

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

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

3. Check all the record from tblsentdata that is not into tbltransactiondetail, parse the delimited data, and insert each as one row.

tblsentdata - data from app

tbltransactionheader - header table

tbltransactiondetail - detail table

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 */")

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

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.

Here are my questions:

1. 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.

2. 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.

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.

Please see the edited query above.

Thankyou guys.

Answer 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.

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.