Solved

Help in speeding up SQL Server geographical data query

Posted on 2016-10-18
9
39 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 46

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 46

Accepted Solution

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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 46

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 46

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

13 Experts available now in Live!

Get 1:1 Help Now