ProcessAddress times out every time - result set size, time of day seem irrelevant

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  March 13, 2013 at 9:16 pm

  #275166

  I am using the ProcessAddress geocoding function found here[/url] on CodePlex, but no matter what, it always times out on me. Is this just an issue with Yahoo's geocoding, or am I doing something wrong?

  Here's my stored procedure:

  BEGIN

  UPDATE [Account_Master_2013]

  SET [County] = pa.County,

  [Long] = pa.Longitude,

  [Lat] = pa.Latitude

  FROM [Account_Master_2013]

  CROSS APPLY ProcessAddress([Account_Master_2013].[Address],

  [Account_Master_2013].CITY + ', ' + [Account_Master_2013].[STATE], null) pa

  WHERE [Account_Master_2013].[state]=@StateRegion

  AND [Account_Master_2013].County IS NULL

  AND ACM IN (SELECT TOP (@Top_N_Records) ACM FROM [Account_Master_2013] WHERE lat=0 OR lat IS NULL);

  END

  At first I tried limiting the records processed to a really small set, but that didn't seem to matter. Nor did the time of day that I ran the stored procedure (because I was wondering if the server were just busy). The code compiles fine, and runs, but times out after about two minutes, even if I set the @Top_N_Records = 5 .

  What am I doing wrong? Or should I use something else to do geocoding? I am pretty sure the syntax for the update is right - Vedran Kesegic sorted that part out a long time ago... now it just plain fails though.

  Where do I start to try to fix this? Thanks!

  Pieter