Slow procedure using view

[Witold Iwaniec]

Hi

I have a procedure that calls a view. The view is built with some outer
joins but it performs fine.

If I run in Query Analyzer

select count(*) from long_name_view
where name_id = 'AAA'

it returns instantly

The procedure has the same code. I juststripped down the code to narrow
the problem:

create or replace procedure my_name_proc
@nid VARCHAR(32)
AS

DECLARE
@nidkey_count INTEGER

select @nidkey_count = count(*)
from long_name_view
where name_id = @nid

print 'The count: ' + CAST(@nidkey_count as varchar)

GO

When I call in Query Analyzer:

exec my_name_proc 'AAA'

it takes a while to run, over 20 sec and the execution plan is
different. What is the reason that the same view is used in different
ways?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Witold Iwaniec (wi****@poczta.onet.pl) writes:

I have a procedure that calls a view. The view is built with some outer
joins but it performs fine.

If I run in Query Analyzer

select count(*) from long_name_view
where name_id = 'AAA'

it returns instantly

The procedure has the same code. I juststripped down the code to narrow
the problem:

create or replace procedure my_name_proc
@nid VARCHAR(32)
AS

DECLARE
@nidkey_count INTEGER

select @nidkey_count = count(*)
from long_name_view
where name_id = @nid

print 'The count: ' + CAST(@nidkey_count as varchar)

GO

When I call in Query Analyzer:

exec my_name_proc 'AAA'

it takes a while to run, over 20 sec and the execution plan is
different. What is the reason that the same view is used in different
ways?

There are two possible answers here. One is that there is an indexed
view or an index on a computed column somewhere, and the procedure
was created with one of the settings ANSI_NULLS or QUOTED_IDENTIFIER
off. For these settings the current run-time settings does not apply,
but those saved with the procedure. And these settings must be on for
indexed views or indexes on computed columns to be considered. You can
check this with:

select objectproperty(object_id('cur_get_sp'), 'ExecIsAnsiNullsOn')
select objectproperty(object_id('cur_get_sp'), 'ExecIsQuotedIdentOn')

If any of these are 0, recreate the procedure with these settings on
and try again. (The most likely culprit is Enterprise Manager which
has some funny ideas about QUOTED_IDENTIFIER.)

Note that it does not have to be the long_name_view itself which is
indexed, but it may include references to indexed views.

The other possible answer is that parameter sniffing is at work. When
you run the plain query, SQL Server knows exactly which value to use
and can determine which query plan from this. When you run stored
procedure the first time, SQL Servers uses the input value at that
point to determine the plan. This plan is then cached and reused, but
maybe that initial value was atypical, and you are stuck with a bad
plan as long as it stays in cache. This you can work around with

EXEC my_name_proc 'AAA' WITH RECOMPILE

to force a new plan.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp