SQL Server Performance Forum – Threads Archive

Stored procedure big performence problems

Hello,
i have a big big problem with a stored procedure. The stored goes often in timeout and then the completly program crash. Can somebody maybe help me? Here are the storeds: Stored procedure 1: CREATE PROCEDURE SyncroActiveAtp AS
UPDATE Syncro2
SET SyncroActive = 0
FROM Syncro1 INNER JOIN Syncro2
ON Syncro2.SyncroID1 = Syncro1.SyncroID1
where ( Syncro1.SyncroType = ‘ATP’) AND SYNCRO2.SYNCROACTIVE=1
SELECT MAX(Syncro1.SyncroNr) AS SyncroNr, Max(Syncro1.SyncroID1) as SyncroID1 , Syncro2.SyncroTF, Syncro2.SyncroOrderNR
INTO #TmpSyncro1
FROM Syncro1 INNER JOIN
Syncro2 ON Syncro1.SyncroID1 = Syncro2.SyncroID1
WHERE (Syncro1.SyncroType = ‘ATP’)
GROUP BY Syncro2.SyncroTF, Syncro2.SyncroOrderNR
UPDATE Syncro2
SET SyncroActive = 1
FROM #TmpSyncro1 a INNER JOIN
Syncro2 ON Syncro2.SyncroTF = a.SyncroTF AND Syncro2.SyncroOrderNR = a.SyncroOrderNR AND a.SyncroID1 = Syncro2.SyncroID1 INNER JOIN
Syncro1 ON Syncro2.SyncroID1 = Syncro1.SyncroID1 AND Syncro1.SyncroNr = a.SyncroNr
GO
Stored procedure 2: CREATE PROCEDURE SyncroActiveAsk AS
UPDATE Syncro2
SET SyncroActive = 0
FROM Syncro1 INNER JOIN Syncro2
ON Syncro2.SyncroID1 = Syncro1.SyncroID1
where ( Syncro1.SyncroType = ‘ASK’) AND SYNCRO2.SYNCROACTIVE=1
SELECT MAX(Syncro1.SyncroNr) AS SyncroNr, Max(Syncro1.SyncroID1) as SyncroID1 , Syncro2.SyncroTF, Syncro2.SyncroOrderNR
INTO #TmpSyncro1
FROM Syncro1 INNER JOIN
Syncro2 ON Syncro1.SyncroID1 = Syncro2.SyncroID1
WHERE (Syncro1.SyncroType = ‘ASK’)
GROUP BY Syncro2.SyncroTF, Syncro2.SyncroOrderNR
UPDATE Syncro2
SET SyncroActive = 1
FROM #TmpSyncro1 a INNER JOIN
Syncro2 ON Syncro2.SyncroTF = a.SyncroTF AND Syncro2.SyncroOrderNR = a.SyncroOrderNR AND a.SyncroID1 = Syncro2.SyncroID1 INNER JOIN
Syncro1 ON Syncro2.SyncroID1 = Syncro1.SyncroID1 AND Syncro1.SyncroNr = a.SyncroNr
GO Thank you in advance. Greats

Did you try to see execution plan to find out if any indexes are neccesary?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

How many rows gets affected while selecting and inserting it into temp table(i.e.#).
You can also use instead of select * into #tmp to
create table #tmp and then use insert into #tmp select columns. This might boost a bit.
Also check if insertion f rows are more make index on temp table with column used in join condition.

Also read articles in this site on Performance tuning Madhivanan Failing to plan is Planning to fail

Find out whether your all quries are using proper indexs.
To avoid the recomilation of your sp use the stapes suggested by ranjit.
Surendra Kalekar

]]>