4
Answers

How to reduce query execution time in sql server

Hi,
 
In my table having 32 lakh records ,
 
If I execute query it takes 20 seconds for execution.
 
This is my procedure :
 
 
ALTER PROCEDURE [dbo].[SpReturnNearbyAddresses]
@clientLat float = Null,
@clientLong float = Null,
@maxRadius float = Null,
@district varchar(100),
@state varchar(100)=null
AS
BEGIN
DECLARE @MinLat float;
DECLARE @MaxLat float;
DECLARE @MinLong float;
DECLARE @MAXLong float;
SET @MinLat= @clientLat-(@maxRadius*0.03)
SET @MaxLat= @clientLat+(@maxRadius*0.03)
SET @MinLong= @clientLong-(@maxRadius*0.03)
SET @MAXLong= @clientLong+(@maxRadius*0.03)
IF(@state='WA')
BEGIN
SELECT AddressID,Number,Street, City,Zip, Geo_Lat, Geo_Long,dbo.CalcDistance(@clientLat, @clientLong,
tbl_Washington.Geo_Lat, tbl_Washington.Geo_Long, 0) as Distance FROM
tbl_Washington
WHERE dbo.CalcDistance(@clientLat, @clientLong,
tbl_Washington.Geo_Lat, tbl_Washington.Geo_Long, 0) <= @maxRadius
AND CAST(Geo_Lat as float) BETWEEN @MinLat AND @MaxLat
AND CAST(Geo_Long as float) BETWEEN @MinLong AND @MAXLong
--AND District=@district
AND State=@state
--ORDER BY Distance ASC
-- temp table is ordered by ASC
END
ELSE
BEGIN
SELECT AddressID,Number,Street, City,Zip, Geo_Lat, Geo_Long,dbo.CalcDistance(@clientLat, @clientLong,
tbl_Washington.Geo_Lat, tbl_Washington.Geo_Long, 0) as Distance FROM
tbl_Washington
WHERE dbo.CalcDistance(@clientLat, @clientLong,
tbl_Washington.Geo_Lat, tbl_Washington.Geo_Long, 0) <= @maxRadius
AND District=@district OR State=@state
END
END
 
How to reduce execution time please tell me.
 
Thanks 
 

Answers (4)