Witam Forumowiczów.
Chciałbym zoptymalizować zapytanie SQL, które wyciąga z systemu ERP zlecenia produkcyjne, w których pozostało jeszcze coś do zrealizowania. Dodam, że pozakładałem już indeksy i zmieniłem podzapytania na JOINy z zapytaniami, co poprawiło już szybkość wykonywania zapytania, ale czas jego wykonywania wciąż pozostawia wiele do życzenia. Wszelka pomoc z Waszej strony mile widziana. Poniżej zapytanko:
CREATE PROCEDURE [dbo].[CSProdGetOrdersOfDirector](
@usr_id AS CHAR(20)
)
AS
BEGIN
DECLARE @freefield2 AS CHAR(50)
IF @usr_id IS NOT NULL AND LTRIM(RTRIM(@usr_id)) != ''
BEGIN
SELECT @freefield2 = freefield2 FROM humres with(nolock) where usr_id = @usr_id
IF @freefield2 IS NOT NULL AND LTRIM(RTRIM(@usr_id)) != ''
BEGIN
SELECT ItemCode, ItemDescription, Version, ProductionOrderNumber, PlannedStartDate, PlannedEndDate,
ProductionOrderDate, Planned,Realized,(Planned-Realized) as ToRealize, ItemClass, Class_09, UserField_09
FROM
(
SELECT ItemCode = g.artcode, ItemDescription = Items.Description, ItemClass = Items.Class_10,
MIN(g.starttime) AS PlannedStartDate, Max(g.endtime) AS PlannedEndDate,
Version = MAX(g.comp_code), ProductionOrderNumber = p.Projectnr,
ProductionOrderDate = p.syscreated,
Planned = MAX(ISNULL(ProjectsPlanned.planned, 0)),
Realized = MAX(ISNULL(ProjectsRealized.realized, 0)),
Class_09 = Items.Class_09 , UserField_09 = Items.UserField_09
FROM PrProject p
WITH(NOLOCK)
JOIN gbkmut g WITH(NOLOCK) ON p.ProjectNr=g.project AND g.bud_vers='MRP'
AND g.transtype='B' AND g.freefield1='P' AND g.transsubtype = 'A'
JOIN Items WITH(NOLOCK) ON Items.ItemCode=g.artcode
AND Items.GLAccountDistribution=g.reknr
LEFT JOIN
(
SELECT project = g2.project, planned = ISNULL(SUM(g2.aantal), 0)
FROM gbkmut g2 WITH(NOLOCK)
JOIN Items i2 WITH(NOLOCK) ON i2.ItemCode=g2.artcode
AND i2.GLAccountDistribution=g2.reknr
WHERE g2.bud_vers='MRP'
AND g2.transtype='B' AND g2.freefield1='P' AND g2.transsubtype = 'A' AND g2.checked = 1
GROUP BY g2.project
) ProjectsPlanned ON g.project = ProjectsPlanned.project
LEFT JOIN
(
SELECT project = ch.ProjectNr, realized = COUNT(ch.ID)
FROM CSProdMNIHistory ch WITH(NOLOCK)
LEFT JOIN CSProdData cda WITH(NOLOCK) ON cda.MNI = ch.MNI
WHERE --ch.ProjectNr = p.ProjectNr
cda.IsMultiblock = 0 AND cda.IsMultibox = 0 AND ch.OperationCode =
(
SELECT TOP 1 sub_co.OperationCode
FROM CSProdOperations sub_co WITH(NOLOCK)
LEFT JOIN recipe sub_r WITH(NOLOCK) ON sub_r.itemreq = sub_co.ItemCode
LEFT JOIN CSProdMNIHistory sub_ch WITH(NOLOCK) ON sub_r.itemprod = sub_ch.itemcode
WHERE sub_ch.ProjectNr = ch.ProjectNr ORDER BY sub_r.sequenceno DESC
)
GROUP BY ch.ProjectNr
) ProjectsRealized ON g.project = ProjectsRealized.project
WHERE p.Type='P'
AND p.status <> 'C'
AND NOT (g.Checked=0 AND g.BlockItem=1 AND g.Reviewed=1)
AND ((g.checked=1 and g.blockitem=0 and g.reviewed=0) OR (g.checked=1 and g.blockitem=0 and g.reviewed=1))
AND p.Status='A' And Class_09= @freefield2
AND ((g.warehouse = 'WG01' AND Items.Class_10 = 'WRG') OR (g.warehouse = 'PW01' AND Items.Class_10 = 'PLP'))
AND Items.Assortment NOT IN ('206', '301', '302')
GROUP BY g.artcode, Items.Description, Items.Class_10, p.syscreated,
p.ProjectNr, Items.Class_09,
Items.UserField_09
) T
Where Planned - Realized > 0
ORDER BY ProductionOrderNumber
END
END
END
GO