question

Hello, I have a stored procedure I use to search one of my tables. I'm not sure how to index my table or rewrite my sp to get better performance. Any advice would be appreciated. Below is a link to a sql packager script for creating all of the related tables/functions.

CREATE PROCEDURE [dbo].[oh_SearchAdv]
@HerdType				varchar(25),
@Country				varchar(25),
@MinHerdSize			INT,
@MaxHerdSize			INT,
@FarmState				varchar(25),
@ColumnName				varchar(25),
@Sort					varchar(5),
@OffersFinancing		varchar(25),
@Zip					VARCHAR(25),
@Radius					INT,
@doZipSearch			BIT,
	
--used for paging
@PageIndex				int,
@NumRows				int,
@RecordCount			int OUTPUT
AS
BEGIN
DECLARE	@TempFarmIDs TABLE (FarmID INT)
DECLARE @iStartLat			DECIMAL(18,10)
DECLARE @iStartLong			DECIMAL(18,10)
SELECT @iStartLat = (SELECT Latitude FROM oh_ZipCodes WHERE ZipCode = @Zip)
SELECT @iStartLong = (SELECT Longitude FROM oh_ZipCodes WHERE ZipCode = @Zip)
IF @doZipSearch = 1
	BEGIN
		INSERT INTO @TempFarmIDs EXEC oh_GetFarmsByZipRadius @Zip, @Radius
	END
	
ELSE
	BEGIN
		INSERT INTO @TempFarmIDs SELECT FarmID FROM oh_Farms
	END
SELECT	vf.*, dbo.fnGetMiles(@iStartLat, vf.Latitude, @iStartLong, vf.Longitude) AS 'Distance'
INTO	#TempFarms FROM	v_oh_FarmList vf
WHERE
    
		is_Active = 1
		AND is_UnderReview = 0
		AND is_EmailVerified = 1
		--User Options
  
		AND FarmState LIKE @FarmState
		AND FarmCountry LIKE @Country
		AND HerdType LIKE @HerdType
		AND (HerdSize BETWEEN @MinHerdSize AND @MaxHerdSize)
		AND is_OffersFinancing LIKE @OffersFinancing
 
		AND FarmID IN (SELECT FarmID FROM @TempFarmIDs)
		
SELECT @RecordCount=(SELECT COUNT(*) FROM #TempFarms)
	Declare @startRowIndex INT;
	set @startRowIndex = (@PageIndex * @NumRows) + 1;
	With FarmRecords as (
							SELECT 
							ROW_NUMBER() 
							OVER (
								
								 ORDER BY 
									CASE	WHEN @ColumnName='Farm' AND @Sort='ASC' THEN FarmName
									END ASC,
									CASE	WHEN @ColumnName='Farm' AND @Sort='DESC' THEN FarmName
									END DESC,
									CASE	WHEN @ColumnName='Owner' AND @Sort='ASC' THEN FarmOwners
									END ASC,
									CASE	WHEN @ColumnName='Owner' AND @Sort='DESC' THEN FarmOwners
									END DESC,
									CASE	WHEN @ColumnName='HerdType' AND @Sort='ASC' THEN HerdType
									END ASC,
									CASE	WHEN @ColumnName='HerdType' AND @Sort='DESC' THEN HerdType
									END DESC,
									CASE	WHEN @ColumnName='HerdSize' AND @Sort='ASC' THEN cast(HerdSize as int)
									END ASC,
									CASE	WHEN @ColumnName='HerdSize' AND @Sort='DESC' THEN cast(HerdSize as int)
									END DESC,
									CASE	WHEN @ColumnName='State' AND @Sort='ASC' THEN FarmState
									END ASC,
									CASE	WHEN @ColumnName='State' AND @Sort='DESC' THEN FarmState
									END DESC,
									CASE	WHEN @ColumnName='Phone' AND @Sort='ASC' THEN FarmPhone1
									END ASC,
									CASE	WHEN @ColumnName='Phone' AND @Sort='DESC' THEN FarmPhone1
									END DESC,
									CASE	WHEN @ColumnName='Distance' AND @Sort='ASC' THEN Distance
									END ASC,
									CASE	WHEN @ColumnName='Distance' AND @Sort='DESC' THEN Distance
									END DESC,
									CASE	WHEN @ColumnName='Updated' AND @Sort='ASC' THEN Cast(LastUpdated as DateTime)
									END DESC,
									CASE	WHEN @ColumnName='Updated' AND @Sort='DESC' THEN Cast(LastUpdated as DateTime)
									END ASC
									
									) 
							as Row,
							tf.*
							
						FROM #TempFarms tf
						)
	
	Select fr.*
	FROM FarmRecords fr
	WHERE Row between 
	@startRowIndex and @StartRowIndex+@NumRows-1
	
	
	DROP TABLE #TempFarms
END