Gespeicherte MSSQL-Prozedur - Timeout abgelaufen
asp.net-web-api dapper mirroring sql-server-2008 stored-procedures
Frage
Wir haben einen Web-API-Service für das Erstellen von Berichten. Dieser Dienst verwendet die MS SQL 2008-Datenbank als Datenquelle. Die Datenbank arbeitet als Spiegel im schreibgeschützten Modus. Der Dienst und die Datenbank werden auf verschiedenen Computern eines einzelnen lokalen Netzes gehostet.
In der Datenbank TicketSaleByAggregator befindet sich eine gespeicherte Prozedur, die Berichtsdaten auswählt. Manchmal löst die Prozedur die folgende Ausnahme aus:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
Dann wirft die Prozedur diese Ausnahme immer wieder auf, bis wir sie neu kompilieren :: ALTER PROCEDURE [dbo] [TicketSaleByAggregator] . Nach dem Befehl ALTER PROCEDURE läuft die Prozedur auch bei großen Datenmengen einwandfrei, aber nach ca. 10-15 Stunden löst der Fehler erneut aus: Timeout abgelaufen
Die Prozessparameter:
DECLARE @PeriodFrom DATETIME = '2016-12-30 00:00:00'
DECLARE @PeriodTo DATETIME = '2016-12-30 23:59:59'
DECLARE @Dealers Identities
DECLARE @Branches Identities
DECLARE @SaleChannels Identities
DECLARE @Carriers Identities
INSERT INTO @Dealers (Id) VALUES(10068)
INSERT INTO @Branches(Id) VALUES(1),(2),(3)
INSERT INTO @SaleChannels(Id)VALUES (7)
exec TicketSaleByAggregator @PeriodFrom, @PeriodTo, @Dealers, @Branches, @SaleChannels, @Carriers
Wenn wir die Prozedur in SQL Management Studio lokal (auf der Maschine, auf der sie gehostet wird) aufrufen, wird sie 15 Sekunden lang ausgeführt. Wenn wir die Prozedur auf dem Computer aufrufen, auf dem der Web-API-Dienst gehostet wird, wird SQL Management Studio ausgeführt für 15 bis 16 Sekunden. Die Ergebnisdaten enthält 82540 Datensätze
Code der gespeicherten Prozedur:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TicketSaleByAggregator]
(
@PeriodFrom DATETIME,
@PeriodTo DATETIME,
@Dealers Identities READONLY,
@Branches Identities READONLY,
@SaleChannels Identities READONLY,
@Carriers Identities READONLY
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON
;WITH ParentDealer(childId, parentId, BIN)
AS(
SELECT d.Id,
parentDealer.pId,
parentDealer.BIN
FROM Dealer AS d
JOIN (
SELECT d.Id,
parentDealer.Id pId,
parentDealer.BIN
FROM Dealer d
JOIN Dealer parentDealer
ON parentDealer.Hid = d.Hid.GetAncestor(d.[Hid].[GetLevel]() -1)
) parentDealer
ON d.Id = parentDealer.Id
),
CarrierNames
AS(
SELECT c.Name AS CarrierName
FROM Carrier AS c
INNER JOIN @Carriers crs ON c.Id = crs.Id
),
Result
AS(
SELECT *
FROM
(
SELECT t.[ExpressId] AS TicketExpressId,
o.[OrderCreateDate] AS OperationDate,
1 AS TicketCount,
t.[Tariff] AS Tariff,
6 AS OperationTypeId,
sc.Name AS SalesChannelName,
b.ShortName AS BranchName,
o.CarrierName,
o.PaymentType AS PaymentType,
--financial dealers
uDealer.Surname AS FinancialDealerSurname,
uDealer.Name AS FinancialDealerName,
uDealer.Middlename AS FinancialDealerPatronymic,
d.BIN AS FinancialDealerBin,
uparentDealer.Surname AS FinancialParentDealerSurname,
uparentDealer.Name AS FinancialParentDealerName,
uparentDealer.Middlename AS FinancialParentDealerPatronymic,
parentDealer.BIN AS FinancialParentDealerBin,
--place dealers
uDealer.Surname AS PlaceDealerSurname,
uDealer.Name AS PlaceDealerName,
uDealer.Middlename AS PlaceDealerPatronymic,
d.BIN AS PlaceDealerBin,
uparentDealer.Surname AS PlaceParentDealerSurname,
uparentDealer.Name AS PlaceParentDealerName,
uparentDealer.Middlename AS PlaceParentDealerPatronymic,
parentDealer.BIN AS PlaceParentDealerBin
FROM Ticket AS t
INNER JOIN [Order] AS o ON o.Id = t.OrderId AND o.OrderCreateDate BETWEEN @PeriodFrom AND @PeriodTo
INNER JOIN [Terminal] AS ter ON t.[TerminalId] = ter.[Id]
LEFT JOIN [TerminalData] AS td ON ter.[Id] = td.[Id]
INNER JOIN [SalePoint] AS sp ON td.[SalePointId] = sp.[Id]
INNER JOIN FinAccStation AS fas ON fas.Id = sp.FinAccStationId
INNER JOIN Guo AS g ON g.Id = fas.GuoId
INNER JOIN Department AS dep ON dep.Id = g.DepartmentId
INNER JOIN Dealer AS d ON d.Id = ter.DealerId
INNER JOIN [User] AS uDealer ON uDealer.Id = d.Id
INNER JOIN SalesChannel AS sc ON sc.Id = d.SalesChannelId AND (EXISTS(SELECT * FROM @SaleChannels) AND (d.SalesChannelId IN (SELECT * FROM @saleChannels)) OR NOT EXISTS(SELECT * FROM @saleChannels))
INNER JOIN Branch AS b ON b.Id = dep.BranchId AND (EXISTS(SELECT * FROM @Branches) AND (b.Id IN (SELECT * FROM @Branches)))
INNER JOIN ParentDealer AS parentDealer ON parentDealer.childId = d.Id AND (parentDealer.parentId IN (SELECT Id FROM @Dealers) OR NOT EXISTS(SELECT * FROM @Dealers))
INNER JOIN [User] AS uParentDealer ON uParentDealer.Id = parentDealer.parentId
WHERE t.TicketStatusId IN (3, 6) AND
((EXISTS(SELECT TOP 1 * FROM @Carriers) AND (o.CarrierName IN (SELECT CarrierName FROM CarrierNames))) OR NOT EXISTS(SELECT TOP 1 * FROM @Carriers))
UNION ALL
SELECT t.[ExpressId] AS TicketExpressId,
c.OperationDate AS OperationDate,
-1 AS TicketCount,
(-1)*ct.RetTariff AS Tariff,
3 AS OperationTypeId,
sc.Name AS SalesChannelName,
b.ShortName AS BranchName,
o.CarrierName,
c.PaymentType AS PaymentType,
--financial dealers
uDealer.Surname AS DealerSurname,
uDealer.Name AS DealerName,
uDealer.Middlename AS DealerPatronymic,
d.BIN AS DealerBin,
uparentDealer.Surname AS ParentDealerSurname,
uparentDealer.Name AS ParentDealerName,
uparentDealer.Middlename AS ParentDealerPatronymic,
parentDealer.BIN AS ParentDealerBin,
--place dealers
uDealer1.Surname AS PlaceDealerSurname,
uDealer1.Name AS PlaceDealerName,
uDealer1.Middlename AS PlaceDealerPatronymic,
d1.BIN AS PlaceDealerBin,
uparentDealer1.Surname AS PlaceParentDealerSurname,
uparentDealer1.Name AS PlaceParentDealerName,
uparentDealer1.Middlename AS PlaceParentDealerPatronymic,
parentDealer1.BIN AS PlaceParentDealerBin
FROM Cancelation AS c
INNER JOIN CancelationTicket AS ct ON ct.CancelationId = c.Id
INNER JOIN Ticket AS t ON t.Id = ct.TicketId
INNER JOIN [Order] AS o ON o.Id = c.OrderId
INNER JOIN Terminal AS ter ON ter.Id = IIF(o.PaymentType = 1, c.TerminalId, t.TerminalId)
INNER JOIN Terminal AS ter1 ON ter1.Id = c.TerminalId
LEFT JOIN [TerminalData] AS td ON td.[Id] = ter.Id
INNER JOIN [SalePoint] AS sp ON td.[SalePointId] = sp.[Id]
INNER JOIN FinAccStation AS fas ON fas.Id = sp.FinAccStationId
INNER JOIN Guo AS g ON g.Id = fas.GuoId
INNER JOIN Department AS dep ON dep.Id = g.DepartmentId
INNER JOIN Dealer AS d ON d.Id = ter.DealerId
INNER JOIN Dealer AS d1 ON d1.Id = ter1.DealerId
INNER JOIN [User] AS uDealer ON uDealer.Id = d.Id
INNER JOIN [User] AS uDealer1 ON uDealer1.Id = d1.Id
INNER JOIN SalesChannel AS sc ON sc.Id = d.SalesChannelId AND (EXISTS(SELECT * FROM @SaleChannels) AND (d.SalesChannelId IN (SELECT * FROM @saleChannels)) OR NOT EXISTS(SELECT * FROM @saleChannels))
INNER JOIN Branch AS b ON b.Id = dep.BranchId AND (EXISTS(SELECT * FROM @Branches) AND (b.Id IN (SELECT * FROM @Branches)))
INNER JOIN ParentDealer AS parentDealer ON parentDealer.childId = d.Id AND (parentDealer.parentId IN (SELECT Id FROM @Dealers) OR NOT EXISTS(SELECT * FROM @Dealers))
INNER JOIN [User] AS uParentDealer ON uParentDealer.Id = parentDealer.parentId
INNER JOIN ParentDealer AS parentDealer1 ON parentDealer1.childId = d1.Id
INNER JOIN [User] AS uParentDealer1 ON uParentDealer1.Id = parentDealer1.parentId
WHERE c.OperationDate BETWEEN @PeriodFrom AND @PeriodTo AND
((EXISTS(SELECT TOP 1 * FROM @Carriers) AND (o.CarrierName IN (SELECT CarrierName FROM CarrierNames))) OR NOT EXISTS(SELECT TOP 1 * FROM @Carriers)) AND
c.ResponseXml.value('(/GtETicket_Response/@Type)[1]','nvarchar(max)') != 'ExpressStatus'
)T
)
SELECT *
from Result
END
AKTUALISIERT: Code auf C # -Seite:
public IEnumerable<RegisterTicketsByDealerReportItem> GetRegisterTicketsByDealerReport(DateTime periodFrom, DateTime periodTo, int[] dealerIds, int[] salesChannelIds, int[] branchIds, int[] carrierIds)
{
var pars = new DynamicParameters();
var identityFailureValue = new { Id = 0 }.ToEmptyTable().AsTableValuedParameter("Identities");
pars.AddDynamicParams(
new
{
PeriodFrom = periodFrom,
PeriodTo = periodTo,
Dealers = dealerIds.Return(ds => ds.Select(d => new { Id = d }).ToDataTable().AsTableValuedParameter("Identities"), identityFailureValue),
Branches = branchIds.Return(br => br.Select(b => new { Id = b }).ToDataTable().AsTableValuedParameter("Identities"), identityFailureValue),
SaleChannels = salesChannelIds.Return(scs => scs.Select(sc => new { Id = sc }).ToDataTable().AsTableValuedParameter("Identities"), identityFailureValue),
Carriers = carrierIds.Return(scs => scs.Select(sc => new { Id = sc }).ToDataTable().AsTableValuedParameter("Identities"), identityFailureValue)
});
var result = Connection.Query<RegisterTicketsByDealerReportItem>("TicketSaleByAggregator", pars,
commandType: CommandType.StoredProcedure,
commandTimeout: dbConfiguration.SqlLargeTimeoutSeconds);
Connection.CloseIfNoTransaction();
return result;
}
SQL Verbindungsparameter:
<add key="SqlLargeTimeoutSeconds" value="00:02:00" />
<add name="Readonly" providerName="System.Data.SqlClient" connectionString="Data Source=.;Initial Catalog=db_Readonly;Integrated Security=True;" />
Beliebte Antwort
1- Versuchen Sie mit (nolock) zu verwenden, wenn es nicht sensible transnationale Daten sind. 2. Stoppen Sie die Spiegelung und überprüfen Sie die Geschwindigkeit. Spiegelung kann dieses Problem verursachen
Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow