How to reduce query execution time in sql server

Sivakumar

10y12.5k

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

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

How to reduce execution time please tell me.

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary10y ago
Hello! Shivakumar
It may be possible that if table contains millions of record then it takes large time in execution.
Now a question is arise how to increase the performance in that case. i am providing some tips that can help you to reduce the query execution time.
1. you should try to see if you can reduce the row footprint. For example, is nvarchar really needed? Using Varchar will cut the size in half. This may cut your processing time in half too.
2. Use the Clustered and Non clustered index for frequently used columns.
3. Use Full Text Index in table if you required.
4. Use the Indexed Views for Frequently using query.
5. Use Index for State and District column in your table
0
Ranjan Dailata10y ago
One thing you need to consider is to have a table with a good index. That will solve all your problems. A bad index can also cause issues. So you need to revisit and design accordingly.
0
Manish Kumar10y ago
Hi Shiva,
First of all remove your user defined function from procedure and try to implement function logic with in procedure.
Check the execution plan in brief ,check index created on table whether it is used by or not execution plan or not. We can create covering index if you see key lookup or book mark look up.
In last check parameter sniffing on internet , this will give tremendous performance for a procedure.
0
Gowtham K10y ago
Hello Shivakumar,

Refer this,

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

http://blog.sqlauthority.com/2011/05/02/sql-server-performance-improvement-with-of-executing-stored-procedure-with-result-sets-in-denali/
0