有时仅从一个 SP 获取请求超时? [英] Getting Request Timeout sometimes from only one SP?
本文介绍了有时仅从一个 SP 获取请求超时?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有很多存储过程.但我有时只为这个 SP 收到请求超时?
I have a lot of stored procedures. But I am only getting Request Timeout sometimes only for this SP ?
ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog]
(@OS NVARCHAR(50)
,@UniqueID VARCHAR(500)
,@Longitude FLOAT
,@Latitude FLOAT
,@Culture VARCHAR(10)
,@Other NVARCHAR(200)
,@IPAddress VARCHAR(50)
,@NativeDeviceID VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TranCount INT;
SET @TranCount = @@TRANCOUNT;
DECLARE @OldUniqueID VARCHAR(500) = ''-1'';
SELECT @OldUniqueID = [UniqueID] FROM DeviceCatalog WHERE (@NativeDeviceID != '''' AND [NativeDeviceID] = @NativeDeviceID);
BEGIN TRY
IF @TranCount = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION Insertorupdatedevicecatalog;
DECLARE @Geo GEOGRAPHY = geography::STGeomFromText(''POINT('' + CONVERT(VARCHAR(100), @Longitude) + '' '' + CONVERT(VARCHAR(100), @Latitude) + '')'', 4326);
IF EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @UniqueID)
BEGIN
DECLARE @OldGeo GEOGRAPHY
,@OldCity NVARCHAR(100)
,@OldCountry NVARCHAR(100)
,@OldAddress NVARCHAR(100);
SELECT @OldGeo = [LastUpdatedLocationFromJob]
,@OldCity = [City]
,@OldCountry = [Country]
,@OldAddress = [Address]
FROM DeviceCatalog
WHERE [UniqueID] = @UniqueID;
UPDATE DeviceCatalog
SET [OS] = @OS
,[Location] = @Geo
,[Culture] = @Culture
,[Other] = @Other
,[IPAddress] = @IPAddress
WHERE [UniqueID] = @UniqueID;
IF (@OldGeo IS NULL OR @OldAddress IS NULL OR @OldCity IS NULL OR @OldCountry IS NULL OR ISNULL(@Geo.STDistance(@OldGeo) / 1000,0) > 50)
BEGIN
UPDATE DeviceCatalog
SET [Lastmodifieddate] = Getdate()
WHERE [UniqueID] = @UniqueID;
END
END
ELSE
BEGIN
INSERT INTO DeviceCatalog
([OS]
,[UniqueID]
,[Location]
,[Culture]
,[Other]
,[IPAddress]
,[NativeDeviceID])
VALUES (@OS
,@UniqueID
,@Geo
,@Culture
,@Other
,@IPAddress
,@NativeDeviceID);
IF(@OldUniqueID != ''-1'' AND @OldUniqueID != @UniqueID)
BEGIN
EXEC DeleteOldAndroidDeviceID @OldUniqueID, @UniqueID;
END
END
LBEXIT:
IF @TranCount = 0
COMMIT;
END TRY
BEGIN CATCH
DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
SELECT @Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();
IF @XState = -1
ROLLBACK;
IF @XState = 1 AND @TranCount = 0
rollback
IF @XState = 1 AND @TranCount > 0
ROLLBACK TRANSACTION Insertorupdatedevicecatalog;
RAISERROR (''Insertorupdatedevicecatalog: %d: %s'', 16, 1, @error, @message) ;
END CATCH
END
推荐答案
由于在同一事务中对同一表进行两次更新而发生超时.你可以用 case 语句来避免它.也可以用合并替换整个 IF ELSE.
The timeout occurs due to two updates to same table inside same transaction. You could avoid it with a case statement. Also whole IF ELSE can be replaced with a merge.
MERGE INTO DeviceCatalog DC
USING (SELECT @UniqueID AS UniqueID) T ON (DC.UniqueID = T.UniqueID)
WHEN MATCHED THEN
UPDATE SET [OS] = @OS
,[Location] = @Geo
,[Culture] = @Culture
,[Other] = @Other
,[IPAddress] = @IPAddress
,[Lastmodifieddate] = (CASE
WHEN (LastUpdatedLocationFromJob IS NULL OR [Address] IS NULL OR [City] IS NULL OR [Country] IS NULL OR ISNULL(@Geo.STDistance(LastUpdatedLocationFromJob) / 1000,0) > 50)
THEN Getdate()
ELSE [Lastmodifieddate]
END)
WHEN NOT MATCHED THEN
INSERT INTO DeviceCatalog
([OS]
,[UniqueID]
,[Location]
,[Culture]
,[Other]
,[IPAddress]
,[NativeDeviceID])
VALUES (@OS
,@UniqueID
,@Geo
,@Culture
,@Other
,@IPAddress
,@NativeDeviceID)
WHEN NOT MATCHED BY SOURCE AND @OldUniqueID != ''-1'' AND @OldUniqueID != @UniqueID THEN
DELETE;
尝试一下,看看这是否符合您的预期.
Try it and check whether this is what you expected.
这篇关于有时仅从一个 SP 获取请求超时?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文