Solved

Help in speeding up SQL Server geographical data query

Posted on 2016-10-18
9
34 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 45

Expert Comment

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

Author Comment

by:shah36
Comment Utility
Thanks Vitor,
Please find attached
QEPlan.sqlplan
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
 

Author Comment

by:shah36
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Return with a good number, please :)
1
 

Author Comment

by:shah36
Comment Utility
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 45

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

10 Experts available now in Live!

Get 1:1 Help Now