SQLTeam.com | Weblogs | Forums

How to run these codes faster


#1

hello
i have these three updates
do you know how to do them faster?
thank you

</
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')
WHERE MONTH(Offline_date) = MONTH(GETDATE())
AND YEAR(Offline_date) = YEAR(GETDATE());

UPDATE AMPPU_Fehlerdaten_CalcVals
SET
	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()))
				WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());
	--
	UPDATE AMPPU_Fehlerdaten_CalcVals
    SET
	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()))
					WHERE MONTH(Offline_date) = MONTH(GETDATE())
	AND YEAR(Offline_date) = YEAR(GETDATE());

/>


#2

First, to the extent possible, avoid using functions on columns in WHERE clauses. For example, change your final WHERE clause from

WHERE   MONTH(Offline_date) = MONTH(GETDATE())
        AND YEAR(Offline_date) = YEAR(GETDATE());

to this:

WHERE
	Offline_date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)   

Second, see if there are appropriate indexes. For the final where clause, an index on the Offline_date column might help. However, before adding or changing any indexes, run the update statement while you have the query plan turned on (control-M or Query->Include Actual Execution Plan in SSMS to turn it on). Then look at the query plan that is generated. It can give you information on what the most expensive operations are, and if some indexes would help.