September 11, 2008 at 10:01 am
So, I have a bit of code as a stored procedure. It takes 2:19 to run. When I pull it out, declare and set the variables, and run it as a straight query, it takes less than a second! Can anyone tell me why? The highlights are: it declares a table variable, the select uses a full text comparison to insert records into it, then it's joined back to the originating tables to grab more data. I use 'with nolock' to increase performance. Thoughts?
September 11, 2008 at 10:07 am
Search parameter sniffing on this site.
Also you might want to test if using a temp table instead of table variable helps here.
September 11, 2008 at 12:05 pm
Well, that's a possibility, but I tried executing the stored proc 'WITH RECOMPILE', and it's no faster. In other words, compensating for the execution plan not being optimized didn't work. What else could cause such a discrepancy?
September 11, 2008 at 12:13 pm
Blocking, locks, contention on ressources, data out of cache, too much stuff going on, other programs fighting for ressource, slow network... just from the top of my head... Really hard to tell when you're not around the server.
September 11, 2008 at 1:25 pm
I'm not sure why one run would be better than the other, but - a table variable doesn't accumulate statistics like a regular table does. So - the joining back process is likely using an assumed number of rows on that table (10 I think), which more than likely means the exec plan is crap. No matter how many times it runs, it's using the same bogus assumptions, and coming to a wrong conclusion.
Simply switching that to a "true" table, even if it's a temp table, might give you a substantially better exec plan.
That being said - if you get a chance, perhaps you could post the exec plans from both execution scenarios. I bet you will find some rather substantial differences.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2008 at 1:16 am
(nolock) doesn't help with performance. You are basically doing a dirty read of the table and allowing other process to write to that table. It does nothing for performance except to help with blocking or deadlocks.
Take a look at your execution plans for your straight TSQL and when it is in the procedure.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
September 12, 2008 at 1:47 am
Matt Miller (9/11/2008)
a table variable doesn't accumulate statistics like a regular table does. So - the joining back process is likely using an assumed number of rows on that table (10 I think)
One row.
Any chance you can post either the proc or the execution plans that it has as a proc and as a straight query? (save the plan as a .sqlplan file, zip and attach to your post)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2008 at 6:04 am
@ninja: yes, of course, those are all issues. However, the argument goes: it's a development database - little or no load, and I can run first the query then the sp at any time and get consistent results. This isn't a tuning issue.
@matt-2: I did convert both to use a temp table to manage the join-back, but I actually ended up getting slightly worse performance. But, it was consistent - it was worse for both methods.
@Oberion: arguably eliminating deadlocks and blocking is a performance boost. It's like saying eliminating road construction doesn't boost travel times. And, in my case, I don't really care if someone's writing.
@Gail: Yes, I can post the query, but would it be useful? In can see the execution plan of the query, but how do I the the stored proc's plan? If I request it, doesn't it just return with the plan of the "create" statement?
September 12, 2008 at 6:07 am
Oh, and in case anyone is curious, the reason I'm running it as an sp and not a query is because I need to return two resultsets, and I can't "catch" both from a query on the other side. (but my app can if it's an sp)
September 12, 2008 at 6:27 am
Paste ur Strored Proc Script ...then we'll see :):)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 12, 2008 at 7:24 am
Oblio Leitch (9/12/2008)
@Gail: Yes, I can post the query, but would it be useful?
It might be useful. There are certain constructs that can cause parameter sniffing or other similar problems that someone might recognise
In can see the execution plan of the query, but how do I the the stored proc's plan? If I request it, doesn't it just return with the plan of the "create" statement?
Same way you get the query's plan. Switch the show execution plan option on, then run the procedure.
Create procedure, like all other DDL operations doesn't have an execution plan.
Oh, and in case anyone is curious, the reason I'm running it as an sp and not a query is because I need to return two resultsets, and I can't "catch" both from a query on the other side. (but my app can if it's an sp)
Not curious at all. Procedures are recommended over ah-hoc SQL for several reasons (plan cache reuse, security)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2008 at 7:46 am
I'm curious, are running the SP then the Ad-Hoc? Does the SP run faster the second time? What happens if you run DBCC DROPCLEANBUFFERS before running each query?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2008 at 5:10 am
This version uses temp tables instead of a table var (that's still there if you search/replace). In case you're wondering, I took out the code that does an audit of the results returned - the reason I'm storing in a table before returning.
At this point it's a curiosity - I've split the sp into two queries in the application, and it seems to be fine.
IF EXISTS(
SELECT * FROM [sysobjects]
WHERE id=OBJECT_ID(N'[dbo].[cN_searchCases]')
AND OBJECTPROPERTY(id,N'IsProcedure')=1
) DROP PROC [dbo].[cN_searchCases]
GO
CREATE PROC [dbo].[cN_searchCases]
@searchQry_inp VARCHAR(100),
@family_inp INT,
@startDate_inp SMALLDATETIME =NULL,
@endDate_inp SMALLDATETIME =NULL,
@uID_inp INT
AS
SET NOCOUNT ON
--DECLARE @idTbl TABLE([cID] INT,[enteredFN] VARCHAR(30),[enteredLN] VARCHAR(30))
CREATE TABLE #idTbl ([cID] INT,[enteredFN] VARCHAR(30),[enteredLN] VARCHAR(30))
DECLARE @viewID INT,
@fetchCount INT,
@searchQry VARCHAR(100),
@family INT,
@startDate SMALLDATETIME,
@endDate SMALLDATETIME,
@uID INT
SET @fetchCount=0
SET @searchQry=@searchQry_inp
SET @family=@family_inp
SET @startDate=@startDate_inp
SET @endDate=@endDate_inp
SET @uID=@uID_inp
/* First, get the IDs of all casenote matches */
INSERT #idTbl([cID],[enteredFN],[enteredLN])
SELECT [src].[rID]
,u.[userfn],u.[userln]
FROM [casenotes_tmp] src
LEFT JOIN [srs_users]"u" WITH (NOLOCK)
ON u.[userID]=[src].[enteredByID]
WHERE
-- FULLTEXT:
CONTAINS(src.[notes], @searchQry)
-- limit by family number
AND CASE
WHEN @family IS NULL THEN 1
WHEN @family IS NOT NULL AND [src].[famly]=@family THEN 1
ELSE 0
END =1
AND CASE
WHEN @startDate IS NULL THEN 1
WHEN @endDate IS NOT NULL AND ([src].[date] BETWEEN @startDate AND @endDate) THEN 1
WHEN @endDate IS NULL AND ([src].[date] >= @startDate) THEN 1
ELSE 0
END =1
OPTION (RECOMPILE)
/*debug*/ PRINT 'cases matching: '+CAST(@@ROWCOUNT AS VARCHAR(3))
SET @fetchCount=@fetchCount+@@ROWCOUNT
INSERT #idTbl([cID],[enteredFN],[enteredLN])
SELECT a.[casenoteID],u.[userfn],u.[userln]
FROM [casenote_ammended] a
JOIN [casenotes_tmp] c
ON a.[casenoteID] = c.[rID]
LEFT JOIN [srs_users]"u" WITH (NOLOCK)
ON u.[userID]=a.[enteredByID]
WHERE
-- FULLTEXT:
CONTAINS([ammendment], @searchQry)
-- limit by family number
AND CASE
WHEN @family IS NULL THEN 1
WHEN @family IS NOT NULL AND c.[famly]=@family THEN 1
ELSE 0
END =1
AND CASE
WHEN @startDate IS NULL THEN 1
WHEN @endDate IS NOT NULL AND (a.[date] BETWEEN @startDate AND @endDate) THEN 1
WHEN @endDate IS NULL AND (a.[date] >= @startDate) THEN 1
ELSE 0
END =1
OPTION (RECOMPILE)
/*debug*/ PRINT 'ammendments matching: '+CAST(@@ROWCOUNT AS VARCHAR(3))
SET @fetchCount=@fetchCount+@@ROWCOUNT
/*IF @fetchCount>0
BEGIN
{insert results in audit}
END*/
SET NOCOUNT OFF
SELECT [case].*,
[src].[enteredFN],-- who entered the casenote
[src].[enteredLN],
u.[userfn],-- who made contact
u.[userln],
(SELECT COUNT(*)
FROM [casenote_details]"d" WITH (NOLOCK)
WHERE [d].[casenoteID]=[case].[rID] AND [d].[fieldID]=1)"f2f",
(SELECT COUNT(*) FROM [casenote_ammended]"a" WITH (NOLOCK) WHERE [case].[rID]=[a].[casenoteID])"ammended"
,LEFT(p.[l],LEN(p.[l])-1)"present"
,LEFT(c.[l],LEN(c.[l])-1)"contactType"
,LEFT(l.[l],LEN(l.[l])-1)"location"
,LEFT(t.[l],LEN(t.[l])-1)"type"
,LEFT(m.[l],LEN(m.[l])-1)"members"
FROM [casenotes_tmp]"case"
JOIN #idTbl"src"
ON [case].[rID]=[src].[cID]
LEFT JOIN [srs_users] u WITH (NOLOCK)
ON u.[userid]=[case].[cwkrID]
CROSS APPLY
(SELECT COALESCE([f].[label]+',','') AS [text()]
FROM [casenote_details]"d" WITH (NOLOCK)
JOIN [casenote_fields]"f" WITH (NOLOCK)
ON [d].[fieldID]=[f].[rID]
WHERE [d].[casenoteID]=[case].[rID] AND [f].[type]='present'
ORDER BY [f].[posOrdinal]
FOR XML PATH ('')
)"p"([l])
CROSS APPLY
(SELECT [f].[label] + ',' AS [text()]
FROM [casenote_details]"d" WITH (NOLOCK)
JOIN [casenote_fields]"f" WITH (NOLOCK)
ON [d].[fieldID]=[f].[rID]
WHERE [d].[casenoteID]=[case].[rID] AND [f].[type]='contactType'
ORDER BY [f].[posOrdinal]
FOR XML PATH ('')
)"c"([l])
CROSS APPLY
(SELECT COALESCE([f].[label]+',','') AS [text()]
FROM [casenote_details]"d" WITH (NOLOCK)
JOIN [casenote_fields]"f" WITH (NOLOCK)
ON [d].[fieldID]=[f].[rID]
WHERE [d].[casenoteID]=[case].[rID] AND [f].[type]='location'
ORDER BY [f].[posOrdinal]
FOR XML PATH ('')
)"l"([l])
CROSS APPLY
(SELECT COALESCE([f].[label]+',','') AS [text()]
FROM [casenote_details]"d" WITH (NOLOCK)
JOIN [casenote_fields]"f" WITH (NOLOCK)
ON [d].[fieldID]=[f].[rID]
WHERE [d].[casenoteID]=[case].[rID] AND [f].[type]='type'
ORDER BY [f].[posOrdinal]
FOR XML PATH ('')
)"t"([l])
CROSS APPLY
(SELECT COALESCE(u.[cc_fn]+' '+u.[cc_ln]+' ('+CAST([c].[clientID]AS VARCHAR)+'),','') AS [text()]
FROM [casenote_clients]"c" WITH (NOLOCK)
JOIN [custcases]"u" WITH (NOLOCK)
ON [case].[famly]=u.[cc_famly] AND [c].[clientID]=u.[cc_child]
WHERE [c].[casenoteID]=[case].[rID]
FOR XML PATH ('')
)"m"([l])
ORDER BY [case].[date] DESC
/* find any amendments */
SELECT [a].*
,u.[userfn]"enteredFN",u.[userln]"enteredLN"
FROM [casenote_ammended]"a"
JOIN #idTbl"src"
ON [src].[cID]=[a].[casenoteID]
LEFT JOIN [srs_users]"u" WITH (NOLOCK)
ON u.[userID]=[a].[enteredByID]
September 17, 2008 at 9:07 pm
The problem is the Cross-Apply's... they resolve to a single row in the execution plan. Query may run ok when running from QA because of cache and stats... likely won't run worth a hoot as an sp. Embedding SQL code in the App is probably not the best way to do things even if they temporarily seem the easiest. Best thing to do would to be to work on finding a better way than using CrossApply because it's nothing more than correlated sub-query which is a form of RBAR. Would probably be faster to use a cursor, instead, but there's very likely a proper set based solution to be had if you look for it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2008 at 5:06 am
Ok. I coulda sworn I've put cross applies in other sps. It seems like the best way to go. I'm trying to generate a denormalized column result (specifically a string) for each row. Still, though, why would the sp work so much more poorly than a straight query for this?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply