How to make faster this code

Transact-SQL
1

Hello

i have the following code in sql server but it takes time a lot, do you have any ideas how to rewrite it to be faster?
thank you

BEGIN
--
DECLARE cr_CalcValTable_EmptyFields CURSOR FOR
SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.[BV-05], a.Bereich, a.Teamleader, a.Disponent
FROM AMPPU_Alle_Fehlteile a
LEFT OUTER JOIN AMPPU_Fehlerdaten_CalcVals b
ON a.Auftrag = b.Auftrag
AND a.Fehlercode = b.Fehlercode
AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
OR (a.Bereich <> b.Bereich OR (b.Bereich IS NULL AND a.Bereich IS NOT NULL))
OR (a.Teamleader <> b.Teamleader OR (b.Teamleader IS NULL AND a.Teamleader IS NOT NULL))
OR (a.Disponent <> b.Disponent OR (b.Disponent IS NULL AND a.Disponent IS NOT NULL))
OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
--
OPEN cr_CalcValTable_EmptyFields
FETCH NEXT FROM cr_CalcValTable_EmptyFields
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @BV_05, @Bereich, @Teamleader, @Disponent
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
UPDATE AMPPU_Fehlerdaten_CalcVals
SET [BV-05] = @BV_05,
Bereich = @Bereich,
Teamleader = @Teamleader,
Disponent = @Disponent,
Offline_Date = LEFT(CONVERT(NVARCHAR, @BV_05, 120),10)
WHERE Auftrag = @Auftrag
AND Fehlercode = @fehlercode
AND Zeit_Fehler_EIN = @Zeit_Fehler_Ein;
--
FETCH NEXT FROM cr_CalcValTable_EmptyFields
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @BV_05, @Bereich, @Teamleader, @Disponent
--
END
--
CLOSE cr_CalcValTable_EmptyFields
DEALLOCATE cr_CalcValTable_EmptyFields
--
END

2

This is not tested but something like

WITH cte AS (
SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.[BV-05], a.Bereich, a.Teamleader, a.Disponent
FROM AMPPU_Alle_Fehlteile a
LEFT OUTER JOIN AMPPU_Fehlerdaten_CalcVals b ON a.Auftrag = b.Auftrag
                                                AND a.Fehlercode = b.Fehlercode
                                                AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
    OR (a.Bereich <> b.Bereich OR (b.Bereich IS NULL AND a.Bereich IS NOT NULL))
    OR (a.Teamleader <> b.Teamleader OR (b.Teamleader IS NULL AND a.Teamleader IS NOT NULL))
    OR (a.Disponent <> b.Disponent OR (b.Disponent IS NULL AND a.Disponent IS NOT NULL))
    OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
)
UPDATE A 
SET [BV-05] = cte.BV_05,
    Bereich = cte.Bereich,
    Teamleader = cte.Teamleader,
    Disponent = cte.Disponent,
    Offline_Date = LEFT(CONVERT(NVARCHAR, cte.BV_05, 120),10)
FROm AMPPU_Fehlerdaten_CalcVals A 
INNER JOIN cte ON A.Auftrag = cte.Auftrag
    AND A.Fehlercode = cte.fehlercode
    AND A.Zeit_Fehler_EIN = cte.Zeit_Fehler_Ein;
3

Hi
but with this code my job is not stopped at all
do you have any other ideas?

4

i mean with this code my job is not stopped at all!

5

i wrote the following
is it correct

WITH cte AS (
SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.[BV-05], a.Bereich, a.Teamleader, a.Disponent
FROM AMPPU_Alle_Fehlteile a
LEFT OUTER JOIN AMPPU_Fehlerdaten_CalcVals b ON a.Auftrag = b.Auftrag
AND a.Fehlercode = b.Fehlercode
AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
OR (a.Bereich <> b.Bereich OR (b.Bereich IS NULL AND a.Bereich IS NOT NULL))
OR (a.Teamleader <> b.Teamleader OR (b.Teamleader IS NULL AND a.Teamleader IS NOT NULL))
OR (a.Disponent <> b.Disponent OR (b.Disponent IS NULL AND a.Disponent IS NOT NULL))
OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL))
)
UPDATE A
SET [BV-05] = cte.[BV-05],
Bereich = cte.Bereich,
Teamleader = cte.Teamleader,
Disponent = cte.Disponent,
Offline_Date = LEFT(CONVERT(NVARCHAR, cte.[BV-05], 120),10)
FROm AMPPU_Fehlerdaten_CalcVals A
INNER JOIN cte ON A.Auftrag = cte.Auftrag
AND A.Fehlercode = cte.fehlercode
AND A.Zeit_Fehler_EIN = cte.Zeit_Fehler_Ein;

6

hello it takes still long do you have any other ideas?
thank you

7

here is also the rest of my code
do you have any oideas how to write these to run as soon as possible

8

UPDATE AMPPU_Fehlerdaten_CalcVals
SET
-- General
Failures_TOTAL_exklNA =
(SELECT COUNT()
FROM AQIs.dbo.Alle_Fehlerdaten
WHERE LEFT(CONVERT(NVARCHAR, [BV-05], 120), 10) = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
AND RIGHT(Fehlercode,1) IN ('4','5')
AND [BV-05_Monat] = MONTH(GETDATE())
AND [BV-05_Jahr] = YEAR(GETDATE())
AND CASE WHEN Zeit_Fehler_AUS IS NULL THEN 'No'
WHEN [BV-05] >= Zeit_Fehler_AUS THEN 'Yes'
ELSE 'No' END = 'NO'),
Failures_TOTAL_inklNA =
(SELECT COUNT()
FROM AQIs.dbo.Alle_Fehlerdaten
WHERE LEFT(CONVERT(NVARCHAR, [BV-05], 120), 10) = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
AND RIGHT(Fehlercode,1) IN ('4','5')
AND [BV-05_Monat] = MONTH(GETDATE())
AND [BV-05_Jahr] = YEAR(GETDATE())),
--
Orders_DailyVolume =
(SELECT COUNT(Auftrag_Nummer)
FROM Auftrags_Info
WHERE LEFT(CONVERT(NVARCHAR, Datum_Pfase_7, 120), 10) = AMPPU_Fehlerdaten_CalcVals.offline_Date
AND MONTH(Datum_Pfase_7) = MONTH(GETDATE())
AND YEAR(Datum_Pfase_7) = YEAR(GETDATE())),

		sum_devices_B_monthly =
	( select sum(SUpdateColSource1)
   FROM  (select  Offline_date ,Orders_DailyVolume as SUpdateColSource1 
   from AMPPU_Fehlerdaten_CalcVals B  
   where MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date) and
   YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date) 
   group by Offline_date,Orders_DailyVolume) a ),
   
   
	-- Teamleader
	Failures_Teamleader_exklNA = 
				(SELECT SUM(Count_All)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Teamleader,'Null')
					AND Bereich = 'Intern'
					AND On_Line_Repair = 'No'
				GROUP BY Teamleader),
	Failures_Teamleader_inklNA =
				(SELECT SUM(Count_ALL)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Teamleader,'Null')
					AND Bereich = 'Intern'
				GROUP BY Teamleader),
	-- Bereich
	Failures_Bereich_exklNA = 
				(SELECT SUM(Count_ALL)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')
					AND On_Line_Repair = 'NO'
				GROUP BY Bereich),
				
