SQL Search is slow

The code below is slow and I need help with optimizing it. The reason it is slow is because all records from lkpZipCodesCA are selected when the "distance" column is dynamically calculated. And there are currently more than 900 records. After all 900 records are selected and inserted into #zipcodes, the where clause narrows down the search/dataset (the dataset that is passed back to the application). This search takes about 10 secs on average.

What can I do to optimize it?
a. I added non clustered index with no luck.

Perhaps all zip-codes within 20 miles can be retrieved in a much faster and different way? This search needs to perform under 1 sec.

select str_ZipCode from lkpZipCodesCA where contains()  

Open in new window


 declare @location nvarchar(100)           
 declare  @distance nvarchar(100)           
 declare  @subject int = null
 declare  @online int = null    
  
  declare @lat varchar(20)  
  declare @lon varchar(20)  
  declare @ZipCode varchar(5)  

  select @location = 'm6p3h4'
  select @distance = '20'
  select @subject = 9
  select @online = 0
  
select @lat = latitude, @lon = longitude from dbo.lkpZipCodesCA (nolock) where str_ZipCode = @location  
     
    select d.str_ZipCode  
    into #zipcodes 
    from (select str_ZipCode, ( 3959 * acos( cos( radians( @lat ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( @lon ) ) + sin( radians( @lat ) ) * sin( radians( latitude ) ) ) ) as distance FROM dbo.lkpZipCodesCA  
    where (cos( radians( @lat  ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( @lon ) ) + sin( radians( @lat ) ) * sin( radians( latitude ) ) )  <= 1 and
    (cos( radians( @lat  ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( @lon ) ) + sin( radians( @lat ) ) * sin( radians( latitude ) ) ) >= -1
    ) as d  
    where distance <= @distance 

select * from #zipcodes

Open in new window

LVL 7
TransBindAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Currently the calculations are:
(
3959 
* ACOS( 
        COS( RADIANS( @lat ) ) 
      * COS( RADIANS( latitude ) ) 
      * COS( RADIANS( longitude ) - RADIANS( @lon ) ) 
      + SIN( RADIANS( @lat ) ) 
      * SIN( RADIANS( latitude ) ) 
      )
) AS distance

WHERE 
(
        COS( RADIANS( @lat ) ) 
      * COS( RADIANS( latitude ) ) 
      * COS( RADIANS( longitude ) - RADIANS( @lon ) ) 
      + SIN( RADIANS( @lat ) ) 
      * SIN( RADIANS( latitude ) )
      ) 
<= 1

AND 
(
        COS( RADIANS( @lat ) ) 
      * COS( RADIANS( latitude ) ) 
      * COS( RADIANS( longitude ) - RADIANS( @lon ) ) 
      + SIN( RADIANS( @lat ) ) 
      * SIN( RADIANS( latitude ) )
      ) 
>= -1

Open in new window


So, from that the potential for storing parts of this reduced to just RADIANS( latitude ) & RADIANS( latitude )

        COS( RADIANS( @lat ) )
      * COS( RADIANS( latitude ) )
      * COS( RADIANS( longitude ) - RADIANS( @lon ) )
      + SIN( RADIANS( @lat ) )
      * SIN( RADIANS( latitude ) )

as the other parts are dependent on values from variables.

With this in mind there probably is little to gain from storing those 2 values (it might be worth testing though).

The CROSS APPLY should still be of benefit, and I now believe it is:
SELECT
      str_ZipCode
    , 3959 * ACOS( CA.calc1 ) AS Distance
FROM dbo.lkpZipCodesCA
      CROSS APPLY (SELECT
                        (
                          COS( RADIANS( @lat ) ) 
                        * COS( RADIANS( latitude ) ) 
                        * COS( RADIANS( longitude ) - RADIANS( @lon ) ) 
                        + SIN( RADIANS( @lat ) ) 
                        * SIN( RADIANS( latitude ) ) 
                        )
                  ) CA (calc1)
WHERE CA.calc1 >= -1 AND CA.calc1 <= 1
AND (3959 * ACOS( CA.calc1 ))  <= @distance
;

Open in new window


One extra cross apply could be used, like this:

SELECT
      str_ZipCode
    , CA2.distance
FROM dbo.lkpZipCodesCA
      CROSS APPLY (SELECT
                        (
                          COS( RADIANS( @lat ) ) 
                        * COS( RADIANS( latitude ) ) 
                        * COS( RADIANS( longitude ) - RADIANS( @lon ) ) 
                        + SIN( RADIANS( @lat ) ) 
                        * SIN( RADIANS( latitude ) ) 
                        )
                  ) CA (calc1)
      CROSS APPLY (SELECT
                        3959 * ACOS( CA.calc1 )
                  ) CA2 (distance)
WHERE CA.calc1 >= -1 AND CA.calc1 <= 1
AND CA2.distance  <= @distance
;

Open in new window


It's worth trying with the extra cross apply but I suspect any difference would be marginal.

It might also be possible that adding indexes to the temp table could assist with filtering the result, however you add the overhead of building any indexes. (note you could now store [calc1] and index it in addition to distance so indexes could assist all filtering predicates)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Can you show you format latitude and longitude?

Since each minute of latitude is ~1mi and each minute of longitude is 1.15mi, then only values within those ranges could be within 20 miles.  Thus, to me, the most obvious way to reduce the rows would be to only consider 25 minutes of latitude and 20 minutes of longitude range.  

That is, generically:

WHERE
    <latitude in table> BETWEEN <lookup_latitude - 25 minutes> AND <lookup_latitude + 25 minutes> AND
    <longitude in table> BETWEEN <lookup_longitude - 20 minutes> AND <lookup_longitude + 20 minutes>

Finally, to fully improve the lookup speed, cluster the dbo.lkpZipCodesCA table on ( latitude, longitude ).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.