Link to home
Start Free TrialLog in
Avatar of Kevin Robinson
Kevin Robinson

asked on

Impove long SQL Stored Procedure Performance

I have inherited a long complex stored procedure.  The stored proc works OK but is very  slow.   I intend to refactor this and possible move some of the functions to CLR code.   However in the meantime could anyone recommend any quick improvements that would improve the speed of execution.     When i say slow it can take unto 30 second  to complete a search.   The database is currently hosted in Sql Azure on plan S02.

summary
The stored procedure search records based on 3 main parameters , there are a lot more but the minimum is the following 3.
Postcode
Distance
Activity Type

Doing the distance search calculation is itself relatively simple (and working) .  The complexity comes from the addresses it needs to compare against are in multiple tables.  The results then need to be complied into a result set which is then used ( by the client ) as paging data.  The postcode Latitude and Longitude are pre-poulated in a separate table.

The script is quite long but mainly due to the number of parameters,  but again most of these are optional.

Any tips would  be gratefully appreciated.
script.txt
Executitaion-Plan.sqlplan
Executitaion-Plan1.sqlplan
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Is this the first time are you facing this performance issue?
Few pointers...

1. SELECT
                  OpportunityID,
                  [dbo].[fncGetDistance](@PostcodeLat, @PostcodeLong, POST.Lat, POST.Lng),

--I think we should have a CROSS APPLY to call this function. Also use Inline Table Valued function. I am not sure what kind of function is this.

2. (@PostcodeRadius = 0 OR (@PostcodeRadius <> 0 AND ([dbo].[fncGetDistance](@PostcodeLat, @PostcodeLong, POST.Lat, POST.Lng) <= @PostcodeRadius))) AND

--In this case also use a CROSS APPLY to call this function. Also use Inline Table Valued function. I am not sure what kind of function is this. If we call a function like it will always scan the entire
--table

--3. Also I would like to insert all data into the temp table before this function calling in the where clause and the joins one ( # point 1 and # point 2 )

--4. Remove these, they will also lead to scans. Also never use functions on a column in the where clause.

(LOWER(ORG.OrganisationName) LIKE ('%' + LOWER(@KeyWord) + '%')) OR
                                                                              (LOWER(OPP.Title) LIKE ('%' + LOWER(@KeyWord) + '%')) OR
                                                                              (LOWER(OPP.[Description]) LIKE ('%' + LOWER(@KeyWord) + '%'))

--5. Also you are using parameters directly which is also not good.
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kevin Robinson
Kevin Robinson

ASKER

Awesome will get right on this and report back shortly
This is the caculateDistance funciton.    seems a bit much.   I vaguely remember doing something like this as an inline function.  Not as pretty maybe but  it was faster.

USE [vndev]
GO
/****** Object:  UserDefinedFunction [dbo].[fncGetDistance]    Script Date: 10/4/2016 2:44:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
Title:                  dbo.fncGetDistance
Author:             Unknown
Created:             Unknown
Last Modified:      Unknown
Purpose:
Work out the distance between two co-ordinates based on
latitude and longitude
************************************************************/
ALTER FUNCTION [dbo].[fncGetDistance]
(
      @lat1 FLOAT,
      @long1 FLOAT,
      @lat2 FLOAT,
      @long2 FLOAT
)
RETURNS FLOAT
AS
BEGIN

      -- Declare local variables
      DECLARE @earth FLOAT
      DECLARE @dlong FLOAT
      DECLARE @dlat FLOAT
      DECLARE @sinlat FLOAT
      DECLARE @sinlong FLOAT
      DECLARE @a FLOAT
      DECLARE @c FLOAT
      DECLARE @d FLOAT

      -- Set the radius of the earth - to change into km
      -- use the value below
      -- SET @earth = 6371; -- km
      SET @earth = 3960; --miles

      -- Point 1 cords
      SET @lat1 = dbo.fncDegToRad(@lat1);
      SET @long1= dbo.fncDegToRad(@long1);

      -- Point 2 cords
      SET @lat2 = dbo.fncDegToRad(@lat2);
      SET @long2= dbo.fncDegToRad(@long2);

      -- Haversine Formula (http://en.wikipedia.org/wiki/Haversine_formula)
      SET @dlong=@long2-@long1;
      SET @dlat=@lat2-@lat1;

      SET @sinlat=Sin(@dlat/2);
      SET @sinlong=Sin(@dlong/2);

      SET @a=(@sinlat*@sinlat)+Cos(@lat1)*Cos(@lat2)*(@sinlong*@sinlong);
      SET @c=2*Asin(CASE WHEN Sqrt(@a) < 1 THEN Sqrt(@a) ELSE 1 END);
      SET @d=Round(@earth*@c,1);

      RETURN @d;
      
END
Looks that's only math operations and not really accessing any data so no impact.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kevin, with all this information do you still have performance issues?
Sorry still working on this.  Will come back later to close
Kevin, please close this question since you said that's working.
Cheers.
Hi Kevin,

Do you need more help with this ?

Regards,
Pawan
"Kevin, please close this question since you said that's working".
No, I said I was still working on it.

However I will close the question.
No, I said I was still working on it.
Right. My bad.