?
Solved

Help in speeding up SQL Server geographical data query

Posted on 2016-10-18
9
Medium Priority
?
72 Views
Last Modified: 2016-10-18
Hi Guys,

I have got a stored procedure which creates some geographical shapes.
USE [Spaces];
GO
/****** Object:  StoredProcedure [CrossContext].[USP_Locations_AddPointLocations]    Script Date: 18/10/2016 13:53:44 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		<Ali Shah>
-- Create date: <18/10/2016,,>
-- Description:	Add the polygon shape data in the database 
-- =============================================
ALTER PROCEDURE [CrossContext].[USP_Locations_AddPolygonShape]
    @LocationGuid VARCHAR(MAX) ,
    @AddressStringId INT ,
    @LocationName VARCHAR(200) ,
    @AddressLine1 VARCHAR(100) ,
    @AddressLine2 VARCHAR(100) ,
    @AddressLine3 VARCHAR(100) ,
    @BuildingNumbers VARCHAR(100) ,
    @BuildingName VARCHAR(100) ,
    @Street VARCHAR(100) ,
    @Locality VARCHAR(100) ,
    @Municipality VARCHAR(200) ,
    @AdministrativeArea VARCHAR(100) ,
    @SubAdministrativeArea VARCHAR(100) ,
    @PostalCode VARCHAR(100) ,
    @Iso3166MinusOneCountryAlpha2Code VARCHAR(2) ,
    @Wgs84Longitude VARCHAR(50) ,
    @Wgs84Latitude VARCHAR(50) ,
    @UniqueHashString VARCHAR(64) ,
    @PointBufferDistanceInMeters FLOAT ,
    @ErrorNumber INT OUTPUT ,
    @ErrorSeverity INT OUTPUT ,
    @ErrorState INT OUTPUT ,
    @ErrorProcedure VARCHAR(128) OUTPUT ,
    @ErrorLine INT OUTPUT ,
    @ErrorMessage VARCHAR(4000) OUTPUT ,
    @NumberOfInsertedLocations INT OUTPUT ,
    @SearchString VARCHAR(MAX)
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @LocationGuid1 AS UNIQUEIDENTIFIER = HASHBYTES('SHA1',
                                                              CONVERT(VARCHAR(12), @LocationGuid));
        DECLARE @UniqueHashString1 AS VARCHAR(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;


			
            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 VARCHAR(MAX))
                                                       + N' '
                                                       + CAST(Wgs84Latitude AS VARCHAR(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'');
         


            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;

          

            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

This is taking approximately 6-7 minutes for 1000 records.

Please find attached the query execution plan. It seems that it is taking lot of time in nested loops. How can i improve it?

QE Plan
0
Comment
Question by:shah36
  • 5
  • 4
9 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41848360
Can you provide the Query Execution Plan as .sqlplan file?
0
 

Author Comment

by:shah36
ID: 41848363
Thanks Vitor,
Please find attached
QEPlan.sqlplan
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41848375
Can you try this version?
INSERT  INTO [PhysicalSpace].[LocationShapes]
SELECT  NEWID() ,
        Geography::STPointFromText(Wkt, 4326) AS Point ,
        1
FROM (SELECT Point.STAsText() AS Wkt
	FROM TempTable AS S
    WHERE NOT EXISTS (SELECT 42
					FROM [PhysicalSpace].[LocationShapes]
                    WHERE S.Point.STDistance(ShapeData) < 3.0 AND [IsPoint] = 1)
	GROUP BY Point.STAsText()
      ) X

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:shah36
ID: 41848381
Hi Vitor,

I have transferred all data into another table AddressSearchString and replaced the temp table with this one. It is the same query you suggested and it took only one second to execute.
INSERT  INTO [PhysicalSpace].[LocationShapes]
SELECT  NEWID() ,
        Geography::STPointFromText(Wkt, 4326) AS Point ,
        1
FROM (SELECT TOP(1000) Point.STAsText() AS Wkt
	FROM AddressSearchString AS S
    WHERE NOT EXISTS (SELECT 42
					FROM [PhysicalSpace].[LocationShapes]
                    WHERE S.Point.STDistance(ShapeData) < 3.0 AND [IsPoint] = 1)
	GROUP BY Point.STAsText()
      ) X

Open in new window


Am i right in assuming that it has inserted top(1000) records from AddressSearchString table correctly into locatinSHapes ?
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 41848388
Am i right in assuming that it has inserted top(1000) records from AddressSearchString table correctly into locatinSHapes ?
Yes, but be careful with the TOP keyword without an ORDER BY since you don't know which 1000 records the engine will process.
1
 

Author Closing Comment

by:shah36
ID: 41848390
Thank you so much with your advise and help. I am now going to transfer all the data and see how long does it take.
regards,
Ali
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41848396
Return with a good number, please :)
1
 

Author Comment

by:shah36
ID: 41848422
For 360,000 records it only took a fraction of a second. This is much quicker than the SSIS. With the help of your query and eliminating SSIS it is awesome. With SSIS it is still taking ages but who cares. Job is done.

Thanks again
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41849529
So no coffee break during the process. Sorry about that :)
1

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 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