Should I always use Recompile with this query?
-
I have just experienced an issue with my website on a particular page where the page just wouldn't load and kept reporting Timeout errors (it was working fine last week and no code has changed). If I took the SQL that the page was running to return a recordset and ran it directly in Query Analyser it returned in less than a second so I was very confused. The page itself takes a number of parameters from a form including the field to sort by, sort order (ASC/DESC) and whether to filter on a string. It returns a paged recordset eg records 50-100 out of 1000. When we were using an SQL 2k backend we built the sql string on the front end up depending on these parameters and then executed it. When we moved to 2005 I re-wrote the code so that its all contained within a proc with no dynamic sql (see below for proc code). So as there are different combinations of clauses,order bys I thought it may be down to an old execution plan hurting performance. I added the with recompile statement to the proc and hey presto the webpages suddenly started working and returning results very quickly. So two questions:
Why was it returning results instantaneously when running the SQL in Query Analyser but taking so long that it timed out when running the same SQL from the web? (this is before I added the recompile statement) Surely the same query plan would have been used for both or am I incorrect in this presumption?
Also should I leave the (with recompile) statement in the procedure to prevent this happening again? It currently performs very well with it in.
The full code for the proc is here:
ALTER PROCEDURE [dbo].[usp_asp_get_candidates]
@SitePk int,
@Name varchar(25) = null,
@OrderBy varchar(15) = 'RegisterDate',
@Direction varchar(4) = 'DESC',
@PageIndex int = 1,
@PageSize int = 10
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Start int,
@Stop int
SET @Start = (@PageIndex - 1) * @PageSize + 1
SET @Stop = @PageIndex*@PageSize
SELECT
Count(c.CandidatePK) as No
FROM
tbl_CANDIDATES as c
LEFT JOIN
tbl_CANDIDATES_CV as cv
ON
c.CandidatePk = cv.CandidatePK
WHERE
c.SiteFK = @SitePK AND
CASE WHEN @name is null THEN 1 WHEN c.Name LIKE @name THEN 1 ELSE 0 END = 1
SELECT
Row, CandidatePK, [Name], Email, RegisterDate,
FROM
(SELECT TOP (@Stop) ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'ASC' THEN registerDate END ASC,
CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'DESC' THEN registerDate END DESC,
CASE WHEN @OrderBy = 'name' AND @Direction = 'ASC' THEN c.Name END ASC,
CASE WHEN @OrderBy = 'name' AND @Direction = 'DESC' THEN c.Name END DESC)
AS Row, c.CandidatePK, c.Name, c.Email,RegisterDate
FROM tbl_CANDIDATES as c
LEFT JOIN
tbl_CANDIDATES_CV as cv
ON
c.CandidatePk = cv.CandidatePK
WHERE
c.SiteFK = @SitePK AND
CASE WHEN @name is null THEN 1 WHEN c.Name LIKE @name THEN 1 ELSE 0 END = 1
GROUP BY
c.CandidatePK, c.Name, c.Email, RegisterDate
 
