question

j-welch avatar image
j-welch asked

SQL 2005 Stored Procedure in Workgroup Edition

I have a SQL Server 2005 stored procedure that: 1) Takes 5 parameters for searching - all have default values of null since the user can pass in any combination of parameters 2) Creates a temp table variable for the initial results - this pivots the data into columns for searching. 3) Has a couple if else statements for parsing out address entry This works in less than 1 second when exectued off of SQL Server 2005 Enterprise Edition, however in SQL 2005 Workgroup edition, it takes 35 seconds when executed via Mangement Studio. If I pull the query out directly and run it (with the same parameters on the Workgroup edition), it runs in 1 second or less. Can someone explain why the stored procedure is executing so slowly in 2005 Workgroup and how I can tune it? Many thanks, Jayme
sql-server-2005pivottabletable-variable
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
First thought, you have different default ANSI settings on the one machine, and connections can change ANSI settings. The different ANSI settings result in a different execution plan. You can check this by looking at the exec plan on the different versions of SQL Server. Just pull it directly from cache using the Dynamic Management Views (sys.dm_exec_query_plan & sys.dm_exec_query_stats). You may even see two different plans in the Workgroup version since you've run it two ways. Otherwise, it might be something related to statistics or parameter sniffing. We are dealing with two different databases after all.
4 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.

j-welch avatar image j-welch commented ·
Can you explain why running the query directly runs ok? I am not following how the ANSI settings have an effect? I did not see any data for the ANSI settings in those queries - it just returns the execution plan and what is taking the longest (like what Query Execution Plan provides). Any extra information you can provide would be greatly appreciated. I also ran into this before with how SQL executes procedures with null parameter values for a default, but I have forgotten the fix.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
ANSI connection settings are set by default on each server, and they can be different. You can see the ANSI settings used by an execution plan in the SELECT properties (for a SELECT statement, same for INSERT, DELETE, etc.). You have to look at the execution plans. I'm confused by "taking the longest (like what query execution plan provides)" Execution plans have estimated costs, but they don't measure execution times, so I'm not sure what you're talking about there. Have you looked at the exec plans for the two servers, specifically for the one that is running slow and for one that is running fast? Are they different? If it's a stored procedure versus a query, my first two thoughts are ANSI connection settings and parameter sniffing. But without looking at both execution plans, I can't tell you what's what.
0 Likes 0 ·
j-welch avatar image j-welch commented ·
Grant, thank you for your replies. I am not seeing the ANSI settings in the execution plan: To clarify, when I run the query directly in Mgt Studio in the Workgroup Edition, it executes in 1 second or less. When I save it as a stored procedure, it takes 32 seconds with the exact same parameters. The following is the query: --declare variables and set initial values declare @search varchar(1000) = null declare @start datetime = null declare @end datetime = null declare @formtypeid int = null set @search='2' if @Search = '' begin set @Search=null end -- Insert statements for procedure here declare @responseid int declare @count int set @count=0 --pivot table of answers DECLARE @Results TABLE ( ResponseID int, DateStarted datetime, DateCompleted datetime, [Property Address] varchar(max), [Property City] varchar(max), [Property State] varchar(max), [Property zip] varchar(max), [Policy Number] varchar(max), [Insured Name] varchar(max), [Inspection Type] varchar(30), Buildings int, FormTypeID int ) INSERT INTO @Results (Responseid, DateStarted, DateCompleted, [Property Address], [Property City], [Property State], [Property Zip], [Policy Number], [Insured Name], [Inspection Type], Buildings, FormTypeID) select distinct ResponseID, DateStarted, DateCompleted, [Property Address], [Property City], [Property State], [Property Zip], [Policy Number], [Insured Name], [Inspection Type], count(instanceid) as Buildings, FormTypeID from ( Select ResponseID, datestarted, Question, Answer, DateCompleted, InstanceID, FormTypeID from v_form_answers where (datestarted >= @start or @start is null) and (datestarted <=@end or @end is null) and (formtypeid=@formtypeid or @formtypeid is null) group by DateStarted, ResponseID, Question, Answer, DateCompleted, InstanceID, FormTypeID) o Pivot (Max(Answer) for Question in ([Property Address], [Property City], [Property State], [Property Zip], [Policy Number], [Insured Name], [Inspection Type]))p group by ResponseID, DateStarted, DateCompleted, [Property Address], [Property City], [Property State], [Property Zip], [Policy Number], [Insured Name], [Inspection Type], FormTypeID --check if search phrase was entered if @Search is not null begin --response id search begin try set @responseid=cast(@search as int) end try begin catch --do nothing end catch select * from @Results where responseid=@responseid or [policy number] = @responseid set @count=@@rowcount print @count --address, policy number or name search if @count = 0 begin if @search like '%,%' begin --parse out the unique address pieces declare @city varchar(max) declare @address varchar(max) declare @state varchar(max) declare @lessaddress varchar(max) declare @lesscity varchar(max) SELECT @address=SubString(@search, 1, CharIndex(',' , @search, 1 ) - 1) Select @lessaddress = replace(@search, @address + ', ', '') if @lessaddress like '%,%' begin SELECT @city=SubString(@lessaddress, 1, CharIndex(',' , @lessaddress, 1 ) - 1) select @lesscity=replace(@lessaddress, @city + ', ', '') if @lesscity like '%,%' begin select @state = SubString(@lesscity, 1, CharIndex(',' , @lesscity, 1 ) - 1) end else begin set @state=@lesscity end end else begin set @city=@lesscity end end select * from @Results where [property address] like '%' + @address + '%' and ([property city] = @city or @city is null) and ([property state] = @state or @state is null) end else begin select * from @Results where [property address] like '%' + @search + '%' or [property city] like '%' + @search + '%' or [property state] like '%' + @search + '%' or [property zip] like '%' + @search + '%' or [insured name] like '%' + @search + '%' end end
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@J-welch It's in the properties under the SELECT operator, down near the bottom. I can't convert the text to a .sqlplan file. I tried. Anyway, it sounds more like bad parameter sniffing than ANSI settings now anyway. But, because you have a completely dynamic query, it could be anything. You're especially going to hit snags with recompiles & reuse because of the dynamic nature of this query.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Are you saying that the same query runs slower on the Workgroup instance than it does against the Enterprise instance? Are these two servers on similar hardware? With the same configuration? And the same data sets and schema? If so, then as @Grant Fritchey says, without the full execution plan (not the estimated, the actual), then it's difficult to say. If not, then depending on the differences, that could be your problem right there. Much less memory in a workgroup server could cause data to be paged to disk rather than manipulated in memory; slower CPUs will affect query times too; slower disk hardware, fewer CPUs, slower (or more convoluted) network to return results... lots of potential causes. ....Sorry - it's been a long & difficult day, and I'm feeling a little befuddled.
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.