[sum_Failures_B_monthly(excl)] =
	( select sum(SUpdateColSource1)
   FROM  (select Offline_date,Failures_Bereich_exklNA
  as SUpdateColSource1 from AMPPU_Fehlerdaten_CalcVals B  
  where MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date) and
  YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date) and
  ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')  
  group by Offline_date,Failures_Bereich_exklNA ) a ),
  
  
	Failures_Bereich_inklNA =
				(SELECT SUM(Count_ALL)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')
				GROUP BY Bereich),
				
	sum_Failures_B_monthly =
	( select sum(SUpdateColSource1)
   FROM  (select Offline_date,Failures_Bereich_inklNA
   as SUpdateColSource1 from AMPPU_Fehlerdaten_CalcVals B  
   where MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date) and
   YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date) and
  ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')  
   group by Offline_date,Failures_Bereich_inklNA ) a ),
	-- Disponent
    
	-- Disponent
	Failures_Disponent_exklNA = 
				(SELECT SUM(Count_ALL)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.disponent,'Null')
					AND Bereich = 'Extern'
					AND On_Line_Repair = 'No'
				GROUP BY Disponent),
	Failures_Disponent_inklNA =
				(SELECT SUM(Count_ALL)
				FROM v_AMPPU_CalcValue_HOURLY
				WHERE Offline_date = AMPPU_Fehlerdaten_CalcVals.Offline_DATE
					AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.disponent,'Null')
					AND Bereich = 'Extern'
				GROUP BY Disponent)
WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());
--
--
-- Setting the excepted DailyVolume for the current date
--
UPDATE AMPPU_Fehlerdaten_CalcVals
SET Orders_DailyVolume =
				(SELECT COUNT(Auftrag_Nummer)
				FROM Auftrags_Info
				WHERE LEFT(CONVERT(NVARCHAR, Datum_Final_Schedule, 120), 10) =  AMPPU_Fehlerdaten_CalcVals.offline_Date
				group  by Datum_Final_Schedule)
WHERE Offline_date = LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10);
--
-- per Day:
--
UPDATE AMPPU_Fehlerdaten_CalcVals
SET
	-- General
	CalcVal_TOTAL_exklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_TOTAL_exklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	CalcVal_TOTAL_inklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_TOTAL_inklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	-- Teamleader
	CalcVal_Teamleader_exklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Teamleader_exklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	CalcVal_Teamleader_inklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Teamleader_inklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	-- Bereich
	CalcVal_Bereich_exklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Bereich_exklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	CalcVal_Bereich_inklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Bereich_inklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	-- Disponent
	CalcVal_Disponent_exklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Disponent_exklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END,
	CalcVal_Disponent_inklNA = CASE WHEN Orders_DailyVolume = 0 THEN 0 ELSE ISNULL(Failures_Disponent_inklNA/CONVERT(DECIMAL,Orders_DailyVolume),0) END
WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());
--
-- per Month:
--
UPDATE AMPPU_Fehlerdaten_CalcVals
SET
	-- Bereich
	-- CalcVal_B_MONAT_exklNA = 
 -- CASE WHEN sum_devices_B_monthly = 0 THEN 0 ELSE  (	[sum_Failures_B_monthly(excl)] /sum_devices_B_monthly) END,
				-- (SELECT AVG(avg_bereich)
				-- FROM (SELECT Offline_date, AVG(CalcVal_Bereich_exklNA) AS avg_bereich
				--	FROM AMPPU_Fehlerdaten_CalcVals b
				--	WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
				--	AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
				--	AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')
				--	GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a),
	--CalcVal_B_MONAT_inklNA =
	-- CASE WHEN sum_devices_B_monthly = 0 THEN 0 ELSE  (sum_Failures_B_monthly/sum_devices_B_monthly) END,
				--(SELECT AVG(avg_bereich)
				--FROM (SELECT Offline_date, AVG(CalcVal_Bereich_inklNA) AS avg_bereich
				--	FROM AMPPU_Fehlerdaten_CalcVals b
				--	WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
				--	AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
				--	AND ISNULL(Bereich,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.bereich,'Null')
				--	GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a),
	-- Teamleader
	CalcVal_T_MONAT_exklNA = 
				(SELECT AVG(avg_Teamleader)
				FROM (SELECT Offline_date, AVG(CalcVal_Teamleader_exklNA) AS avg_Teamleader
					FROM AMPPU_Fehlerdaten_CalcVals b
					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Teamleader,'Null')
					GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a),
	CalcVal_T_MONAT_inklNA =
				(SELECT AVG(avg_Teamleader)
				FROM (SELECT Offline_date, AVG(CalcVal_Teamleader_inklNA) AS avg_Teamleader
					FROM AMPPU_Fehlerdaten_CalcVals b
					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND ISNULL(Teamleader,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Teamleader,'Null')
					GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a),
	-- Disponent
	CalcVal_D_MONAT_exklNA = 
				(SELECT AVG(avg_Disponent)
				FROM (SELECT Offline_date, AVG(CalcVal_Disponent_exklNA) AS avg_Disponent
					FROM AMPPU_Fehlerdaten_CalcVals b
					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Disponent,'Null')
					GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a),
	CalcVal_D_MONAT_inklNA =
				(SELECT AVG(avg_Disponent)
				FROM (SELECT Offline_date, AVG(CalcVal_Disponent_inklNA) AS avg_Disponent
					FROM AMPPU_Fehlerdaten_CalcVals b
					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)
					AND ISNULL(Disponent,'Null') = ISNULL(AMPPU_Fehlerdaten_CalcVals.Disponent,'Null')
					GROUP BY Offline_date, YEAR(Offline_date), MONTH(Offline_date)) a)
WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());
--
-- Total, per Month:
--
UPDATE AMPPU_Fehlerdaten_CalcVals
SET
	--Bereich:
	--CalcVal_B_TOTAL_MONAT_exklNA = 
	--			(SELECT ISNULL(SUM(CalcVal_B_MONAT_exklNA),0)
	--			FROM (SELECT DISTINCT CalcVal_B_MONAT_exklNA
	--					FROM AMPPU_Fehlerdaten_CalcVals b
	--					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
	--					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a),
	--CalcVal_B_TOTAL_MONAT_inklNA = 
	--			(SELECT ISNULL(SUM(CalcVal_B_MONAT_inklNA),0)
	--			FROM (SELECT DISTINCT CalcVal_B_MONAT_inklNA
	--					FROM AMPPU_Fehlerdaten_CalcVals b
	--					WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
	--					AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a),
	--Teamleader:
	CalcVal_T_TOTAL_MONAT_exklNA = 
				(SELECT ISNULL(SUM(CalcVal_T_MONAT_exklNA),0)
				FROM (SELECT DISTINCT CalcVal_T_MONAT_exklNA
						FROM AMPPU_Fehlerdaten_CalcVals b
						WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
						AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a),
	CalcVal_T_TOTAL_MONAT_inklNA = 
				(SELECT ISNULL(SUM(CalcVal_T_MONAT_inklNA),0)
				FROM (SELECT DISTINCT CalcVal_T_MONAT_inklNA
						FROM AMPPU_Fehlerdaten_CalcVals b
						WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
						AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a),
	--Disponent:
	CalcVal_D_TOTAL_MONAT_exklNA = 
				(SELECT ISNULL(SUM(CalcVal_D_MONAT_exklNA),0)
				FROM (SELECT DISTINCT CalcVal_D_MONAT_exklNA
						FROM AMPPU_Fehlerdaten_CalcVals b
						WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
						AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a),
	CalcVal_D_TOTAL_MONAT_inklNA = 
				(SELECT ISNULL(SUM(CalcVal_D_MONAT_inklNA),0)
				FROM (SELECT DISTINCT CalcVal_D_MONAT_inklNA
						FROM AMPPU_Fehlerdaten_CalcVals b
						WHERE MONTH(Offline_date) = MONTH(AMPPU_Fehlerdaten_CalcVals.Offline_date)
						AND YEAR(Offline_date) = YEAR(AMPPU_Fehlerdaten_CalcVals.Offline_date)) a)
WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());