Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distance Calc Taking longtime
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

54 Posts

Posted - 10/07/2010 :  05:10:29  Show Profile  Reply with Quote

I am querying the following.but its long time for execution.

can anyone i help.

@zipcode nvarchar(50),
@distnace numeric(18,2),
@longitude nvarchar(50),
@latitude nvarchar(50),
@l_qry nvarchar(max),
@l_larstr nvarchar(max),
@l_latstr nvarchar(max)

SET @zipcode='00610'
SET @distnace = 50

drop table #Result

create table #Result
country nvarchar(max),
state nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)

set @l_larstr='replace(Replace(longitude,'','',''.''),''"'','''')'
set @l_latstr='replace(Replace(latitude,'','',''.''),''"'','''')'

SELECT @longitude=cast(replace(Replace(longitude,',','.'),'"','') as decimal(18,10)),
@latitude=cast(replace(Replace(latitude,',','.'),'"','') as decimal(18,10)) FROM postaldb
WHERE replace(postalcode,'"','')=@zipcode

set @l_qry='insert into #Result (country,state,city,zipcode) select distinct replace(countrycode,''"'','''') as country,replace(region1,''"'','''') as state,
replace(place,''"'','''') as city,replace(postalcode,''"'','''') as zipcode
from postaldb
where (SQRT(POWER(((cast(replace(Replace('+@latitude+','','',''.''),''"'','''') as decimal(18,10))) -(cast(replace(Replace('+@l_latstr+','','',''.''),''"'','''') as decimal(18,10))))*110.7,2)
+POWER((cast(replace(Replace('+@longitude+','','',''.''),''"'','''') as decimal(18,10))-(cast(replace(Replace('+@l_larstr+','','',''.''),''"'','''') as decimal(18,10))))*75.6,2)))
<='+cast(@distnace as varchar)

print @l_qry
exec (@l_qry)

Select * from #result

Previously it takes 30 secs.So i have created the non-clustered Index.
and after that it takes 15 to 20 secs for execution.

But 15 to 20 secs is also too long.It takes time for only calculation.

In my postaldb total records is 6 laksh records.


Flowing Fount of Yak Knowledge

1182 Posts

Posted - 10/07/2010 :  05:20:26  Show Profile  Reply with Quote
Duplicate post.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000