AS CANDS
WHERE
Row between @Start and @Stop
END
-
I think you've already answered your own question. If the optimizer can generate a plan that kills performance then you need a method to avoid this. You could try forcing the good plan on the query, but since it's a fairly simple query that probably recompiles quickly, I think you've already done the right thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'd recomend removing the recompile and rather split the stored proc into two, one that retrieves all candidates, one that retrieves a single one by name.
The problem is that depending on the value passed for name, two vastly different exec plans can be generated, one that's optimised for a single record (probably) and one that's optimised for many.
If the one that's optimal for single rows executes first and gets put into the plan cache, then it's very likely that any follwoign calls for all rows will perform terribly
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for the responses.
At the moment the current query handles multiple types of name search.
Candidate Name: (could be empty or full name or partial name)
There is an option on the criteria page which if a string is entered for name tells the system whether the string is the start of the name
or just contained within the string and so the param is passed in like
'Rob%' OR '%Rob%'
So I'm not actually searching to return one record by name although only one may be returned.
Its probably this like statement that has caused the bad query plan so should I split the query into 3
-For when no name is supplied at all.
-For when the start of a name is supplied eg Rob%
-For when a search string is supplied eg %Rob%
Also what about the Order By statements in the OVER clause would they be affecting performance by having them in the CASE statement
rather than a query per combination?
If the answer is yes and I am going to remove the With Recompile and then go back to having multiple queries depening on the parameters would it not
be better to revert back to my original way of building up a string on the webserver depending on the criteria selected and then
executing it. This way each combination of criteria would have its own execution plan rather than one plan per X combinations?
Or I could just leave the recompile statement in.
-
I'd suggest split into two. One for when the beginning of the name is specified (because it can use an index if there is one on name) and one for the other conditions.
Like '%' and Like '%d%' will probably be optimised much the same, as stats can't say how many rows in either case, so will probably estimate the number in the table, or based on other conditions.
No idea about the ROW_NUMBER OVER ... but you can try that out yourself. run the query with different order bys in query analyser/management studio and check the execution plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
And, if you do decide to split them, you can still use the original proc as a wrapper around the other two so that you don't have to re-write your code.
I still think, depending on how often this proc is called and how long the recompile takes, simply leaving the recompile in place may be the easier solution. I'm all for finding the optimal path through the code, but optimal can also be expedient, depending on the circumstances.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Agreed on the wrapper proc.
Recompile can cause serious bottlenecks, not just because of the CPU time to do the compile.
While an object is being compiled, an exclusive lock is held on it, preventing anyone else from compiling the stored proc. This can lead to serious blocking if the proc is run by multiple users concurrently.
I had a proc that got called about 2000 times an hour that recompiled 3-5 times on each exec, due to temp table usage mostly. I used to have blocking chains up to 15 long from this proc alone. I rewrote it to remove the necessity to recompile and the average exec time went from 2 sec to 500 ms
Personally I feel that recompile should be the last resort for solving a problem. If recompile is necessary, then use the statement level recom[pile that SQL 2005 introduced, not the object level.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for all your responses.
It seems splitting up the current proc into numerous different queries defeats my original purpose of writing one generic proc in the first place and still no-one has answered the question of whether it wouldnt be better to just revert back to the old way of building the SQL up on the client then each combination of parameters would have its own optimized query plan to use?
I did some work at my last company on a complicated search proc within a batch job that used many different parameters in a similar fashion in different combinations. The job ran at night to clean up callcenter data and find missing data etc. When run at night the time it took to return results increased exponentially as time went on. Eg within the first 30 mins of running it would return results within a second, whereas after an hour or so it would take minutes. When I added the with recompile statement in it resolved this problem. In that situation though the only user calling the proc was this batch job so there was no chance of concurrent calls and locking.
I can see from your comments that recompiling a frequently used proc could cause locking issues which although this proc is only used by administrators quite infrequently I would like to code it in the best way possible. Also I didn't know about this other 2005 recompile statement. Would this be useful in this situation?
Thanks for your advice / comments.
-
No arguments there. We had a proc that was so poorly written that it took about 3 minutes to recompile. It too was called thousands of times a minute and the data was so volatile that we were getting a recompile every 12 to 18 minutes which would then shut down the system for three minutes. I am paranoid about recompiles. Just, sometimes, circumstances go the other way too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I wouldn't recommend passing executable strings to the database just because each execution gets it's own plan. You want the system to reuse plans. Most of the time, in most circumstances, one plan will work for most executions of a given query. You could go through all the trouble of building your queries such that you're using sp_executesql and passing in paramaterized queries such that you get query reuse, but then you've just spent a ton of time rebuilding something that you get for free inside SQL Server anyway. Plus the fact, if the query has problems, it has problems whether passed directly from code or called through a stored procedure. I've long been an advocate of letting each system do what it does best. Let the database handle data access and do that access in the way that makes the most sense to the database, not the code, and let the code do all the manipulations that code does best and keep all that stuff away from the database.
My 1.5 cents.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
It seems splitting up the current proc into numerous different queries defeats my original purpose of writing one generic proc in the first place and still no-one has answered the question of whether it wouldnt be better to just revert back to the old way of building the SQL up on the client then each combination of parameters would have its own optimized query plan to use?
I don't think you need numerous different queries, just the two I mentioned earlier. One for when the first portion of the name is passed, once for other conditions.
If you still have problems, then consider adding OPTION(RECOMPILE) to the very end of the sql statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply