How to improve performance of Stored Procedure in SQL Server 2014
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?
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;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
ASKER
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,