Kudos for you all guys!!!

By adding adr_delete_flag in indexes it tooks only 2 seconds.

Keep it up. And again thanks to you all.

Oh! I didn't look that third tab "Execute Plan" after query execution.

Here is actual exe plan for you guys.

Hi,

Attached is the actual execution plan for those two SQLs. Let me explain how I got these sql plan files.

1. I click on "Include Actual Execution Plan'

2. Ran SQL...

Hi,

Please find the attached execution plans for those two SQL.

Let me know you should need more details if any.

Thanks!

Hi,

SQLs are taking 20 and 15 seconds respectively. I did try using both temp table and table variable no major difference.

Thanks!

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...

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 ...

INSERT INTO @latlongdtlinkm(adr_key,latlonkm)

SELECT adr_key,(6367...

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...

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"...

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...

Hi Muhammad,

It's giving an error as below

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near ','.

Hi Muhammad,

It's running without error but not updating club_status in table unfortunately.

thanks for your reply!!!

Seems this Pivot will work for me. Thanks for you reply!