Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Only sometimes, I get Request Time-out(means my SP is not responding within a specfied time-period which is 90 seconds). Here is my 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
share|improve this question
    
Are stats on the tables up-to-date ? Try to recompile the SP using sp_recompile SP_NAME. –  Kin Oct 1 '13 at 12:05
    
@Kin, Yes. This work lot of time. Only sometimes request time-out. –  user960567 Oct 1 '13 at 12:32
1  
When it doesn't time out, how long does it normally take to execute? –  Jon Seigel Oct 1 '13 at 13:17
1  
Sounds like some other process may be blocking your stored proc occasionally. Use server tracing or extended events to see what/if blocking is occurring. –  Max Vernon Oct 1 '13 at 13:53
1  
Kind of aside: there's also a race condition possible in this code. You need to add locking hints in the IF EXISTS query to prevent this. See samsaffron.com/blog/archive/2007/04/04/14.aspx –  Jon Seigel Oct 2 '13 at 16:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.