Странное поведение оптимизатора. sql 2000

[Kon]

Имеем процедуру,

create proc dbo.good
@first varchar (100) =null
@last varchar (100) = null
as
select id from sometable where firstname=@first and lastname=@last
go

execution time: 200 миллисекунд

если я меняю процедуру на

create proc dbo.bad
as
declare @first varchar (100),@last varchar (100)
select @first=null, @last=null
select id from sometable where firstname=@first and lastname=@last
go
execution time: 9 секунд.

План меняется в худшую сторону. Почему оно так делает?


P.S.
см. картинки.

[tengiz]

Kon, судя по планам, реальная процедура заметно сложнее. Для того, чтобы понять что точно просходит нужно больше информации.

[Kon]

Ну чтож, прикрепляю удлиненную версию. Спасибо, если кому охота копатся.

CREATE PROCEDURE dbo.good
@lead_firstname_n varchar(50)
, @lead_lastname_n varchar(50)
, @lead_dayphone_n varchar(50)
, @lead_evephone_n varchar(50)
, @lead_email_n varchar(255) ,
@sycampusid int
, @activity bit ,

@lead_firstname varchar(50) =null,
, @lead_lastname varchar(50) =null,
, @lead_dayphone varchar(50) =null,
, @lead_evephone varchar(50) =null,
, @lead_email varchar(255) =null,
, @lead_dayphone2 varchar(50) =null,
, @lead_evephone2 varchar(50) =null

as

select
c2k_ss.systudentid
, rtrim(c2k_ss.firstname) firstname
, rtrim(c2k_ss.lastname) lastname
, rtrim(c2k_ss.nickname) nickname
, case when c2k_ss.workphone = 'unk' or c2k_ss.workphone = 'unknown' then '' else rtrim(c2k_ss.workphone) end workphone
, case when c2k_ss.phone = 'unk' or c2k_ss.phone = 'unknown' then '' else rtrim(c2k_ss.phone) end phone
, case when c2k_ss.email = 'unk' or c2k_ss.email = 'unknown' then '' else rtrim(c2k_ss.email) end email
, rtrim(c2k_ss.addr1) addr1
, rtrim(isnull(c2k_ss.addr2,'')) addr2
, rtrim(c2k_ss.city) city
, rtrim(c2k_ss.state) state
, rtrim(c2k_ss.zip) zip
, case when len(ltrim(rtrim(isnull(country.descrip,'')))) > 10 and country.code!=null and len(ltrim(rtrim(isnull(country.code, '')))) > 0 then
ltrim(rtrim(country.code))
else
ltrim(rtrim(case when len(isnull(country.descrip,'')) > 0 and substring(country.descrip,1,1) = '.' then substring(country.descrip, 2, len(country.descrip)-1) else '' end))
end country
, ltrim(rtrim(isnull(program.descrip, ''))) program
, c2k_ss.leaddate
, ltrim(rtrim(isnull(status.descrip,''))) status
, isnull(amrep.amrepid, -1) amrepid
, ltrim(rtrim(case when len(isnull(amrep.descrip,'')) > 0 and substring(amrep.descrip,1,1) = '.' then substring(amrep.descrip, 2, len(amrep.descrip)-1) else '' end)) amrep
, isnull(c2k_ss.amleadtypeid, -1) amleadtypeid
, isnull(c2k_ls.amLeadCatID, -1) amleadcatid
, isnull((case when @activity = 1 then (select max(c2k_e.DateLstMod) from cectest.dbo.cmevent c2k_e (nolock) where c2k_ss.amrepid = c2k_e.SyStaffID and c2k_ss.SyStudentID = c2k_e.SyStudentID) else null end), '01/01/1900') Event_DateLstMod
, isnull((case when @activity = 1 then (select max(c2k_et.descrip) from cectest.dbo.cmevent c2k_e (nolock) inner join cectest.dbo.cmEventType c2k_et (nolock) on c2k_e.CmEventTypeID = c2k_et.CmEventTypeID where c2k_ss.amrepid = c2k_e.SyStaffID and c2k_ss.S
yStudentID = c2k_e.SyStudentID) else null end ), '') EventType
from (
select
systudentid
from cectest.dbo.systudent ss (nolock)
where
(sycampusid = @sycampusid) and
(
(lastname = @lead_LastName and firstname = @lead_firstName)
or (email = @lead_email)
or (phone = @lead_dayphone)
or (phone = @lead_dayphone2)
or (phone = @lead_evephone)
or (phone = @lead_evephone2)
or (workphone = @lead_evephone)
or (workphone = @lead_evephone2)
or (workphone = @lead_dayphone2)
or (workphone = @lead_dayphone)
)
) t
inner join cectest.dbo.systudent c2k_ss (nolock) on t.systudentid = c2k_ss.systudentid
left outer join cectest.dbo.sycountry country (nolock) on c2k_ss.sycountryid = country.sycountryid
left outer join cectest.dbo.adprogram program (nolock) on c2k_ss.adprogramid = program.adprogramid
left outer join cectest.dbo.syschoolstatus status (nolock) on c2k_ss.syschoolstatusid = status.syschoolstatusid
left outer join cectest.dbo.amrep amrep (nolock) on c2k_ss.amrepid = amrep.amrepid
left outer join cectest.dbo.amleadsrc c2k_ls (nolock) on c2k_ss.amleadsrcid = c2k_ls.amleadsrcid
go

-----------------------------------------------
CREATE PROCEDURE dbo.bad
@lead_firstname_n varchar(50)
, @lead_lastname_n varchar(50)
, @lead_dayphone_n varchar(50)
, @lead_evephone_n varchar(50)
, @lead_email_n varchar(255) ,
@sycampusid int
, @activity bit
as

declare
@lead_firstname varchar(50)
, @lead_lastname varchar(50)
, @lead_dayphone varchar(50)
, @lead_evephone varchar(50)
, @lead_email varchar(255)
, @lead_dayphone2 varchar(50)
, @lead_evephone2 varchar(50)


select
@lead_firstname = null
, @lead_lastname = null
, @lead_dayphone = null
, @lead_evephone = null
, @lead_email = null
, @lead_dayphone2 = null
, @lead_evephone2 = null

select
c2k_ss.systudentid
, rtrim(c2k_ss.firstname) firstname
, rtrim(c2k_ss.lastname) lastname
, rtrim(c2k_ss.nickname) nickname
, case when c2k_ss.workphone = 'unk' or c2k_ss.workphone = 'unknown' then '' else rtrim(c2k_ss.workphone) end workphone
, case when c2k_ss.phone = 'unk' or c2k_ss.phone = 'unknown' then '' else rtrim(c2k_ss.phone) end phone
, case when c2k_ss.email = 'unk' or c2k_ss.email = 'unknown' then '' else rtrim(c2k_ss.email) end email
, rtrim(c2k_ss.addr1) addr1
, rtrim(isnull(c2k_ss.addr2,'')) addr2
, rtrim(c2k_ss.city) city
, rtrim(c2k_ss.state) state
, rtrim(c2k_ss.zip) zip
, case when len(ltrim(rtrim(isnull(country.descrip,'')))) > 10 and country.code!=null and len(ltrim(rtrim(isnull(country.code, '')))) > 0 then
ltrim(rtrim(country.code))
else
ltrim(rtrim(case when len(isnull(country.descrip,'')) > 0 and substring(country.descrip,1,1) = '.' then substring(country.descrip, 2, len(country.descrip)-1) else '' end))
end country
, ltrim(rtrim(isnull(program.descrip, ''))) program
, c2k_ss.leaddate
, ltrim(rtrim(isnull(status.descrip,''))) status
, isnull(amrep.amrepid, -1) amrepid
, ltrim(rtrim(case when len(isnull(amrep.descrip,'')) > 0 and substring(amrep.descrip,1,1) = '.' then substring(amrep.descrip, 2, len(amrep.descrip)-1) else '' end)) amrep
, isnull(c2k_ss.amleadtypeid, -1) amleadtypeid
, isnull(c2k_ls.amLeadCatID, -1) amleadcatid
, isnull((case when @activity = 1 then (select max(c2k_e.DateLstMod) from cectest.dbo.cmevent c2k_e (nolock) where c2k_ss.amrepid = c2k_e.SyStaffID and c2k_ss.SyStudentID = c2k_e.SyStudentID) else null end), '01/01/1900') Event_DateLstMod
, isnull((case when @activity = 1 then (select max(c2k_et.descrip) from cectest.dbo.cmevent c2k_e (nolock) inner join cectest.dbo.cmEventType c2k_et (nolock) on c2k_e.CmEventTypeID = c2k_et.CmEventTypeID where c2k_ss.amrepid = c2k_e.SyStaffID and c2k_ss.S
yStudentID = c2k_e.SyStudentID) else null end ), '') EventType

