Solved

Help in speeding up SQL Server geographical data query

Posted on 2016-10-18
9
52 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
[X]
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
  • 5
  • 4
9 Comments
 
LVL 50

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 50

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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

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 50

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 50

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 50

Expert Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

734 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