stored procedure taking too long to execute.

[smuthcrmnl777]

I need to create a trigger when data is present in a particular table. There is only one record in this table. I know how to set up the trigger and have it run some code, but what I need is someone to tell me how I can make my stored procedure (code below) execute faster. For every elemnet, it takes about 5 seconds to execute. If there is a better way to write this code please let me know.

-- 1 -- Carbon
BEGIN

	INSERT INTO MES.tM_RD_Certs (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, C2, CLS, CUS, '% Carbon' AS WC, 1 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
END

BEGIN

	INSERT INTO MES.tM_RD_Certs2 (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.C2, 
                      dbo.vM_RD_Merge_RDSpecs.CLS, dbo.vM_RD_Merge_RDSpecs.CUS, '% Carbon' AS WC, 1 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
END
-- 5 -- Copper
BEGIN

	INSERT INTO MES.tM_RD_Certs (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, Cu_Total, CuTotalLS, CuTotalUS, '% Copper' AS WC, 5 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
END

BEGIN

	INSERT INTO MES.tM_RD_Certs2 (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.Cu_Total, 
                      dbo.vM_RD_Merge_RDSpecs.CuTotalLS, dbo.vM_RD_Merge_RDSpecs.CuTotalUS, '% Copper' AS WC, 5 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
END
-- 6 -- Manganese
BEGIN

	INSERT INTO MES.tM_RD_Certs (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, MN3, MNLS, MNUS, '% Manganese' AS WC, 6 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
END

BEGIN

	INSERT INTO MES.tM_RD_Certs2 (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.MN3, 
                      dbo.vM_RD_Merge_RDSpecs.MNLS, dbo.vM_RD_Merge_RDSpecs.MNUS, '% Manganese' AS WC, 6 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
END

 

[vongrunt]

Run all five SELECTs individually (without INSERTs). How long they take to finish and return how many rows?

Btw. dbo.vM_* = views?



------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[smuthcrmnl777]

I REMOVED THE INSERTS AND IT TOOK ABOUT 10 SECONDS

 

[vongrunt]

10 all together... compared to 5 seconds * 6 queries = 30 seconds, right?

Both SELECT and INSERT times are high... how many rows returns each SELECT?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[smuthcrmnl777]

YOU ARE EXACTLY RIGHT!!


Two of the selects returned no data as it was supposed to and the other two returned 1 record each.

I returned only 2 records.

I only expect 1-2 records per SELECT/INSERT


Now the other piece of information that you should know is that this data comes from our sister plant 15 mins away and travels by a sluggish T1 line. I always expect any SELECT to be a little slower than normal. I just didn't expect it to be 30 secs for 5 INSERTS (10 records maximum)

 

[smuthcrmnl777]

Can anyone help me, please?

 

[vongrunt]

Let's try to reduce roundtrips over slow connection. What happens if you write all INSERTs as one, with UNION ALL:

INSERT INTO MES.tM_RD_Certs (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	<first SELECT query>
UNION ALL
	<second SELECT query>
UNION ALL
	...
UNION ALL
	<last SELECT query>

?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[smuthcrmnl777]

I went ahead with your suggestion, but it does not become one INSERT. I have two separate tables I am trying to insert. So I have two INSERTS to deal with. Below is the INSERT code according to your suggestion. The INSERT w/o the JOIN takes about 6-9 seconds to complete, but the INSERT w/ the JOIN takes about 23 seconds. Both together took about 30-32 seconds.

-- No JOIN into CERTS
BEGIN

	INSERT INTO MES.tM_RD_Certs (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	
	-- 1 -- Carbon
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, C2, CLS, CUS, '% Carbon' AS WC, 1 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
UNION ALL
	-- 2 -- Sulfur
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, S, SLS, SUS, '% Sulfur' AS WC, 2 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
UNION ALL
	-- 3 -- Phosphorous
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, P1, PLS, PUS, '% Phosphorous' AS WC, 3 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs
UNION ALL
	-- 4 -- Silicon
	SELECT     Edono, WONO, [Name-Chem], [Nominal Diameter], ProdName, SI1, SILS, SIUS, '% Silicon' AS WC, 4 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs

--JOIN INSERT into CERTS2

	INSERT INTO MES.tM_RD_Certs2 (Edono,WONO,[Name-Chem],[Nominal Diameter], ProdName, Results, SpecLS1, SpecUS1, WireChem, [Order])
	-- 1 -- Carbon
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.C2, 
                      dbo.vM_RD_Merge_RDSpecs.CLS, dbo.vM_RD_Merge_RDSpecs.CUS, '% Carbon' AS WC, 1 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
UNION ALL
	-- 2 -- Sulfur
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.S, 
                      dbo.vM_RD_Merge_RDSpecs.SLS, dbo.vM_RD_Merge_RDSpecs.SUS, '% Sulfur' AS WC, 2 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
UNION ALL
	-- 3 -- Phosphorous
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.P1, 
                      dbo.vM_RD_Merge_RDSpecs.PLS, dbo.vM_RD_Merge_RDSpecs.PUS, '% Phosphorous' AS WC, 3 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
UNION ALL
	-- 4 -- Silicon
	SELECT     dbo.vM_RD_Merge_RDSpecs.Edono, dbo.vM_RD_Merge_RDSpecs.WONO, dbo.vM_RD_Merge_RDSpecs.[Name-Chem], 
                      dbo.vM_RD_Merge_RDSpecs.[Nominal Diameter], dbo.vM_RD_Merge_RDSpecs.ProdName, dbo.vM_RD_Merge_RDSpecs.SI1, 
                      dbo.vM_RD_Merge_RDSpecs.SILS, dbo.vM_RD_Merge_RDSpecs.SIUS, '% Silicon' AS WC, 4 AS Expr1
	FROM         dbo.vM_RD_Merge_RDSpecs INNER JOIN
                      dbo.vM_RD_SpecsCount2 ON dbo.vM_RD_Merge_RDSpecs.ProdName = dbo.vM_RD_SpecsCount2.[Lincoln Name]
END

 

[smuthcrmnl777]

Can anyone help me, please?

 

[vongrunt]

Lemme clarify: all these queries are actually executed from local Query Analyzer, while database is on a remote server - all over crammed T1 line?

And... are vM_RD_* object actually views?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]