Solved

How to improve performance of Stored Procedure in SQL Server 2014

Posted on 2016-10-18
5
32 Views
Last Modified: 2016-10-18
Hi guys,

I need to transfer 30 millions of rows of data from one server to another server. The requirement is to break down each record and put the contents in separate table. I am using Script Component in SSIS to call the stored procedure for each record. I have tested it against 350,000 records and it is taking almost one minute per 1000 records.
below is my stored procedure. Any suggestions to make it quicker?

USE [Spaces_PAF]
GO
/****** Object:  StoredProcedure [CrossContext].[USP_Locations_AddPointLocations]    Script Date: 18/10/2016 10:40:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	
-- =============================================
ALTER PROCEDURE [CrossContext].[USP_Locations_AddPointLocations]
    @LocationGuid NVARCHAR(MAX) ,
    @AddressStringId INT ,
    @LocationName NVARCHAR(200) ,
    @AddressLine1 NVARCHAR(100) ,
    @AddressLine2 NVARCHAR(100) ,
    @AddressLine3 NVARCHAR(100) ,
    @BuildingNumbers NVARCHAR(100) ,
    @BuildingName NVARCHAR(100) ,
    @Street NVARCHAR(100) ,
    @Locality NVARCHAR(100) ,
    @Municipality NVARCHAR(200) ,
    @AdministrativeArea NVARCHAR(100) ,
    @SubAdministrativeArea NVARCHAR(100) ,
    @PostalCode NVARCHAR(100) ,
    @Iso3166MinusOneCountryAlpha2Code NVARCHAR(2) ,
    @Wgs84Longitude NVARCHAR(50) ,
    @Wgs84Latitude NVARCHAR(50) ,
    @UniqueHashString NVARCHAR(64) ,
    @PointBufferDistanceInMeters FLOAT ,
    @ErrorNumber INT OUTPUT ,
    @ErrorSeverity INT OUTPUT ,
    @ErrorState INT OUTPUT ,
    @ErrorProcedure NVARCHAR(128) OUTPUT ,
    @ErrorLine INT OUTPUT ,
    @ErrorMessage NVARCHAR(4000) OUTPUT ,
    @NumberOfInsertedLocations INT OUTPUT ,
    @SearchString NVARCHAR(MAX)
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @LocationGuid1 AS UNIQUEIDENTIFIER = HASHBYTES('SHA1',
                                                              CONVERT(VARCHAR(12), @LocationGuid));
        DECLARE @UniqueHashString1 AS NVARCHAR(MAX) = HASHBYTES('MD2',
                                                              CONVERT(VARCHAR(12), @LocationGuid));  
        SET @Wgs84Longitude = CAST(LTRIM(RTRIM(@Wgs84Longitude)) AS FLOAT);
        SET @Wgs84Latitude = CAST(LTRIM(RTRIM(@Wgs84Latitude)) AS FLOAT);

       


        BEGIN TRY
            BEGIN TRANSACTION;

            DECLARE @UtcNow DATETIME2;
            DECLARE @EndUtc DATETIME2;

            SET @UtcNow = GETUTCDATE();
            SET @EndUtc = DATETIME2FROMPARTS(2100, 12, 31, 0, 0, 0, 0, 0);

			
            INSERT  INTO TempTable
                    ( LocationGuid ,
                      AddressStringId ,
                      LocationName ,
                      AddressLine1 ,
                      AddressLine2 ,
                      AddressLine3 ,
                      BuildingNumbers ,
                      BuildingName ,
                      Street ,
                      Locality ,
                      Municipality ,
                      AdministrativeArea ,
                      SubAdministrativeArea ,
                      PostalCode ,
                      Iso3166MinusOneCountryAlpha2Code ,
                      Wgs84Longitude ,
                      Wgs84Latitude ,
                      UniqueHashString ,
                      SearchString
                    )
            VALUES  ( @LocationGuid1 ,
                      @AddressStringId ,
                      @LocationName ,
                      @AddressLine1 ,
                      @AddressLine2 ,
                      @AddressLine3 ,
                      @BuildingNumbers ,
                      @BuildingName ,
                      @Street ,
                      @Locality ,
                      @Municipality ,
                      @AdministrativeArea ,
                      @SubAdministrativeArea ,
                      @PostalCode ,
                      @Iso3166MinusOneCountryAlpha2Code ,
                      @Wgs84Longitude ,
                      @Wgs84Latitude ,
                      @UniqueHashString1 ,
                      @SearchString
                    );

            UPDATE  TempTable
            SET     Point = Geography::STPointFromText(N'POINT('
                                                       + CAST(Wgs84Longitude AS NVARCHAR(MAX))
                                                       + N' '
                                                       + CAST(Wgs84Latitude AS NVARCHAR(MAX))
                                                       + N')', 4326); 

            --DROP INDEX SpatialIndex ON TempTable;
            --CREATE SPATIAL INDEX SpatialIndex ON TempTable (Point);

            UPDATE  TempTable
            SET     LocationName = COALESCE(LTRIM(RTRIM(LOWER(LocationName))),
                                            N'') ,
                    AddressLine1 = COALESCE(LTRIM(RTRIM(LOWER(AddressLine1))),
                                            N'') ,
                    AddressLine2 = COALESCE(LTRIM(RTRIM(LOWER(AddressLine2))),
                                            N'') ,
                    AddressLine3 = COALESCE(LTRIM(RTRIM(LOWER(AddressLine3))),
                                            N'') ,
                    BuildingNumbers = COALESCE(LTRIM(RTRIM(LOWER(BuildingNumbers))),
                                               N'') ,
                    BuildingName = COALESCE(LTRIM(RTRIM(LOWER(BuildingName))),
                                            N'') ,
                    Street = COALESCE(LTRIM(RTRIM(LOWER(Street))), N'') ,
                    Locality = COALESCE(LTRIM(RTRIM(LOWER(Locality))), N'') ,
                    Municipality = COALESCE(LTRIM(RTRIM(LOWER(Municipality))),
                                            N'') ,
                    AdministrativeArea = COALESCE(LTRIM(RTRIM(LOWER(AdministrativeArea))),
                                                  N'') ,
                    SubAdministrativeArea = COALESCE(LTRIM(RTRIM(LOWER(SubAdministrativeArea))),
                                                     N'') ,
                    PostalCode = COALESCE(LTRIM(RTRIM(LOWER(PostalCode))), N'') ,
                    Iso3166MinusOneCountryAlpha2Code = COALESCE(LTRIM(RTRIM(LOWER(Iso3166MinusOneCountryAlpha2Code))),
                                                              N'');
            

			------ get rid of duplicates based on hash
            --DELETE  FROM TempTable
            --WHERE   LocationGuid IN (
            --        SELECT DISTINCT
            --                S.LocationGuid AS Wkt
            --        FROM    TempTable AS S
            --        WHERE   EXISTS ( SELECT 42
            --                         FROM   [CrossContext].[Locations] AS I
            --                         WHERE  S.UniqueHashString = I.UniqueHashString ) );

            SELECT  @NumberOfInsertedLocations = COUNT(*)
            FROM    TempTable;

            IF ( @NumberOfInsertedLocations <> 0 )
                BEGIN 
			

                    
			----		-- location names
                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.LocationNames
                                    WHERE   String = @LocationName )
                        BEGIN
					
                            INSERT  INTO [AdministrativeSpace].[LocationNames]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @LocationName;

            
                          
							
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.AddressLines
                                    WHERE   String = @AddressLine1 )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[AddressLines]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @AddressLine1;

                    
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.AddressLines
                                    WHERE   String = @AddressLine2 )
                        BEGIN
                            
                            INSERT  INTO [AdministrativeSpace].[AddressLines]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @AddressLine2;

                    

						
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.AddressLines
                                    WHERE   String = @AddressLine3 )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[AddressLines]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @AddressLine3;

                   

                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.BuildingNumbers
                                    WHERE   String = @BuildingNumbers )
                        BEGIN 
                            INSERT  INTO [AdministrativeSpace].[BuildingNumbers]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @BuildingNumbers;

                   
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.BuildingNames
                                    WHERE   String = @BuildingName )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[BuildingNames]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @BuildingName;

                   
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.Streets
                                    WHERE   String = @Street )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[Streets]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @Street;

                   
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.Localities
                                    WHERE   String = @Locality )
                        BEGIN
                   
                            INSERT  INTO [AdministrativeSpace].[Localities]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @Locality;

                   
                        END; 

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.Municipalities
                                    WHERE   String = @Municipality )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[Municipalities]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @Municipality;
                   
                        END;

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.AdministrativeAreas
                                    WHERE   String = @AdministrativeArea )
                        BEGIN
                            INSERT  INTO [AdministrativeSpace].[AdministrativeAreas]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @AdministrativeArea;

                   
                        END;
                                    
				
                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.SubAdministrativeAreas
                                    WHERE   String = @SubAdministrativeArea )
                        BEGIN
               
                            INSERT  INTO [AdministrativeSpace].[SubAdministrativeAreas]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @SubAdministrativeArea;

                   
                        END; 

                    IF NOT EXISTS ( SELECT  String
                                    FROM    AdministrativeSpace.PostalCodes
                                    WHERE   String = @PostalCode )
                        BEGIN 
                            INSERT  INTO [AdministrativeSpace].[PostalCodes]
                                    SELECT  NEWID() ,
                                            @UtcNow ,
                                            @EndUtc ,
                                            1 ,
                                            @PostalCode;

                   
                        END; 

			----		--locationshape data is not needed for the bulk import. This will be calculated later with address picker

   --                 --INSERT  INTO [PhysicalSpace].[LocationShapes]
   --                 --        SELECT  NEWID() ,
   --                 --                Geography::STPointFromText(Wkt, 4326) AS Point ,
   --                 --                1
   --                 --        FROM    ( SELECT DISTINCT
   --                 --                            Point.STAsText() AS Wkt
   --                 --                  FROM      TempTable AS S
   --                 --                  WHERE     NOT EXISTS ( SELECT
   --                 --                                          42
   --                 --                                         FROM
   --                 --                                          [PhysicalSpace].[LocationShapes]
   --                 --                                         WHERE
   --                 --                                          S.Point.STDistance(ShapeData) < @PointBufferDistanceInMeters
   --                 --                                          AND [IsPoint] = 1 )
   --                 --                ) X;

   --                 --UPDATE  u
   --                 --SET     u.LocationShapeId = s.[Id]
   --                 --FROM    TempTable u
   --                 --        INNER JOIN [PhysicalSpace].[LocationShapes] s ON s.[ShapeData].STDistance(Point) < @PointBufferDistanceInMeters
   --                 --                                          AND IsPoint = 1;				
   --UPDATE  u
			--	SET     u.LocationShapeId = s.[Id]
			--	FROM    TempTable u
			--			INNER JOIN [PhysicalSpace].[LocationShapes] s ON s.[ShapeData].STDistance(Point) < @PointBufferDistanceInMeters
			--													  AND IsPoint = 1;				
                    UPDATE  u
                    SET     u.LocationNameId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[LocationNames] s ON s.String = u.LocationName;
                    UPDATE  u
                    SET     u.AddressLine1Id = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[AddressLines] s ON s.String = u.AddressLine1;
                    UPDATE  u
                    SET     u.AddressLine2Id = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[AddressLines] s ON s.String = u.AddressLine2;
                    UPDATE  u
                    SET     u.AddressLine3Id = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[AddressLines] s ON s.String = u.AddressLine3;
                    UPDATE  u
                    SET     u.BuildingNumbersId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[BuildingNumbers] s ON s.String = u.BuildingNumbers;
                    UPDATE  u
                    SET     u.BuildingNameId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[BuildingNames] s ON s.String = u.BuildingName;
                    UPDATE  u
                    SET     u.StreetId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[Streets] s ON s.String = u.Street;
                    UPDATE  u
                    SET     u.LocalityId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[Localities] s ON s.String = u.Locality;
                    UPDATE  u
                    SET     u.MunicipalityId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[Municipalities] s ON s.String = u.Municipality;
                    UPDATE  u
                    SET     u.AdministrativeAreaId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[AdministrativeAreas] s ON s.String = u.AdministrativeArea;
                    UPDATE  u
                    SET     u.SubAdministrativeAreaId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[SubAdministrativeAreas] s ON s.String = u.SubAdministrativeArea;
                    UPDATE  u
                    SET     u.PostalCodeId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[PostalCodes] s ON s.String = u.PostalCode;
                    UPDATE  u
                    SET     u.Iso3166MinusOneCountryId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [AdministrativeSpace].[Iso3166MinusOneCountries] s ON s.[Alpha2Code] = u.Iso3166MinusOneCountryAlpha2Code;
	
                    MERGE [CrossContext].[Locations] AS T
                    USING TempTable AS S
                    ON T.Guid = S.LocationGuid
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.LocationNameId = S.LocationNameId ,
                                   T.LocationShapeId = S.LocationShapeId ,
                                   T.UniqueHashString = S.UniqueHashString
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationNameId] ,
                                 [LocationShapeId] ,
                                 [StatusId] ,
                                 [UniqueHashString] ,
                                 AddressStringId
						
                               )
                        VALUES ( S.LocationGuid ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 , -- active
                                 S.LocationNameId ,
                                 S.LocationShapeId ,
                                 1 , -- approved 
                                 UniqueHashString ,
                                 @AddressStringId
						
                               );

                    UPDATE  u
                    SET     u.LocationId = s.[Id]
                    FROM    TempTable u
                            INNER JOIN [CrossContext].[Locations] s ON s.Guid = u.LocationGuid;

                    MERGE [CrossContext].[LocationToAddressLine1] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.AddressLineId = S.[AddressLine1Id]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [AddressLineId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.AddressLine1Id
						
                               );

                    MERGE [CrossContext].[LocationToAddressLine2] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.AddressLineId = S.[AddressLine2Id]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [AddressLineId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.AddressLine2Id
						
                               );

                    MERGE [CrossContext].[LocationToAddressLine3] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.AddressLineId = S.[AddressLine3Id]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [AddressLineId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.AddressLine3Id
						
                               );

                    MERGE [CrossContext].[LocationToBuildingNumbers] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.BuildingNumbersId = S.[BuildingNumbersId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [BuildingNumbersId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.BuildingNumbersId
						
                               );

                    MERGE [CrossContext].[LocationToBuildingName] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.BuildingNameId = S.[BuildingNameId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [BuildingNameId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.BuildingNameId
						
                               );

                    MERGE [CrossContext].[LocationToStreet] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.StreetId = S.[StreetId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [StreetId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.StreetId
						
                               );

                    MERGE [CrossContext].[LocationToLocality] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.LocalityId = S.[LocalityId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [LocalityId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.LocalityId
						
                               );

                    MERGE [CrossContext].[LocationToMunicipality] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.MunicipalityId = S.[MunicipalityId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [MunicipalityId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.MunicipalityId
						
                               );

                    MERGE [CrossContext].[LocationToAdministrativeArea] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.AdministrativeAreaId = S.[AdministrativeAreaId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [AdministrativeAreaId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.AdministrativeAreaId
						
                               );

                    MERGE [CrossContext].[LocationToSubAdministrativeArea] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.SubAdministrativeAreaId = S.[SubAdministrativeAreaId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [SubAdministrativeAreaId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.SubAdministrativeAreaId
						
                               );

                    MERGE [CrossContext].[LocationToPostalCode] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.PostalCodeId = S.[PostalCodeId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [PostalCodeId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.PostalCodeId
						
                               );

                    MERGE [CrossContext].[LocationToIso3166MinusOneCountry] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.Iso3166MinusOneCountryId = S.[Iso3166MinusOneCountryId]
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [Iso3166MinusOneCountryId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 S.Iso3166MinusOneCountryId
						
                               );

                    MERGE [CrossContext].[LocationToCategory] AS T
                    USING TempTable AS S
                    ON T.LocationId = S.LocationId
                    WHEN MATCHED THEN
                        UPDATE SET T.StartUtcDateTime = @UtcNow ,
                                   T.CategoryId = 5 -- Undefined location category - TODO adjust?
                    WHEN NOT MATCHED THEN
                        INSERT ( [Guid] ,
                                 [StartUtcDateTime] ,
                                 [EndUtcDateTime] ,
                                 [Active] ,
                                 [LocationId] ,
                                 [CategoryId]
						
                               )
                        VALUES ( NEWID() ,
                                 @UtcNow ,
                                 @EndUtc ,
                                 1 ,
                                 S.LocationId ,
                                 5 -- Undefined location category- TODO adjust?
						
                               );
                END;


            INSERT  INTO AddressSearchString
                    SELECT  LocationGuid ,
                            AddressStringId ,
                            LocationName ,
                            AddressLine1 ,
                            AddressLine2 ,
                            AddressLine3 ,
                            BuildingNumbers ,
                            BuildingName ,
                            Street ,
                            Locality ,
                            Municipality ,
                            AdministrativeArea ,
                            SubAdministrativeArea ,
                            PostalCode ,
                            Iso3166MinusOneCountryAlpha2Code ,
                            Wgs84Longitude ,
                            Wgs84Latitude ,
                            Point ,
                            UniqueHashString ,
                            SearchString
                    FROM    TempTable;

            DELETE  FROM dbo.TempTable;
            COMMIT TRANSACTION;

        END TRY
        BEGIN CATCH
            SET @ErrorMessage = ERROR_MESSAGE();
            RAISERROR(@ErrorMessage,17,1);
		--THROW 51000, @ErrorMessage,1
            --SELECT  @ErrorNumber = ERROR_NUMBER() ,
            --        @ErrorSeverity = ERROR_SEVERITY() ,
            --        @ErrorState = ERROR_STATE() ,
            --        @ErrorProcedure = ERROR_PROCEDURE() ,
            --        @ErrorLine = ERROR_LINE() ,
            --        @ErrorMessage = ERROR_MESSAGE();
            ROLLBACK TRANSACTION;
        END CATCH; 
    END;

Open in new window

0
Comment
Question by:shah36
  • 2
  • 2
5 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41848068
If you can post the Query Execution Plan we can check for missing indexes and recommend you the creation of some if needed.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 41848216
<Unrelated thought>
Why are all these columns defined as nvarchar, which is unicode and takes up 2x the memory space, as opposed to varchar?  Do you really have to deal with potential foreign language characters in every column?
1
 

Author Closing Comment

by:shah36
ID: 41848244
Hi Jim,

Thanks a lot actually i inherited all this and it made a difference by changing nvarchar to varchar. Also thanks to Vitor for guiding to check indexes. It speeds up the things and now over 10,000 records are being transferred in a minute.

regards,
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41848263
and now over 10,000 records are being transferred in a minute.
That's a 1000% improvement. If you think is still no good open a new question with the new code and attach the Query Execution Plan so we can analyze it for you.
Cheers
0
 

Author Comment

by:shah36
ID: 41848336
Thanks vitor, there is another question on the way. it's different stored procedure but creating some geographical shapes (polygon etc) and that is taking like 6 minutes for 1000 records.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now