Ungewöhnliches Timeout-Problem mit LinQ

verwendetes Datenbanksystem: MSSQL

Hallo allerseits. Vorab möchte ich schicken, dass ich schon ne ganze Weile mit LinQ to SQL arbeite, Fehler der Art "Du verbindest Dich falsch" etc. wohl eher nicht der Grund sind. Es gibt auch 1000 andere Procedures, die das Problem nicht haben.

Nun aber zum eigentlichen Problem:
Ich habe eine Stored Procedure, die relativ viele interne Selects, eine Schleife und Aggregatfunktionen hat etc. (Das sollte aber eigentlich unerheblich sein für mein Problem) :

ALTER PROCEDURE [dbo].[DeleteBankTransfersFromClearing]
(
	@clearingId int
)
AS BEGIN
	DECLARE @isTrans	bit;
		
	IF @@TRANCOUNT>0	
		SET @isTrans = 1;
	ELSE 
		SET @isTrans = 0;

	BEGIN TRY
		IF @isTrans=0 
		BEGIN TRANSACTION
			--] Ab hier individueller Code:	[--
			
			DECLARE
			@loopReturnCode		int,
			@loopNextRowId		int,
			@loopCurrentRowId	int,
			@loopControl		int,
			@banktransferId		int
			
		
			--init
			SELECT @loopControl = 1
			SELECT @loopNextRowId = MIN(cb.id_banktransfer) FROM clearing_banktransfer cb
					inner join clearing_debitor_invoice cdi on cdi.id = cb.id_clearing
					Inner join clearing_debitor cd on cd.id = cdi.id_clearing_debitor
					WHERE cd.id_clearing=@clearingId
			
			IF ISNULL(@loopNextRowId,0) = 0
			BEGIN
				SELECT 'No data!'
				RETURN ;
			END
			
			-- get first row
			SELECT @loopCurrentRowId = cb.id_banktransfer
					FROM clearing_banktransfer cb
					WHERE cb.id_bankTransfer = @loopNextRowId
			
			-- Löschen hier
			WHILE @loopControl = 1
			BEGIN
				-- hier banktransferobjekte löschen (vorerst auskommentiert)
				DELETE FROM clearing_banktransfer WHERE id_bankTransfer = @loopCurrentRowId
				DELETE FROM bankTransfer WHERE id = @loopCurrentRowId; 
				-- für test select statt löschen
				--SELECT t.id_bankTransfer AS BT_ID, t.id_clearing AS CL_ID
				-- FROM clearing_banktransfer AS t
				-- WHERE t.id_bankTransfer = @loopCurrentRowId
				
				-- reset loop vars
				SELECT @loopNextRowId = NULL
				-- get next row
				SELECT @loopNextRowId = MIN(cb.id_banktransfer)
					FROM clearing_banktransfer cb
					WHERE 
					cb.id_bankTransfer > @loopCurrentRowId
				IF ISNULL(@loopNextRowId,0) = 0
				BEGIN
					BREAK
				END
				-- get next row
				SELECT @loopCurrentRowId = cb.id_banktransfer
					FROM clearing_banktransfer cb
					WHERE cb.id_bankTransfer = @loopNextRowId
				
			END
			RETURN			

			--] Ende individueller Code [--
		IF @isTrans=0 		
		COMMIT TRANSACTION
		return 0

	END TRY
	BEGIN CATCH
		EXECUTE GetErrorInfo;
		IF @isTrans=0
		ROLLBACK TRANSACTION
		RETURN (-1)*ERROR_NUMBER()	-- negative Errorcodes
	END CATCH
END

Diese procedure wird dann über LinQ verknüpft:

 private int DeleteBanktransfersFromClearing(DL.LQImpersonalObjectsDataContext datacontext)
        {
            datacontext = datacontext ?? new DL.LQImpersonalObjectsDataContext();
            int errCode = (int)ErrorCodes.NO_ERROR;

            foreach (DL.DeleteBankTransfersFromClearingResult res in datacontext.DeleteBankTransfersFromClearing(_id))
            {
                if (res.ErrorLine.HasValue && res.ErrorLine.Value > 0)
                {
                    return (int)ErrorCodes.DATABASE_ERROR;
                }
            }

            if (errCode >= (int)ErrorCodes.NO_ERROR)
            {
                return this.CreateLog(null, ActionTypes.DELETE, "Löschen Banktransfers erfolgreich", string.Format("CLEARING {0} ", this._id));
            }

            return errCode;
        }

Führe ich die Procedure direkt auf der Datenbank aus, dauert es <1 sekunde, bis die Ausführung abgeschlossen ist. Rufe ich die gleiche procedure über die Methode auf, kommt nach 30 Sekunden ein Datenbank-Timeout. Natürlich mit den gleichen Parametern.

Dabei ist es auch unerheblich, ob ich im Code eine Transaktion erstelle oder die Prozedur ohne Transaktion aufrufe.

Um ehrlich zu sein: Sollte mir jemand dieses Verhalten beschreiben würde ich ihm kein Wort glauben. Aber meine Kollegen kämpfen da jetzt auch schon Monate mit und haben letztendlich ihre letzte Hoffnung in mich gesetzt, die ich nun an Euch weiterleite 😃

Ich habe die Erfahrung gemacht, dass das Plan-Caching manchmal Quatsch cached und man deshalb mit OPTION(RECOMPILE) viel erreichen kann (nicht immer und auch nur mit Bedacht einsetzen - zum Testen aber allemale geeignet)

Und dann möchte ich fragen, warum du hier Iterativ arbeitest, denn Datenbanken sind Relational (meistens) und da ist sowas mit durchloopen eher ... kontraproduktiv, was die Performance angeht.

Etwas besser wäre ein CURSOR (FORWARD ONLY) und sehr viel besser wäre ein DELETE Statement, das sich nur auf Mengen-Operationen bezieht.

Den SQL Server Profiler hast du auch schon mitlaufen lassen?
Wo disposed du den DataContext? Bzw. erstellt du für diese Abfrage einen neuen oder wird ein alter wiederverwendet?

Hi

Nachdem es hier erstmal nicht um SQL geht (sonst würde ich definitiv empfehlen die Schleife rauszuschmeißen 😉), hänge ich mich an dN!3L an, schau dir mal an was der Profiler anzeigt.

RECOMPILE ist zwar gefährlich, kann durch die IF-Statements wirklich helfen.

Grüße
Flo

Danke für Eure Anregungen, werde das mal durchtesten. CURSOR hatte ich sogar mal drin, ihn aber - in der Hoffnung das flotter zu kriegen - durch den Loop ersetzt.

Hat zwar nix gebracht, ist aber auch nicht langsamer geworden

CURSOR und LOOPs sind in SQL Server fast äquivalent langsam, wobei die Schleife meistens minimal schneller ist. Ich würde das Statement so umbauen, dass komplett sequenziell, ohne Schleifen und IF-Konstrukte gearbeitet wird. Dann gibt's auch keine Probleme mit Execution Plan Caching.

Zu deinem eigentlichem Problem (weil's ja wohl über's SSMS schnell ist):
Hast du mal im Profiler geschaut?

Grüße
Flo

Du meinst sowas wie:

DELETE FROM clearing_banktransfer WHERE id_bankTransfer IN 
  (SELECT cb.id_banktransfer FROM clearing_banktransfer cb
					inner join clearing_debitor_invoice cdi on cdi.id = cb.id_clearing
					Inner join clearing_debitor cd on cd.id = cdi.id_clearing_debitor
					WHERE cd.id_clearing=@clearingId
)

?

Profiler ging noch nicht. Der Fehler ist leider nur im Live-System und ich muss warten, bis der Kunde diese Funktion wieder benötigt.

Ich könnte natürlich das ganze mit einem anschließenden Rollback testen, aber im Backend passieren dabei noch viele andere Aktionen (z.B. im filesystem), so dass ich das nicht riskieren will

Hi

Gerade ist mir in deiner Prozedur noch was aufgefallen, du hast in Zeile 72 ein RETURN. Damit hebelst du dir ggf. dein Transaction-Handling aus. Das kann übrigens auch dein Problem sein, da .NET implizit eine Transaktion erzeugt, wenn keine existiert.

Zum SQL:
Ich meinte so was. Habe die DELETE-Zeilen auskommentiert und durch SELECT's drüber gepackt, da ich keine Daten habe kann ich natürlich nur vermuten dass das ich's richtig interpretiert habe.

ALTER PROCEDURE [dbo].[DeleteBankTransfersFromClearing]
(
   @clearingId int
)
AS BEGIN
   DECLARE @isTrans   bit;
      
   IF @@TRANCOUNT>0   
      SET @isTrans = 1;
   ELSE 
      SET @isTrans = 0;

   BEGIN TRY
      IF @isTrans=0 
      BEGIN TRANSACTION
         --] Ab hier individueller Code:   [--
         DECLARE @affected INT;
         SET @affected = 0;
         
         -- temp table to catch all bankTransfer ids to be delete
         DECLARE @transfers_to_delete TABLE (Id INT NOT NULL PRIMARY KEY CLUSTERED);
         
         -- get bankTransfer ids to be deleted
         -- (remove the DISTINCT if id_banktransfer is unique in clearing_banktransfer)
         INSERT INTO @transfers_to_delete
         SELECT DISTINCT cb.id_banktransfer
         FROM clearing_banktransfer cb
            INNER JOIN clearing_debitor_invoice cdi on cdi.id = cb.id_clearing
            INNER JOIN clearing_debitor cd on cd.id = cdi.id_clearing_debitor
         WHERE cd.id_clearing = @clearingId;
         
         -- count rows affected
         SET @affected = @@ROWCOUNT;
         
         
         --  delete clearing_bankTransfer rows
         SELECT cb.*
         --DELETE cb
         FROM clearing_banktransfer cb
         WHERE EXISTS (
            SELECT * 
            FROM @transfers_to_delete del 
            WHERE cb.id_bankTransfer = del.id);
         
         -- count rows affected
         SET @affected = @affected + @@ROWCOUNT;
         
         -- delete bankTransfer rows
         SELECT bt.*
         --DELETE bt
         FROM bankTransfer bt
         WHERE EXISTS (
            SELECT *
            FROM @transfers_to_delete del 
            WHERE cb.id_bankTransfer = del.id);
         
         -- count rows affected
         SET @affected = @affected + @@ROWCOUNT;

         IF (@affected = 0)
            SELECT 'No data!';

         --] Ende individueller Code [--
      IF @isTrans=0       
      COMMIT TRANSACTION
      return 0

   END TRY
   BEGIN CATCH
      EXECUTE GetErrorInfo;
      IF @isTrans=0
      ROLLBACK TRANSACTION
      RETURN (-1)*ERROR_NUMBER()   -- negative Errorcodes
   END CATCH
END

Grüße
Flo

Hey. Danke für die Arbeit die Du Dir gemacht hast.

Werde da mal ein bisschen rumdoktorn und austesten. Schonmal ein schönes Wochenende.