September 28, 2009 at 3:00 pm
I have a query that is about 40,000 reads less when run as a select statement. If I put that query in a proc it has a huge increase in the reads.
Anyone have any idea why?
The only difference is the declared variables become required parameters and are passed when the proc executes (exec s_DirectoryListByRole N'CUSTOMER', N'Company')
Here is the select statement:
declare @RoleType varchar(20),@OrderBy varchar(20)
SET @RoleType = 'Customer'
SET @Orderby = 'Company'
select
a.ID,a.COMPANY , a.ADDRESS1, a.ADDRESS2, a.CITY,a.STATE,a.COUNTRY,a.ZIP, a.PARENT_ID,
l.COMPANY as PARENT_COMPANY,a.KEYCONTACT_ID,isnull(a.FIRST,'') + ' ' + isnull(a.LAST,'') as NAME_FULL,
a.PHONE, a.FAX, a.EMAIL, f.NAME_FIRST as ENTERED_BY,a.ENTERED, a.ROLE_TYPE,a.WEBSITE
from v_DIRECTORY a
left outer join t_CONTACT f on f.ID=a.ENTERED_ID
left outer join t_DIRECTORY l on l.ID = a.PARENT_ID
where a.ROLE_TYPE = @RoleType
order by
case @OrderBy
when 'Company' then a.COMPANY
when 'Address1' then a.ADDRESS1
when 'City' then a.CITY
when 'State' then a.STATE
when 'Phone' then a.PHONE
else a.COMPANY
end
Statistics IO for the proc:
Table 't_CONTACT'. Scan count 2, logical reads 1798, physical reads 0, read-ahead reads 0.
Table 't_XREF'. Scan count 20373, logical reads 42753, physical reads 0, read-ahead reads 0.
Table 't_DIRECTORY'. Scan count 3, logical reads 1705, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1188 ms, elapsed time = 3274 ms.
Statistics IO for the query in MGMT Studio:
Table 't_CONTACT'. Scan count 2, logical reads 1798, physical reads 0, read-ahead reads 0.
Table 't_DIRECTORY'. Scan count 3, logical reads 1705, physical reads 0, read-ahead reads 0.
Table 't_XREF'. Scan count 4, logical reads 1052, physical reads 0, read-ahead reads 0.
September 28, 2009 at 3:18 pm
The most likely answer is parameter sniffing. Gail Shaw has a good article on that at http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/.
Read that, and if you still need help on it, please post the execution plan for the script, and the execution plan for the proc. That's where I'd need to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 3:19 pm
AVB (9/28/2009)
I have a query that is about 40,000 reads less when run as a select statement. If I put that query in a proc it has a huge increase in the reads.Anyone have any idea why?
Possibly. Without actually running this in my environment, you may want to look at "parameter sniffing". I had a really large data loading process a few years back that presented the SAME symptoms. In my case it was a difference between 90 minutes in processing time versus 16 hours in what was acting like a runaway query.
Even though it seems silly, try declaring local variables within the sp and reassigning your parameters to them. So, your call looks like:
EXEC dbo.mystoredProc @var1 = 'a', @var2 = 'b' <snip...>
DECLARE
@localVar1 <<datatype>>,
@localVar2 <<datatype>>
SET @localVar1 = @var1
SET @localVar2 = @var2
Make sure to change your variable assignments in the code to look at the "new" local versions of the same thing. See if that solves your problem - it did mine.
HTH:
Mark
September 29, 2009 at 9:57 am
Thanks G2 and Mark!
That was definitely the issue!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply