January 17, 2011 at 3:48 am
Hi All,
I have one SP to performance tune and the MAX function is not properly working on the VARCHAR and NVARCHAR columns. Please suggest some steps to improve overall performance of this SP and to use MAX in a proper way.
CREATE PROCEDURE [dbo].[sp_client_ri_get_Clubs_within_given_range_bagulb]
@varLatitude FLOAT = NULL
, @varLongitude FLOAT = NULL
, @varRange FLOAT = 30
, @varDayOfWeek NVARCHAR(10) = NULL --Added for day of the week
, @varUnitOfMeasure NVARCHAR(10) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @varDayOfWeekFlag BIT-- Added for day of the week
DECLARE @varClubPresident NVARCHAR(128)
DECLARE @varClubSecretary NVARCHAR(128)
SELECT @varClubPresident = fws_value FROM fw_system_option(NOLOCK) WHERE fws_option = 'ClientRIRelationshipClubPresidentCode'
SELECT @varClubSecretary = fws_value FROM fw_system_option(NOLOCK) WHERE fws_option = 'ClientRIRelationshipClubSecretaryCode'
--Added for day of the week
IF (@varDayOfWeek IS NULL OR @varDayOfWeek = '')
SET @varDayOfWeekFlag = 0
ELSE
SET @varDayOfWeekFlag = 1
DECLARE @TBL_CLUB_INFO TABLE (
Address_Key av_key
, Customer_Key av_key
, Club_Name NVARCHAR(300)
, Club_ID av_integer
, Club_Area NVARCHAR(120)
, Club_State NVARCHAR(80)
, Meeting_Time CHAR(4)
, Meeting_Loc NVARCHAR(1028)
, Meeting_Day NVARCHAR(10)
, BaseLatitude DECIMAL(19,4)
, BaseLongitude DECIMAL(19,4)
, ToLatitude DECIMAL(19,4)
, ToLongitude DECIMAL(19,4)
, DistanceInMiles DECIMAL(19,2)
, DistanceInKM DECIMAL(19,2)
, IsPrimaryAddress CHAR(1)
, cty_idd_code NVARCHAR(14)
)
IF(@varUnitOfMeasure = 'Km')
BEGIN
INSERT INTO @TBL_CLUB_INFO (Address_Key, Customer_Key, Club_Name, Club_ID, Club_Area, Club_State, Meeting_Time, Meeting_Loc, Meeting_Day, BaseLatitude, BaseLongitude, ToLatitude, ToLongitude, DistanceInMiles, DistanceInKM, IsPrimaryAddress, cty_idd_code
)
SELECT TOP 11
adr_key AS Address_Key
, cst_key AS Customer_Key
, org_name_noaccent_ext AS Club_Name
, org_club_id_ext AS Club_ID
, adr_country AS [Area]
, ISNULL(adr_intl_province,ISNULL(adr_state,'')) AS [State]
, ISNULL(adr_meeting_time_ext,'') AS [Time]
, adr_meeting_name_ext + ', ' + REPLACE(cxa_mailing_label_html,'
',', ') AS Loc
, adr_meeting_day_ext AS Meeting_Day
, @varLatitude AS BaseLatitude
, @varLongitude AS BaseLongitude
, adr_latitude AS ToLatitude
, adr_longitude AS ToLongitude
, dbo.HaversineDistanceMiles(@varLatitude,@varLongitude,adr_latitude,adr_longitude) AS DistanceInMiles
, dbo.HaversineDistanceKm(@varLatitude,@varLongitude,adr_latitude,adr_longitude) AS DistanceInKM
, CASE WHEN cst_cxa_key = cxa_key THEN 'Y' ELSE 'N' END AS IsPrimaryAddress
, cty_idd_code
FROM co_address(NOLOCK)
JOIN co_address_ext(NOLOCK) ON adr_key_ext = adr_key
JOIN co_customer_x_address(NOLOCK) ON cxa_adr_key = adr_key
JOIN co_address_type(NOLOCK) ON cxa_adt_key = adt_key
JOIN co_country(NOLOCK) ON cty_code = adr_country
JOIN co_customer(NOLOCK) ON cst_key = cxa_cst_key
JOIN co_organization(NOLOCK) ON cst_key = org_cst_key
JOIN co_organization_ext(NOLOCK) ON org_cst_key = org_cst_key_ext
WHERE
dbo.HaversineDistanceKm(@varLatitude,@varLongitude,adr_latitude,adr_longitude) <= @varRange
AND adt_code = 'Meeting Location'
AND org_ogt_code = 'Rotary Club'
AND adr_delete_flag = 0
AND org_delete_flag = 0
AND cst_delete_flag = 0
AND cxa_delete_flag = 0
--Added for day of the week
AND ((@varDayOfWeekFlag = 0)OR (adr_meeting_day_ext = @varDayOfWeek))
END
ELSE
BEGIN
INSERT INTO @TBL_CLUB_INFO (Address_Key, Customer_Key, Club_Name, Club_ID, Club_Area, Club_State, Meeting_Time, Meeting_Loc, Meeting_Day, BaseLatitude, BaseLongitude, ToLatitude, ToLongitude, DistanceInMiles, DistanceInKM, IsPrimaryAddress, cty_idd_code
)
SELECT TOP 11
adr_key AS Address_Key
, cst_key AS Customer_Key
, org_name_noaccent_ext AS Club_Name
, org_club_id_ext AS Club_ID
, adr_country AS [Area]
, ISNULL(adr_intl_province,ISNULL(adr_state,'')) AS [State]
, ISNULL(adr_meeting_time_ext,'') AS [Time]
, adr_meeting_name_ext + ', ' + REPLACE(cxa_mailing_label_html,'
',', ') AS Loc
, adr_meeting_day_ext AS Meeting_Day
, @varLatitude AS BaseLatitude
, @varLongitude AS BaseLongitude
, adr_latitude AS ToLatitude
, adr_longitude AS ToLongitude
, dbo.HaversineDistanceMiles(@varLatitude,@varLongitude,adr_latitude,adr_longitude) AS DistanceInMiles
, dbo.HaversineDistanceKm(@varLatitude,@varLongitude,adr_latitude,adr_longitude) AS DistanceInKM
, CASE WHEN cst_cxa_key = cxa_key THEN 'Y' ELSE 'N' END AS IsPrimaryAddress
, cty_idd_code
FROM co_address(NOLOCK)
JOIN co_address_ext(NOLOCK) ON adr_key_ext = adr_key
JOIN co_customer_x_address(NOLOCK) ON cxa_adr_key = adr_key
JOIN co_address_type(NOLOCK) ON cxa_adt_key = adt_key
JOIN co_country(NOLOCK) ON cty_code = adr_country
JOIN co_customer(NOLOCK) ON cst_key = cxa_cst_key
JOIN co_organization(NOLOCK) ON cst_key = org_cst_key
JOIN co_organization_ext(NOLOCK) ON org_cst_key = org_cst_key_ext
WHERE
dbo.HaversineDistanceMiles(@varLatitude,@varLongitude,adr_latitude,adr_longitude) <= @varRange
AND adt_code = 'Meeting Location'
AND org_ogt_code = 'Rotary Club'
AND adr_delete_flag = 0
AND org_delete_flag = 0
AND cst_delete_flag = 0
AND cxa_delete_flag = 0
--Added for day of the week
AND ((@varDayOfWeekFlag = 0)OR (adr_meeting_day_ext = @varDayOfWeek))
END
SELECT
MAX(Club_Name) AS Club_Name
, Club_ID AS Club_ID
, MAX(Club_Area) AS Area
, MAX(Club_State) AS State
, MAX(Meeting_Time) AS Time
, MAX(Meeting_Loc) AS LOC
, MAX(Meeting_Day) AS Meeting_Day
, MAX(BaseLatitude) AS BaseLatitude
, MAX(BaseLongitude) AS BaseLongitude
, MAX(ToLatitude) AS ToLatitude
, MAX(ToLongitude) AS ToLongitude
, MAX(DistanceInMiles) AS DistanceInMiles
, MAX(DistanceInKM) AS DistanceInKM
, MAX(IsPrimaryAddress) AS IsPrimaryAddress
, MAX(CASE ixo_rlt_code WHEN @varClubPresident THEN cst_name_cp ELSE '' END) AS 'President/@Name'
, MAX(CASE ixo_rlt_code WHEN @varClubPresident THEN ISNULL((cty_idd_code + ' ' + cst_phn_number_complete_dn),'')
ELSE '' END) AS 'President/@Contact'
, MAX(CASE ixo_rlt_code WHEN @varClubPresident THEN cst_eml_address_dn ELSE '' END) AS 'President/@Email'
, MAX(CASE ixo_rlt_code WHEN @varClubSecretary THEN cst_name_cp ELSE '' END) AS 'Secretary/@Name'
, MAX(CASE ixo_rlt_code WHEN @varClubSecretary THEN ISNULL((cty_idd_code + ' ' + cst_phn_number_complete_dn),'')
ELSE '' END) AS 'Secretary/@Contact'
, MAX(CASE ixo_rlt_code WHEN @varClubSecretary THEN cst_eml_address_dn ELSE '' END) AS 'Secretary/@Email'
FROM @TBL_CLUB_INFO
LEFT JOIN co_individual_x_organization(NOLOCK) ON Customer_Key = ixo_org_cst_key
AND ixo_rlt_code IN (@varClubPresident,@varClubSecretary)
AND ixo_start_date < dbo.av_begin_of_day(GETDATE())
AND (ixo_end_date IS NULL OR ixo_end_date > dbo.av_end_of_day(GETDATE()))
AND ixo_delete_flag = 0
LEFT JOIN co_customer(NOLOCK) ON cst_key = ixo_ind_cst_key AND cst_delete_flag = 0
GROUP BY Club_ID
ORDER BY DistanceInKM,Club_Name
FOR XML PATH ('Club'), ROOT('Clubs')
END
Thanks in advance!!!
January 17, 2011 at 4:10 am
Are you using 2008 ?
Have you looked at the spatial functions.
I would imagine the 'worst' offender here is using the function HaversineDistanceKm as a SARG.
Making a calculate the min and max longitude and latitude value and use those in you where clause.
January 17, 2011 at 4:53 am
check for following things:
tuning HaversineDistanceKm fn
check for missing or non used indexes
check for statistics
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 17, 2011 at 5:09 am
Yes I am using SS 2008. Thanks for your reply. I need to do some R & D on Spatial Functions in 2008. But my another concern is using MAX function since I am not able to find out correct data.
Thanks!
January 17, 2011 at 5:26 am
Can you provide us with some simpler code that we can execute that will demonstrate your problem with MAX ?
January 18, 2011 at 5:26 am
Hi,
I don't have any issues with MAX function now. I analysed this procedure and find out that most of the processing time is going on in a function call "HaversineDistanceKm" which is being used in where condition. Please let me know how can I improve this one or is there any way I can replace with this function call?
Thanks!
Bhushan
January 18, 2011 at 5:40 am
No you cant replace it but you can cut down on the amount of calls considerably.
As i said before , if you calculate the Geographic square based upon :
@varLatitude +- @varRange KM
@varLongitude +- @varRange KM
Then an index should be usable.
January 18, 2011 at 7:07 am
Hi,
I have updated procedure and removed function calls from everywhere in SP.
However there is one statement in I am inserting records in table variable and using this table further.
INSERT INTO @latlongdtlinkm(adr_key,latlonkm)
SELECTadr_key,(6367 * (2.0 * atn2(sqrt((square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0))))),
sqrt(1.0-(square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0)))))))) latlonkm
FROMco_address (NOLOCK)
This statement is consuming upto 80% of execution time. co_address table is having 5 million records. Please tell me how can I improve this SELECT statement.
Thanks!
January 18, 2011 at 7:34 am
You need to make the query sargable, at present you are looking at EVERY row to decide if that "point" it required.
You need something more like this....
BTW , Havent got time to sort the math , but you get the idea
Declare @MinLat decimal
Declare @MaxLat decimal
Declare @MinLong decimal
Declare @MaxLong decimal
Select @MinLat = @YourSearchLatPoint - (Value representing the radius of your KM search)
Select @MaxLat = @YourSearchLatPoint + (Value representing the radius of your KM search)
Select @MinLong = @YourSearchLongPoint - (Value representing the radius of your KM search)
Select @MaxLong = @YourSearchLongPoint + (Value representing the radius of your KM search)
INSERT INTO @latlongdtlinkm(adr_key,latlonkm)
SELECT adr_key,(6367 * (2.0 * atn2(sqrt((square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0))))),
sqrt(1.0-(square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0)))))))) latlonkm
FROM co_address (NOLOCK)
where co_address.adr_latitude between @MinLat and @MaxLat
and co_address.adr_longitude between @MinLong and @MaxLong
January 18, 2011 at 8:32 am
INSERT INTO @latlongdtlinkm(adr_key,latlonkm)
SELECT adr_key,(6367 * (2.0 * atn2(sqrt((square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0))))),
sqrt(1.0-(square(sin((radians(adr_latitude)-@varLatitudeRadians)/2.0)) +
(cos(@varLatitudeRadians) * cos(radians(adr_latitude)) *square(sin((radians(adr_longitude)-@varLongitudeRadians)/2.0)))))))) latlonkm
FROM co_address (NOLOCK)
where (co_address.adr_latitude IS NOT NULL OR co_address.adr_longitude IS NOT NULL)
I kept this query as above and now it is running fine. Does this where condition make sense?
Thanks!
Bhushan
January 18, 2011 at 9:06 am
In that case, i would assume that the vast majority of you rows have a long or lat of null.
Doesnt really negate what i have said before
January 19, 2011 at 2:42 am
Hi,
I have written SP now to get nearest clubs information by measuring in KM as below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bhushan Bagul
-- Create date: DD-MON-YYYY
-- Description: Get nearest club information
-- =============================================
ALTER PROCEDURE dbo.sp_client_ri_get_clubs_within_km_range
@varLatitudeav_float= NULL,
@varLongitudeav_float= NULL,
@varRangeav_float= 30
AS
BEGIN
SET NOCOUNT ON
DECLARE @latlongdtl TABLE (
adr_keyav_key,
latlonkmav_float
)
SELECT GETDATE()
INSERT INTO @latlongdtl(adr_key,latlonkm)
SELECTca.adr_key,dbo.HaversineDistanceKm(@varLatitude,@varLongitude,ca.adr_latitude,ca.adr_longitude) latlonkm
FROMco_addressAS caWITH (NOLOCK)
WHEREca.adr_delete_flag = 0
AND(ca.adr_latitude IS NOT NULL
ORca.adr_longitude IS NOT NULL)
SELECT GETDATE()
SELECTTOP 11 co.org_cst_keyClub_Key,
coe.org_club_id_extClub_Id,
coe.org_name_noaccent_extClub_Name
FROMco_addressAS caWITH (NOLOCK)
JOIN@latlongdtlAS lldON ca.adr_key = lld.adr_key
JOINco_address_extAS caeWITH (NOLOCK)ON ca.adr_key = cae.adr_key_ext
ANDca.adr_delete_flag = 0
JOINco_customer_x_addressAS ccxa WITH (NOLOCK)ON ccxa.cxa_adr_key = cae.adr_key_ext
ANDccxa.cxa_delete_flag = 0
JOINco_address_typeAS catWITH (NOLOCK)ON ccxa.cxa_adt_key = cat.adt_key
ANDcat.adt_code = 'Meeting Location'
JOINco_organizationAS coWITH (NOLOCK)ON ccxa.cxa_cst_key = co.org_cst_key
ANDco.org_delete_flag = 0
ANDco.org_ogt_code = 'Rotary Club'
JOINco_organization_extAS coeWITH (NOLOCK)ON co.org_cst_key = coe.org_cst_key_ext
WHERElatlonkm <= @varRange
ORDER BYcoe.org_name_noaccent_ext
FOR XML PATH ('Club'), ROOT('Clubs')
SELECT GETDATE()
END
This SP is taking 45 second for execution and 40 seconds are being spent only in inserting records into table variable @latlongdtl. Please suggest how to tune this insert statement so that it executes within 5 seconds.
Thanks!
January 19, 2011 at 3:01 am
Ho ww many records are you inserting into the table variable? You might want to consider replacing it with an appropriately indexed temp table.
January 19, 2011 at 3:08 am
Hi
I have implemented your suggestions and modified insert statement as below.
INSERT INTO @latlongdtl(adr_key,latlonkm)
SELECTca.adr_key,dbo.HaversineDistanceKm(@varLatitude,@varLongitude,ca.adr_latitude,ca.adr_longitude) latlonkm
FROMco_addressAS caWITH (NOLOCK)
WHEREca.adr_delete_flag = 0
ANDca.adr_latitude BETWEEN (@varLatitude-@varRange) AND (@varLatitude+@varRange)
ANDca.adr_longitude BETWEEN (@varLongitude-@varRange) AND (@varLongitude+@varRange)
It has improved performance and now taking 15 seconds to insert around 1100 to 1200 records. I have separate non-unique non-clustered indexes on adr_latitude and adr_longitude columns and index_seek is being happened. So everything is looking fine but however I want to improve it further. Since this code will be used in iPhone apps and 15-20 seconds is too much in such case and forget about the rendring time which we haven't considered yet.
Thanks for your suggestions.
January 19, 2011 at 3:27 am
What is the difference in time between
SELECT ca.adr_key,dbo.HaversineDistanceKm(@varLatitude,@varLongitude,ca.adr_latitude,ca.adr_longitude) latlonkm
FROM co_address AS ca WITH (NOLOCK)
WHERE ca.adr_delete_flag = 0
AND ca.adr_latitude BETWEEN (@varLatitude-@varRange) AND (@varLatitude+@varRange)
AND ca.adr_longitude BETWEEN (@varLongitude-@varRange) AND (@varLongitude+@varRange)
and
SELECT ca.adr_keylatlonkm
FROM co_address AS ca WITH (NOLOCK)
WHERE ca.adr_delete_flag = 0
AND ca.adr_latitude BETWEEN (@varLatitude-@varRange) AND (@varLatitude+@varRange)
AND ca.adr_longitude BETWEEN (@varLongitude-@varRange) AND (@varLongitude+@varRange)
Im assuming that the majority of the time is spent in the function HaversineDistanceKm.
Is that correct ?
Also i would change to using a temp table here and not a table var , but i dont think that that is a major issue right now.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply