Stored Procedure much slower than Ad Hoc Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure much slower than Ad Hoc Query

Author

Topic

jholovacs
Posting Yak Master

163 Posts

Posted - 2007-05-24 : 10:29:31

I have a stored proc:


set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[proc_address_select_by_trans2]
	@ven_id				INT,
	@trans_id		VARCHAR(15),
WITH RECOMPILE
AS
BEGIN
SELECT 
	s.psn, 
	s.tenant_id, 
	first_name, 
	last_name, 
	addr_house_num,
	addr_street,
	addr_supp1,
	addr_zip,
	fulfill.dbo.func_get_complan_element_name(p.complan_element_id) as serving_element,
	vsa.xfm_id as trans,
	CASE COALESCE(campaign_status_cd, '')
					WHEN 'M'  THEN 'Y'
					ELSE
					''
					END
					as gauge, 
	c.customer_id, 
	s.address_id
FROM 
	links.dbo.address s 
	LEFT OUTER JOIN prospect.dbo.prospect p
		ON s.address_id = p.address_id
	LEFT OUTER JOIN scheme.dbo.vw_address_xfm_ids vsa
		ON s.address_id = vsa.address_id
	LEFT OUTER JOIN customer.dbo.customer c
		ON s.address_id = c.address_id
		AND  isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
WHERE 	
	vsa.offering_id = @ven_id
	AND vsa.xfm_id = @trans_id
ORDER BY 
	addr_street, addr_house_num, addr_supp1
END

that will normally take 5-15 minutes to return a result set. The same query, run like so:


declare @ven_id int
declare @trans_id varchar(15)
set @ven_id = <Some ID Number>
set @trans_id = <Some ID VarChar>
SELECT 
s.psn, 
s.tenant_id, 
first_name, 
last_name, 
addr_house_num,
addr_street,
addr_supp1,
addr_zip,
fulfill.dbo.func_get_complan_element_name(p.complan_element_id) as serving_element,
vsa.xfm_id as trans,
CASE COALESCE(campaign_status_cd, '')
WHEN 'M' THEN 'Y'
ELSE
''
END
as gauge, 
c.customer_id, 
s.address_id
FROM 
links.dbo.address s 
LEFT OUTER JOIN prospect.dbo.prospect p
ON s.address_id = p.address_id
LEFT OUTER JOIN scheme.dbo.vw_address_xfm_ids vsa
ON s.address_id = vsa.address_id
LEFT OUTER JOIN customer.dbo.customer c
ON s.address_id = c.address_id
AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
WHERE 
vsa.offering_id = @ven_id
AND vsa.xfm_id = @trans_id
ORDER BY 
addr_street, addr_house_num, addr_supp1

This query will return the same results in under 20 seconds.

The data execution plans are also radically different:

Ad Hoc Query DEP:


