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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distance Calc Taking longtime

Author  Topic 

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-10-07 : 05:10:29
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
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-07 : 05:20:26
Duplicate post.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151211
Go to Top of Page
   

- Advertisement -