Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to improve performance of Stored Procedure in SQL Server 2014

Posted on 2016-10-18
5
40 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 48

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

839 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