question

adam9367 avatar image
adam9367 asked

help indexing a table

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
indexing
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@adam9367 What is the SQL Server version? If you have 2008 then you can use geography column instead of decimal lat and long. Since your ZipCodes table does not have any other indexes except the cluster, it does not really matter, but still, the cluster appears to be pretty wide (up to 75 bytes). How many countries do you have? Do the lat and long values in each record identify the geographical location corresponding to the center of the specific are which belongs to the zip code? I think that dbo.udf_GetMiles could be one of the sources of the problem. It is probably defined as a scalar udf, and so it is called as many times as there are records in your v_oh_FarmList view. If the view is heavy then you might want to consider not using it but opt for the somewhat ligher join of the involved tables instead.
1 Like 1 ·
Having that code is a great start but we will need DDL for all the tables to properly give advice on tuning the proc. ...
0 Likes 0 ·
oh, and for the function(s) and view(s) used too!
0 Likes 0 ·
do you have current benckmark figures to compare any 'improvements' to?
0 Likes 0 ·
still some missing >> oh_Farms / udf_GetMiles / v_oh_FarmList ...? oh, also I put your last comment into the question to make it easier to follow, can you add the above there too?
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
If you're using SSMS for SQL 2008, then getting the actual execution plan may also highlight "missing indexes" that may be of use. CTRL+M to get SSMS to show the actual execution plan the next time the query runs. Of course, you need to measure performance before & after to determine if the index is actually doing its job.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@adam9367 - check out anything from Grant Fritchey - http://www.simple-talk.com/sql/performance/execution-plan-basics/ Check the information box at the top of that page - there is a link to his book there
1 Like 1 ·
@Everyone, thanks for all of the advice. I added a sql script to create the tables, udf's and sp in the original question. I'll start exploring the execution plans. Can anyone point me towards a good resource for learning about execution plans?
0 Likes 0 ·
After a few hours of reading and a few hours of playing with SSMS I now understand how to determine/compare the cost of my query again other queries and determine where I have missing indexes. However, I’m still having trouble finding a work around for using like ‘%’ in my query. I have a web form that lets users filter by specific columns. For example, a user could pick a specific state or leave the field blank and show results from all states. Does anybody have any work around for this type of problem?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Have you tried reviewing the estimated execution plan in SSMS? Work from the top right of the plan, reviewing each piece of work and identify if it can be eased/removed but re-working your TSQL or adding an index etc.. Are there PK-FK relations between all the object referenced?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
There's a heck of a lot there and without full knowledge of the system I'm only going to provide some guesses, at best. First thing that strikes me, instead of creating everything into a temporary table, why don't you use just select straight from the data? You're moving it twice for no apparent reason. Why are you using LIKE statements with the variables? That can (not necessarily will) lead to non-sargeable conditions, meaning, you don't get index use. I also agree with everyone else, really need to see those functions, because that could be killing performance.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

There are some very good suggestions here. I specifically want to second Thomas' suggestion to look at missing indexes and Grant's point about risking non-SARGeable conditions using like.

Also, as Grant said, you may want to avoid creating the temp table and instead move that into a CTE. If you do want to keep the temp table for some reason then you may want to index the temp table. I have normally found indexing temp tables well worth the server time. (Of course, I normally create a temp table for use in multiple follow up operations not just one.)

The missing index should give you a very good place to start in indexing your main source table, but also remember to put some thought into it. If you are using wild cards with your like statements, especially in the beginning of the string, then those will be non-sargeable and probably should not be included in your index. Similarly, if one of your columns `is_active` for instance is binary then that is probably not differentiated enough to be overly useful in the index.

You may also want to look at https://www.sqlservercentral.com/articles/an-informal-look-at-database-performance though I am biased about it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.