如何优化存储过程

Question

我想知道是否有方法可以优化并提高此存储过程的性能。我是新手，感谢您的帮助。

这个过程我从以前的程序员那里继承了下来。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDailyWorkConfB]    
@datew DATETIME
AS
BEGIN
SET NOCOUNT ON;
;WITH tmpTable AS 
(
SELECT IdRealizare,Adresa, CONVERT(char(2), Data, 108) as Ora
FROM Butoane
WHERE (IdRealizare <> 0) AND (DAY(butoane.Data) = DAY(@datew)) AND (MONTH(butoane.Data) = MONTH(@datew)) AND (YEAR(butoane.Data) = YEAR(@datew)) 
)
SELECT *, COUNT(*) AS Qty INTO #tblOne
FROM tmpTable
GROUP BY IdRealizare,Adresa,Ora
HAVING COUNT(*) >=1
ORDER BY Ora
SELECT Masini.Linie , Masini.CodMasina, Operatii.CodOperatie, Angajati.Angajat, Comenzi.NrComanda,Articole.Articol,
OperatiiArticol.BucatiButon,Operatii.Operatie,OperatiiArticol.BucatiOra, Masini.Grup, #tblone.Ora, #tblOne.Qty, Realizari.Id, Operatii.PozRaport, Angajati.IdSector, Realizari.LastWrite,
Articole.Stagione
INTO #tblFOR
FROM Realizari 
INNER JOIN Masini ON Realizari.IdMasina = Masini.Id 
INNER JOIN Angajati ON Realizari.IdAngajat = Angajati.Id
INNER JOIN Comenzi ON Realizari.IdComanda = Comenzi.Id 
INNER JOIN Operatii ON Realizari.IdOperatie = Operatii.Id
INNER JOIN Articole ON Comenzi.IdArticol = Articole.Id
INNER JOIN OperatiiArticol ON Comenzi.IdArticol = OperatiiArticol.IdArticol AND Operatii.id = OperatiiArticol.IdOperatie    
INNER JOIN #tblOne ON #tblOne.IdRealizare=Realizari.Id 
SELECT * FROM #tblFOR WHERE Qty>0 AND IdSector='1' AND Linie in ('LINEA1','LINEA2','LINEA3','LINEA4','LINEA5','LINEA6','LINEA7','LINEA8','LINEA9','LINEA10','LINEA11','LINEA12') ORDER BY Realizari.Id 
END

如果有人有什么想法，我想听听。 谢谢，最好的问候。

Answer 1

一个提示是您应该在连接查询中使用 WITH (NOLOCK(。由于此过程基本上是为了显示数据而构建的，因此最好在对这些表执行更新的其他查询时获取未提交的数据，而不是创建死锁。