SQL Server Performance Forum – Threads Archive

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.

Can you post the execution plans? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

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.

You can use SET SHOWPLAN_TEXT ON to get the textual representation of the plans. It might be good to wrap the plans with the [ code ] … [ / code ] tags. Makes it easier to read them. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

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 @[email protected]
set @[email protected] 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

Here’s an explanation for parameter sniffing:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611 —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

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?

]]>