At 08:10 PM 7/24/2001 +0000, Alexander V.Nevsky wrote:

> Is there any tool or way to inspect BLR for query or SP?

Yes. QLI will pretty print the blr for a stored procedure. In
fact, there is a built-in blob filter that translates between
binary byte blr and pretty printed blr. QLI won't help with actual
queries because it doesn't talk to DSQL and has a very limited
SQL vocabulary.

>1. Select T1.<something>, T2.Column
> from T1 Left Join T2 On T1.Col1=T2.Col1
>
>2. Select T1.<something>, (Select Column From T2 Where T2.Col1=T1.Col1)
> from T1
>
>3. Create SP Returns (<something>, Column)
> As
> Declare Variable Col1;
> Begin
> For Select T1.<something>, T1.Col1
> Into <something>, :Col1
> Do begin
> Select Column From T2 Where Col1=:Col1 Into :Column;
> Suspend;
> end
> End
>
>and what should be faster and why. Or someone wise will be so kind to
>explain?

Glossing over the question of wisdom, I doubt that seeing the blr
will help you estimate the cost of the various methods. BLR is just
a polish notation binary relational language and contains no optimization
or execution information. The performance of these three queries is
affected by the index on T2.Col1, on the distribution of values of Col1
between T2 and T1, and the sizes of the tables.

Internally, each of these queries should generate an indexed nested
loop, walking through T1 in storage order and using its Col1 values
as input to an indexed lookup in T2. The outer join will almost
certainly produce a simple version of that algorithm. There's some
risk that the subquery in the second version will be partially
recompiled each time through the loop, which is expensive. Subquery
handling is much improved over V4, but is still not the optimizer's
best trick.

The stored procedure should optimize well. My only question there is
what cost the procedure linkage imposes. From experience, I'd say
not much.

So, guessing, my first choice would be 1, second 3, and third 2.
to be more sure, you'll have to do some modelling based on your
own data.


Regards,

Ann
www.ibphoenix.com
We have answers.