|--Sort(ORDER BY:([s].[addr_street] ASC, [s].[addr_house_num] ASC, [s].[addr_supp1] ASC))
|--Compute Scalar(DEFINE:([Expr1017]=[fulfill].[dbo].[func_get_complan_element_name]([p].[complan_element_id]), [Expr1018]=If (If ([p].[campaign_status_cd]<>NULL) then [p].[campaign_status_cd] else ''='M') then 'Y' else ''))
|--Hash Match(Right Outer Join, HASH:([c].[address_id])=([s].[address_id]), RESIDUAL:([c].[address_id]=[s].[address_id]))
|--Clustered Index Scan(OBJECT:([customer].[dbo].[customer].[PK_customer] AS [c]), WHERE:((isnull([c].[cust_status_cd], ' ')<>'W' AND isnull([c].[cust_status_cd], ' ')<>'I') AND isnull([c].[cust_status_cd], ' ')<>'T'))
|--Hash Match(Left Outer Join, HASH:([s].[address_id])=([p].[address_id]), RESIDUAL:([s].[address_id]=[p].[address_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([address].[address_id]) WITH PREFETCH)
| |--Hash Match(Aggregate, HASH:([address].[address_id]), RESIDUAL:([address].[address_id]=[address].[address_id]) DEFINE:([connection].[ven_id_element_id]=ANY([connection].[ven_id_element_id])))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([scheme_address_link].[address_id]) WITH PREFETCH)
| | |--Filter(WHERE:([scheme].[offering_id]=[@ven_id]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1011]), OBJECT:([scheme].[dbo].[scheme]) WITH PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([scheme_address_link].[scheme_id]) WITH PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([layout_scheme].[scheme_id]) WITH PREFETCH)
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1009]), OBJECT:([scheme].[dbo].[layout_scheme]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([layout_scheme].[layout_id]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([connection_runlevel_layout_scheme].[runlevel_layout_scheme_id]) WITH PREFETCH)
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([connection].[connection_id]) WITH PREFETCH)
| | | | | | | |--Index Scan(OBJECT:([scheme].[dbo].[connection].[IX_type_ven_id_element]), WHERE:([connection].[ven_id_element_id]=[@trans_id]))
| | | | | | | |--Clustered Index Seek(OBJECT:([scheme].[dbo].[connection_runlevel_layout_scheme].[PK_connection_runlevel_layout_scheme]), SEEK:([connection_runlevel_layout_scheme].[connection_id]=[connection].[connection_id]) ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT:([scheme].[dbo].[layout_scheme].[PK_layout_scheme]), SEEK:([layout_scheme].[layout_scheme_id]=[connection_runlevel_layout_scheme].[runlevel_layout_scheme_id]) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT:([scheme].[dbo].[layout_scheme].[ix_layout_id]), SEEK:([layout_scheme].[layout_id]=[layout_scheme].[layout_id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([scheme].[dbo].[scheme_address_link].[PK_scheme_address_link]), SEEK:([scheme_address_link].[scheme_id]=[layout_scheme].[scheme_id]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([scheme].[dbo].[scheme].[PK_scheme]), SEEK:([scheme].[scheme_id]=[scheme_address_link].[scheme_id]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([links].[dbo].[address].[PK_address]), SEEK:([address].[address_id]=[scheme_address_link].[address_id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([links].[dbo].[address].[PK_address] AS [s]), SEEK:([s].[address_id]=[address].[address_id]) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([prospect].[dbo].[prospect].[PK_prospect] AS [p]))

Stored Procedure Query DEP:


|--Compute Scalar(DEFINE:([Expr1017]=[fulfill].[dbo].[func_get_complan_element_name]([p].[complan_element_id]), [Expr1018]=If (If ([p].[campaign_status_cd]<>NULL) then [p].[campaign_status_cd] else ''='M') then 'Y' else ''))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[address_id]) WITH PREFETCH)
|--Sort(ORDER BY:([s].[addr_street] ASC, [s].[addr_house_num] ASC, [s].[addr_supp1] ASC))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([prospect].[dbo].[prospect] AS [p]) WITH PREFETCH)
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[address_id]) WITH PREFETCH)
| |--Filter(WHERE:([scheme].[offering_id]=[@ven_id] AND [connection].[ven_id_element_id]=[@trans_id]))
| | |--Merge Join(Left Outer Join, MERGE:([s].[address_id])=([address].[address_id]), RESIDUAL:([s].[address_id]=[address].[address_id]))
| | |--Clustered Index Scan(OBJECT:([links].[dbo].[address].[PK_address] AS [s]), ORDERED FORWARD)
| | |--Sort(DISTINCT ORDER BY:([address].[address_id] ASC, [connection].[ven_id_element_id] ASC, [scheme].[offering_id] ASC))
| | |--Hash Match(Inner Join, HASH:([connection].[connection_id])=([connection_runlevel_layout_scheme].[connection_id]), RESIDUAL:([connection].[connection_id]=[connection_runlevel_layout_scheme].[connection_id]))
| | |--Index Scan(OBJECT:([scheme].[dbo].[connection].[IX_type_ven_id_element]))
| | |--Hash Match(Inner Join, HASH:([connection_runlevel_layout_scheme].[runlevel_layout_scheme_id])=([layout_scheme].[layout_scheme_id]), RESIDUAL:([connection_runlevel_layout_scheme].[runlevel_layout_scheme_id]=[layout_scheme].[layout_scheme_id]))
| | |--Index Scan(OBJECT:([scheme].[dbo].[connection_runlevel_layout_scheme].[IX_runlevel_layout_scheme_id]))
| | |--Hash Match(Inner Join, HASH:([layout_scheme].[layout_id])=([layout_scheme].[layout_id]), RESIDUAL:([layout_scheme].[layout_id]=[layout_scheme].[layout_id]))
| | |--Hash Match(Inner Join, HASH:([scheme_address_link].[scheme_id])=([scheme].[scheme_id]), RESIDUAL:([scheme_address_link].[scheme_id]=[scheme].[scheme_id]))
| | | |--Hash Match(Inner Join, HASH:([scheme_address_link].[address_id])=([address].[address_id]), RESIDUAL:([scheme_address_link].[address_id]=[address].[address_id]))
| | | | |--Merge Join(Inner Join, MERGE:([scheme_address_link].[scheme_id])=([layout_scheme].[scheme_id]), RESIDUAL:([layout_scheme].[scheme_id]=[scheme_address_link].[scheme_id]))
| | | | | |--Clustered Index Scan(OBJECT:([scheme].[dbo].[scheme_address_link].[PK_scheme_address_link]), ORDERED FORWARD)
| | | | | |--Index Scan(OBJECT:([scheme].[dbo].[layout_scheme].[IX_layout_scheme]), ORDERED FORWARD)
| | | | |--Index Scan(OBJECT:([links].[dbo].[address].[IX_service_type_cd]))
| | | |--Clustered Index Scan(OBJECT:([scheme].[dbo].[scheme].[IX_scheme_grid_cell]))
| | |--Index Scan(OBJECT:([scheme].[dbo].[layout_scheme].[ix_layout_id]))
| |--Index Seek(OBJECT:([prospect].[dbo].[prospect].[idx_address_id] AS [p]), SEEK:([p].[address_id]=[s].[address_id]) ORDERED FORWARD)
|--Filter(WHERE:((isnull([c].[cust_status_cd], ' ')<>'W' AND isnull([c].[cust_status_cd], ' ')<>'I') AND isnull([c].[cust_status_cd], ' ')<>'T'))
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([customer].[dbo].[customer] AS [c]))
|--Index Seek(OBJECT:([customer].[dbo].[customer].[idx_address_id] AS [c]), SEEK:([c].[address_id]=[s].[address_id]) ORDERED FORWARD)

Anyone offer any suggestions on how to make this stored procedure behave like the ad hoc query?

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 10:33:53

Article discussion forum isn't meant for question. It's meant for discussing existing articles on sql Team.
I assume you have sql server 2000.
Moved.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jholovacs
Posting Yak Master

163 Posts

Posted - 2007-05-24 : 10:41:54

Whups, sorry, yes this is SQL 2000.

Kristen
Test

22859 Posts

Posted - 2007-05-24 : 11:14:17

Why are you using

WITH RECOMPILE

in your Sproc?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources