SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

visa123
Yak Posting Veteran

India
54 Posts

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

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

can anyone i help.


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




Visa.G

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 10/07/2010 :  05:20:26  Show Profile  Reply with Quote
Duplicate post.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151211
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000