I have inherited a long complex stored procedure. The stored proc works OK but is very slow. I intend to refactor this and possible move some of the functions to CLR code. However in the meantime could anyone recommend any quick improvements that would improve the speed of execution. When i say slow it can take unto 30 second to complete a search. The database is currently hosted in Sql Azure on plan S02.
The stored procedure search records based on 3 main parameters , there are a lot more but the minimum is the following 3.
Doing the distance search calculation is itself relatively simple (and working) . The complexity comes from the addresses it needs to compare against are in multiple tables. The results then need to be complied into a result set which is then used ( by the client ) as paging data. The postcode Latitude and Longitude are pre-poulated in a separate table.
The script is quite long but mainly due to the number of parameters, but again most of these are optional.
Any tips would be gratefully appreciated.