[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

SP is slow

Is there any way to make this store procedure more efficient?

ALTER PROCEDURE [dbo].[ResultCount](@ZipCode nvarchar(12))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--DECLARE
	
		--@ZipCode nvarchar(12)
	
	select (
	SELECT count(id) as a FROM 
	tblMaster WHERE ZIP IN (SELECT h.zip FROM zip g JOIN zip h on g.zip <> h.zip AND 
	g.zip = @ZipCode AND h.zip <> @ZipCode WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 
	1609.344))
	)
	+
	(
	SELECT count(id) as b FROM 
	tblMaster WHERE ZIP = @ZipCode
	)
	as ResultCount
END

Open in new window

0
poweraddict
Asked:
poweraddict
1 Solution
 
Anthony PerkinsCommented:
For the record, this looks like a follow up to the thread here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27835938.html
0
 
jmcmunnCommented:
I assume you have checked the obvious, like making sure indexes are correct on the tables etc...nothing looks particularly inefficient in the queries off hand.  How large is the table?
0
 
poweraddictAuthor Commented:
100k rows zip and id are indexed
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
jmcmunnCommented:
It's is almost certainly the call to the STDistance then.

Here's one discussion on speeding up the geospacial data...
http://stackoverflow.com/questions/6461496/spatial-index-is-slow-when-trying-to-find-all-the-points-within-a-range-of-a-geo

I would definitely look into that aspect of it.  have you done any profiling on the server?
0
 
poweraddictAuthor Commented:
Well I just notice, the code runs in under 1 second when not in the stored procedure.

If I take it and run it as a seperate query and hardcode the zip codes its very fast. If I run the exact same code in the stored proc it takes 5-6 seconds.

Thats strange.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the procedure is slow, the sql fast, it may be this issue:
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
0
 
poweraddictAuthor Commented:
I tried the parameter sniffing.

The execution plan on the stored procedure shows a lot of parallelism; the adhock code doesn't. I wonder if this is the issue.
0
 
poweraddictAuthor Commented:
That was it, I added OPTION (MAXDOP 1) and it runs fast.
0
 
poweraddictAuthor Commented:
Parallelism
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now