How to make this update faster

Transact-SQL
1

hello
i have the following code and it takes too longdo you have any ideas to make it faster?thank you

UPDATE AMPPU_Alle_Fehlteile
SET [BV-05] =
(SELECT [BV-05]
FROM AQIs.dbo.Alle_Fehlerdaten
WHERE Auftrag = AMPPU_Alle_Fehlteile.Auftrag
AND Fehlercode = AMPPU_Alle_Fehlteile.Fehlercode
AND Zeit_Fehler_EIN = AMPPU_Alle_Fehlteile.Zeit_Fehler_EIN)

2

Show us the execution plan and the STATISTICS IO output. What indexes do you have on the tables?

3

hello

i have just primary key on the tables
this is a view of five tables
AQIs.dbo.Alle_Fehlerdaten
what do you mean ith output
i want to update the value of [BV-05]

4

From your query, you are trying to update column [BV_05] for ALL the rows/records.
Is this your intention ?
How many records to you have ?
Also, do you have triggers ?

5

Is this any better:

UPDATE	U
SET	[BV-05] = AQI.[BV-05]
FROM	AMPPU_Alle_Fehlteile AS U
	LEFT OUTER JOIN AQIs.dbo.Alle_Fehlerdaten AS AQI
		 ON AQI.Auftrag = U.Auftrag
		AND AQI.Fehlercode = U.Fehlercode
		AND AQI.Zeit_Fehler_EIN = U.Zeit_Fehler_EIN

Personally I would always add a check so that rows are ONLY updated when they change - otherwise SQL spends a lot of time logging and updating rows necessarily (assuming you don't need a TRIGGER to fire on rows that have NOT changed :smile:)

So I would add this:

WHERE	   U.[BV-05] <> AQI.[BV-05]
	OR (U.[BV-05] IS NULL AND AQI.[BV-05] IS NOT NULL)
	OR (U.[BV-05] IS NOT NULL AND AQI.[BV-05] IS NULL)

Your existing code will update [BV-05] to NULL if the corresponding record does NOT exist in AQIs.dbo.Alle_Fehlerdaten. If you do not need that then change the OUTER JOIN to an INNER JOIN and if you don't need to worry about [BV-05] being NULL (in EITHER table) then remove the two OR statements - SQL is likely to make a much better query plan without the OR statements.

6

thank you very much for your help

7

hello
no i don't have triggers
and i have about 14000 rows

8

thank you very much kristen
it helped me a lot