🚀 go-pugleaf

The problem probably is that a view is just a syntactical substitution and
can use WHERE clause to limit records to return while the stored procedure
always executes from the first record to the last and only then the WHERE
conditions are checked.

I would change the procedure to accept some input arguments to limit records
inside the procedure. And I would try to split the query to two sections
combined with UNION ALL for both parts of your OR, like:

Select *
From MyStoreProcedure
Where foo1 = foo2
and foo3 = foo4
and foo5 = foo6

union all

Select *
From MyStoreProcedure
ID in (Select ID From MyTable Where f1 = f2);

If foo2..foo6 are parameters then the first section sould be like:

Select *
From MyStoreProcedure1(foo2, foo4, foo6)

and the procedure should be rewritten to take this parameters into account.

The second part should look like:

Select sp.*
From MyTable t
  left join MyStoreProcedure2(MyTable.ID) sp on 1=1
Where t.f1 = t.f2



Maxim.


"Safwan" <safwan_maswadeh@yahoo.com> �������/�������� � �������� ���������:
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 ??
>
> Your's
> Safwan.
>
>