from (
select
systudentid
from cectest.dbo.systudent ss (nolock)
where
(sycampusid = @sycampusid) and
(
(lastname = @lead_LastName and firstname = @lead_firstName)
or (email = @lead_email)
or (phone = @lead_dayphone)
or (phone = @lead_dayphone2)
or (phone = @lead_evephone)
or (phone = @lead_evephone2)
or (workphone = @lead_evephone)
or (workphone = @lead_evephone2)
or (workphone = @lead_dayphone2)
or (workphone = @lead_dayphone)
)
) t
inner join cectest.dbo.systudent c2k_ss (nolock) on t.systudentid = c2k_ss.systudentid
left outer join cectest.dbo.sycountry country (nolock) on c2k_ss.sycountryid = country.sycountryid
left outer join cectest.dbo.adprogram program (nolock) on c2k_ss.adprogramid = program.adprogramid
left outer join cectest.dbo.syschoolstatus status (nolock) on c2k_ss.syschoolstatusid = status.syschoolstatusid
left outer join cectest.dbo.amrep amrep (nolock) on c2k_ss.amrepid = amrep.amrepid
left outer join cectest.dbo.amleadsrc c2k_ls (nolock) on c2k_ss.amleadsrcid = c2k_ls.amleadsrcid

[Dmitry67]

Ай яй яй, production code в инете

[tengiz]

Kon, спасибо за продробный скрипт, но без метаданных он малополезен. Если всё-таки вернуться к Вашему исходному сообщению, то проблема с процедурой скорее всего в том, что в таблице sometable строк с firstname=NULL and lastname=NULL относительно мало и об этом чётко говорит статистическая гистограмма по соответствующим индексам - селективность для NULL, видимо, высокая. Оптимизатор "видит" значения параметров процедуры на этапе компиляции если они имеют defaults (первый случай), поэтому компилирует процедуру с планом, оптимальным для @first = NULL и @last = NULL. Когда эти параметры переносятся в тело процедуры и становятся локальными переменными, оптимизатор не пытается делать заключений о том, какие значения могут иметь эти переменные, поэтому компилирует план, который годится для среднего случая. А средний случай - это средняя селективность по индексу целиком, которая, видимо, уже заметно ниже, чем для случая с NULL.

[zVlad]

Kon, спасибо за продробный скрипт, но без метаданных он малополезен. Если всё-таки вернуться к Вашему исходному сообщению, то проблема с процедурой скорее всего в том, что в таблице sometable строк с firstname=NULL and lastname=NULL относительно мало и об этом чётко говорит статистическая гистограмма по соответствующим индексам - селективность для NULL, видимо, высокая. Оптимизатор "видит" значения параметров процедуры на этапе компиляции если они имеют defaults (первый случай), поэтому компилирует процедуру с планом, оптимальным для @first = NULL и @last = NULL. Когда эти параметры переносятся в тело процедуры и становятся локальными переменными, оптимизатор не пытается делать заключений о том, какие значения могут иметь эти переменные, поэтому компилирует план, который годится для среднего случая. А средний случай - это средняя селективность по индексу целиком, которая, видимо, уже заметно ниже, чем для случая с NULL.

А не значит ли это что таблица создана с атрибутом NOT NULL по крайне мере для lastname (или firstname - неважно) и когда оптимизатор видит поиск по значению которого не может быть то он сразу возвращает пустой "result set", когда же ему значение недоступно - пытается искать?