ad hoc query ridiculously faster than sproc?

I have a stored procedure similar to this:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

CREATE PROC [links].[dbo].[proc_address_select]
@vINT,
@idVARCHAR(15)
WITH RECOMPILE
AS
BEGIN
SELECT
s.field1,
s.field2,
first_name,
last_name,
addr_house_num,
addr_street,
addr_supp1,
addr_zip,
dbOther.dbo.func_get_name(p.id) as e_id,
vsa.id as t_id,
CASE COALESCE(status_cd, '')
WHEN 'M' THEN 'Y'
ELSE
''
END
as gauge,
c.customer_id,
s.service_id
FROM
links.dbo.service s
LEFT OUTER JOIN survey.dbo.prospect p
ON s.service_id = p.service_id
LEFT OUTER JOIN inf.dbo.vw_transform vsa
ON s.service_id = vsa.service_id
LEFT OUTER JOIN customer.dbo.customer c
ON s.service_id = c.service_id
AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
WHERE
vsa.v_id = @v
AND vsa.t_id = @id
ORDER BY
addr_street, addr_house_num, addr_supp1
END


If I run a query like:

exec proc_service_address_select @v = <some number>, @id = <some varchar string>

this proc will return results in about 6-10 minutes.

If however, I run this query as ad hoc in management studio or Query analyzer with the same parameter values like so:

DECLARE @v INT
DECLARE @id VARCHAR(15)

set @v=<some number>
set @id=<some varchar string>

SELECT
s.field1,
s.field2,
first_name,
last_name,
addr_house_num,
addr_street,
addr_supp1,
addr_zip,
dbOther.dbo.func_get_name(p.id) as e_id,
vsa.id as t_id,
CASE COALESCE(status_cd, '')
WHEN 'M' THEN 'Y'
ELSE
''
END
as gauge,
c.customer_id,
s.service_id
FROM
links.dbo.service s
LEFT OUTER JOIN survey.dbo.prospect p
ON s.service_id = p.service_id
LEFT OUTER JOIN inf.dbo.vw_transform vsa
ON s.service_id = vsa.service_id
LEFT OUTER JOIN customer.dbo.customer c
ON s.service_id = c.service_id
AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
WHERE
vsa.v_id = @v
AND vsa.t_id = @id
ORDER BY
addr_street, addr_house_num, addr_supp1


This will give me the expected result, with one huge difference: this query takes 10 to 15 seconds!!!

The data execution plans are completely different, and I have no idea why. Suggestions?

I'd try dropping the WITH RECOMPILE clause.

eh... that's what it started with. I thought it might be caching its execution plan, so I added the RECOMPILE hint, with no noticeable performance difference.

The data execution plan between the ad hoc query and the sproc are pretty radically different, and I'm scratching my head over why that would be.

maybe... is there a way to make them "text only"? the DEP takes up like 10 pages of mostly empty space, very difficult to follow as an image file.

look into parameter sniffing

what are the data type for the columns below
vsa.venture_id = @jv
AND vsa.transformer_id = @id

venture_id is int, and transformer_id is varchar(15), just like the vars. Plus, I would think that if that was the performance issue, the result would be the same across both query methods.

I'm trying to get management buy-in to post the DEP's; it's all proprietary stuff so they have to approve.

ok, but it is still a parameter sniffing issue
you need to search this matter

for now, try


CREATE PROC [dbo].[proc_service_address_select] @jv INT, @id VARCHAR(15)
AS
BEGIN

DECLARE @jv2 INT
DECLARE @id2 VARCHAR(15)

set @jv2=@jv
set @id2=@id

SELECT s.field1, s.field2, first_name, last_name, addr_house_num,addr_street,
addr_supp1,addr_zip, dbOther.dbo.func_get_name(p.element_id) as element,
vsa.transform_id as el,
CASE COALESCE(campaign_status_cd, '') WHEN 'M' THEN 'Y' ELSE '' END as meter,
c.customer_id, s.service_id
FROM clink.dbo.service s
LEFT OUTER JOIN prospect.dbo.prospect p ON s.service_id = p.service_id
LEFT OUTER JOIN structure.dbo.vw_transform vsa ON s.service_id = vsa.service_id
LEFT OUTER JOIN customer.dbo.customer c ON s.service_id = c.service_id
AND isnull(c.cust_status_cd,'') not in ('T', 'I', 'W')
WHERE vsa.venture_id = @jv2
AND vsa.transformer_id = @id2
ORDER BY addr_street, addr_house_num, addr_supp1
END

this still took 6:00.

now you need to look at the execution plan for each

also
does it take 6min to execution the sp from the client app
does it take 6min to exec the sp from Management Studio?

is there a way to "capture" the execution plan for the ad hoc query, and force the sp to use it?