hi Svein,

Many thanks for you reply... i will try your suggestions and give a feedback....


if it works... i also pay a beer for you....

---
Luís Carlos Dill Junges ©

"A realidade de cada lugar e de cada época é uma alucinação coletiva."
Bloom, Howard

>

________________________________
De: Svein Erling Tysvaer <svein.erling.tysvaer@...>
Para: firebird-support@yahoogroups.com
Enviadas: Quarta-feira, 1 de Julho de 2009 18:21:22
Assunto: Res: [firebird-support] Optimization - Select Query





Hi again, Luis!

Looking at your plans, things at first doesn't seem all too bad, but
trying to look a bit closer, I certainly got a few questions.

Your code starts with

FOR select tbbrushes.brushid from tbbrushes
where brushid like UPPER(:partnumber)
INTO :similarbrushid

This must be the part that gets PLAN (TBBRUSHES NATURAL). This query is
only executed once, so it only has to scan through 100K records once.
Using LIKE prevents an index from being used and it will most likely be
slower than using '=' or STARTING WITH, but gives you greater
flexibility. Could well be a fair decision if you use this procedure
with partnumbers that you do not know how starts.

The next query seems OK, but then you have

FOR select tbrelshape.brushid from tbrelshape
where tbrelshape.sthick= :sim_thick
and tbrelshape.swidth= :sim_width
and tbrelshape.slength= :sim_length
and tbrelshape.shape= :sim_shape
and tbrelshape.sradius= :sim_radius
and tbrelshape.brushid <> :similarbrushid
INTO :currentbrushid

What puzzles me here, is that all your plans seems to involve just one
index, never more. I don't actually find the plan for this query in your
reported plan, but hopefully some of these fields (brushid doesn't count
since it is compared for inequality) are selective enough for an index
on them to be benefitial for this query. Unlike many other databases,
Firebird can often successfully use several indexes on the same table in
a query.

You could probably also consider joining this query to the following
query and get this code:

FOR select tbrelshape.brushid, tbbrushes.company
from tbrelshape
join tbbrushes on tbbrushes.brushid = tbrelshape.brushid
where tbrelshape.sthick= :sim_thick
and tbrelshape.swidth= :sim_width
and tbrelshape.slength= :sim_length
and tbrelshape.shape= :sim_shape
and tbrelshape.sradius= :sim_radius
and tbrelshape.brushid <> :similarbrushid
INTO :currentbrushid, :cname2
DO
BEGIN

Admittedly, I wouldn't expect there to be a big speed difference in this
case (though I don't use stored procedures enough to really tell).

Next, there is a few strange queries, e.g.

select count(*) from tbrelshunt
where brushid=:similarbru shid
group by brushid
into :nbshunts1;

I see no reason to use GROUP BY here, just delete that part so that this
(and similar) query(ies) becomes:

select count(*) from tbrelshunt
where brushid=:similarbru shid
into :nbshunts1;

The next query is actually challenging for me, I'm not used to select
from select from select:

FOR select count(*)
from (select bevel,angle
from (select bevel,angle
from tbrelbevel
where brushid=:similarbru shid
union all
select bevel,angle
from tbrelbevel
where brushid=:currentbru shid )
group by bevel,angle
having count(*) = 2) into :nbsamebevel

After a bit of thinking, I think this can be changed to:

FOR select count(*)
from tbrelbevel t1
join tbrelbevel t2 on t1.bevel = t2.bevel
and t1.angle = t2.angle
and t1.PK < t2.PK
left join tbrelbevel tNo on t1.bevel = tNo.bevel
and t1.angle = tNo.angle
and (tNo.PK not in (t1.PK, t2.PK)
where tNo.PK is NULL
and t1.brushid in (:similarbrushid, :currentbrushid)
and t2.brushid in (:similarbrushid, :currentbrushid)
into :nbsamebevel

Admittedly, this a completely different way to handle this query and do
require that there is a field in tbrelbevel who is a primary key. I
would expect my solution to be quicker, but please check, depending on
how many rows may have the same value for bevel and angle for the given
brushids, both queries may be quick.

This is basically what I notice in your procedure. As for your aim to
get your procedure to always execute as fast as it does on a later run,
then I'm afraid that you're unlikely to succeed. The reason for the
speed difference between first and later runs, is that the first run
moves things into cache.

HTH,
Set




____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

[Non-text portions of this message have been removed]