Optimizer in Stored Procedure

Hello
We have a question about the optimizer in Stored Procedure.
The Problem ist as follow
We have 3 tabels, every tabel has columns
key1 ((rtrim(([number] + right(('0000' + convert(varchar
(4),[position])),4) + right(('00' + convert(varchar(2),
[type])),2)))))
number char(8)
position num(4)
type num(2)
every tabel has a index on the column key1
We take the following statement in der sql query analyzer
set rowcount 1
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
order by key1 desc
The executen plan gives the following information
a index seek over index from key1 ordered backward for
tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
selected for every tabel)
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very fast
this is exactil what we want.
When we put this statement in a Stored procedure and
execute this stored procedure, the execution is very long
when there are many lines in the tabels
The executen plan gives for the executen from the stored
procedure the following information
a index scann over index form key1 for tabel1, tabel2 and
tabel3 ist executed (most of the lines of the tabels will
be selected)
a compute scalar / filter / sort ist peformed vor every
scann
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very slow when
there are many lines in the tables
Wy is there a difference betwen this two execution planes
can anybody help us
thanks very much
PeterPeter,
this question can only be answered based on the actual queries. Usually,
the queries you run in Query Analyser or not identical to the queries in
a stored procedure (because of local variables, etc.).
If you are using SQL Server 7.0 or later, you could drop the "set
rowcount" and use the TOP keyword.
select TOP 1 key1 from (
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
) as T
order by key1 desc
Hope this helps,
Gert-Jan
Peter Wyss wrote:
> Hello
> We have a question about the optimizer in Stored Procedure.
> The Problem ist as follow
> We have 3 tabels, every tabel has columns
> key1 ((rtrim(([number] + right(('0000' + convert(varchar
> (4),[position])),4) + right(('00' + convert(varchar(2),
> [type])),2)))))
> number char(8)
> position num(4)
> type num(2)
> every tabel has a index on the column key1
> We take the following statement in der sql query analyzer
> set rowcount 1
> select key1 from tabel1 where key1 < '12345678123412'
> union all
> select key1 from tabel2 where key1 < '12345678123412'
> union all
> select key1 from tabel3 where key1 < '12345678123412'
> order by key1 desc
> The executen plan gives the following information
> a index seek over index from key1 ordered backward for
> tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
> selected for every tabel)
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very fast
> this is exactil what we want.
> When we put this statement in a Stored procedure and
> execute this stored procedure, the execution is very long
> when there are many lines in the tabels
> The executen plan gives for the executen from the stored
> procedure the following information
> a index scann over index form key1 for tabel1, tabel2 and
> tabel3 ist executed (most of the lines of the tabels will
> be selected)
> a compute scalar / filter / sort ist peformed vor every
> scann
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very slow when
> there are many lines in the tables
> Wy is there a difference betwen this two execution planes
> can anybody help us
> thanks very much
> Petersql