visa123
Yak Posting Veteran
54 Posts |
Posted - 2010-10-07 : 05:10:29
|
HiI 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 = 50drop table #Resultcreate 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 postaldbWHERE replace(postalcode,'"','')=@zipcodeset @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 postaldbwhere (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_qryexec (@l_qry)Select * from #resultPreviously 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 |
|