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
Solved

Help in speeding up SQL Server geographical data query

Posted on 2016-10-18
9
47 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 48

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 48

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 48

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 48

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 48

Expert Comment

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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 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