Store Procedure and IN

Hi

Select *
From MyView
Where foo1 = foo2
and foo3 = foo4
and foo5 = foo6
or ID in (Select ID From MyTable Where f1 = f2);

With the above statement the speed is acceptable. But when changeing the 
same view (MyView) into a store procedure with  - for select .... from ... 
do suspend. - like this :

Select *
From MyStoreProcedure
Where foo1 = foo2
and foo3 = foo4
and foo5 = foo6
or ID in (Select ID From MyTable Where f1 = f2);

The statement take a looooong time to fetch the records.
I use the PLANalyzer to check the statement, I notice that the in clause 
cause slow.

What should I do to speed up my query ??

Your's
Safwan. 

	Read his post, Karol. He's selecting from a proc. None of this will
help.

-- 
Craig Stuntz [TeamB] ˇ Vertex Systems Corp. ˇ Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Useful articles about InterBase development:

Safwan wrote:

> What should I do to speed up my query ??

	You need to move the inner select *inside* the proc. 

-- 
Craig Stuntz [TeamB] ˇ Vertex Systems Corp. ˇ Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
All the great TeamB service you've come to expect plus (New!) 

Użytkownik "Safwan" <safwan_maswadeh@yahoo.com> napisał w
wiadomości 
news:47bcbe87@newsgroups.borland.com...
> Hi
>
> Select *
> From MyView
> Where foo1 = foo2
> and foo3 = foo4
> and foo5 = foo6
> or ID in (Select ID From MyTable Where f1 = f2);
>
> With the above statement the speed is acceptable. But when changeing the 
> same view (MyView) into a store procedure with  - for select .... from ...

> do suspend. - like this :
>
> Select *
> From MyStoreProcedure
> Where foo1 = foo2
> and foo3 = foo4
> and foo5 = foo6
> or ID in (Select ID From MyTable Where f1 = f2);
>
> The statement take a looooong time to fetch the records.
> I use the PLANalyzer to check the statement, I notice that the in clause 
> cause slow.
>
> What should I do to speed up my query ??
>
change in (select ..
to inner join

Karol Bieniaszewski 

then try exists clause

Select *
From MyStoreProcedure MSP
Where MSP.foo1 = MSP.foo2
and MSP.foo3 = MSP.foo4
and MSP.foo5 = MSP.foo6
or exists(SELECT * FROM MyTable MT where MT.ID=MSP.ID AND MT.f1=MT.f2)

Karol Bieniaszewski



Użytkownik "Safwan" <safwan_maswadeh@yahoo.com> napisał w
wiadomości 
news:47bd3ecc$1@newsgroups.borland.com...
> Thanks Karol Bieniaszewski
>
> But I use OR, so inner join will not give me the wanted result.
>
> ....
> or ID in (Select ID From MyTable Where f1 = f2);
>
>
>
> "Karol Bieniaszewski" <liviuslivius@poczta.onet.pl> wrote
in message 
> news:47bd39ef$1@newsgroups.borland.com...
>>
>> Użytkownik "Safwan" <safwan_maswadeh@yahoo.com> napisał
w wiadomości 
>> news:47bcbe87@newsgroups.borland.com...
>>> Hi
>>>
>>> Select *
>>> From MyView
>>> Where foo1 = foo2
>>> and foo3 = foo4
>>> and foo5 = foo6
>>> or ID in (Select ID From MyTable Where f1 = f2);
>>>
>>> With the above statement the speed is acceptable. But when
changeing the 
>>> same view (MyView) into a store procedure with  - for select ....
from 
>>> ... do suspend. - like this :
>>>
>>> Select *
>>> From MyStoreProcedure
>>> Where foo1 = foo2
>>> and foo3 = foo4
>>> and foo5 = foo6
>>> or ID in (Select ID From MyTable Where f1 = f2);
>>>
>>> The statement take a looooong time to fetch the records.
>>> I use the PLANalyzer to check the statement, I notice that the in
clause 
>>> cause slow.
>>>
>>> What should I do to speed up my query ??
>>>
>> change in (select ..
>> to inner join
>>
>> Karol Bieniaszewski
>>
>>
>
>