1
\$\begingroup\$

Can someone please find out why this store procedure is timing out?

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
\$\endgroup\$
  • 1
    \$\begingroup\$ why do you use double ' around things? \$\endgroup\$ – Malachi Nov 13 '13 at 14:42
3
\$\begingroup\$

It's impossible to say what the problem is without knowing how the data is structured, how many rows are in each table, what the indexes are, and other information. Even then, the problem may not even be with this query but with some other query that causes problems for the database as a whole. Basically, your question is far too narrow.

That said, you could try a few things:

  1. Try running this with Explain Plan turned on. That should tell you what part of the query is taking the longest.
  2. Run SET STATISTICS IO ON; and then your query. That will give you an idea if the I/O usage is high.
  3. If the above don't suggest improvement, check the overall DB for problems. Glenn Berry's SQL diagnostics queries are a good place to start.
  4. If you still can't figure it out, hire someone like Glenn to help you. Or start reading a lot of books on SQL Server optimization.

(disclaimer: Glenn's a friend and former colleague of mine, but he is really good at this stuff. I don't get kickbacks. Maybe a beer, but probably not.)

\$\endgroup\$
0
\$\begingroup\$

What if @TranCount < 1, would that cause a weird looping condition?

   IF @TranCount = 0
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION Insertorupdatedevicecatalog;

In the example on Microsoft's SAVE TRANSACTION (Transact-SQL) it gives an example like this:

IF @TranCounter > 0
    SAVE TRANSACTION ProcedureSave;
\$\endgroup\$
  • \$\begingroup\$ rusanu.com/2009/06/11/… \$\endgroup\$ – user960567 Nov 13 '13 at 15:00
  • \$\begingroup\$ ok, that is someone's blog from 2009, and I posted a link to Microsoft. I would take a look at what Microsoft has to say on this and see if you can make it look like theirs, the code you presented is timing out, maybe this is why. \$\endgroup\$ – Malachi Nov 13 '13 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.