hi..

enclosed is procedure code i am working on...

the query plan is....

PLAN (TBRELSHAPE INDEX (FK_BRUSHID))(TBBRUSHES INDEX (RDB$PRIMARY1867))(TBRELSHUNT ORDER RDB$FOREIGN1898 INDEX (RDB$FOREIGN1898))(TBRELSHUNT ORDER RDB$FOREIGN1898 INDEX (RDB$FOREIGN1898))(TBRELBEVEL ORDER INTEG_3490 INDEX (RDB$FOREIGN1882))(TBRELBEVEL ORDER INTEG_3490 INDEX (RDB$FOREIGN1882))(TBRELBEVEL INDEX (RDB$FOREIGN1882))
PLAN (TBRELBEVEL INDEX (RDB$FOREIGN1882))(TBRELPAD ORDER FK_21121 INDEX (FK_21121))(TBRELPAD ORDER FK_21121 INDEX (FK_21121))(TBRELPAD INDEX (FK_21121)) PLAN (TBRELPAD INDEX (FK_21121))(TBRELCLIP ORDER FK_6781 INDEX (FK_6781))(TBRELCLIP ORDER FK_6781 INDEX (FK_6781))(TBRELHOLE ORDER FK_21333 INDEX (FK_21333))(TBRELHOLE ORDER FK_21333 INDEX (FK_21333))(TBRELRMATERIAL ORDER PK_231 INDEX (FK_2312))(TBRELRMATERIAL ORDER PK_231 INDEX (FK_2312))(TBRELTERMINAL ORDER RDB$FOREIGN1886 INDEX (RDB$FOREIGN1886))(TBRELTERMINAL ORDER RDB$FOREIGN1886 INDEX (RDB$FOREIGN1886))(TBCOMPANY INDEX (RDB$PRIMARY1852))(TBRELSHAPE INDEX (RDB$FOREIGN1915))(TBBRUSHES NATURAL)

looking this plan, i extracted the higher selectivity number.... which is the one i have shown on the last email...

basically the procedure returns similar 'carbone brushes' based on the comparison of properties... (width, length, shape...etc)

the first run of the procedure returns something like show below in 2.47 sec while the second run return 1.4 sec... the objective is build a procedure that always return on 1.4 seconds... the reason is because some searches use to take around 10 seconds on the first run and 5 seconds on the second run and 2 and the third run...
SQL>set stats;
SQL> select * from getcrossreference('dve-70','','');
SQL>
PARTNUMBER1 PARTNUMBER2 STATUS
============================== =========================
207C2380PO1 DVE-70 1
197C6249P01 DVE-70 1
207C2380P01 DVE-70 1
25C11541P01 DVE-70 1
493X606PO1 DVE-70 1
493X606PO2 DVE-70 1
493X606PO3 DVE-70 1
493X606PO4 DVE-70 1
493X606PO5 DVE-70 1
E50790 DVE-70 1
E81188 DVE-70 1
J09796 DVE-70 1
J16270 DVE-70 1
M34323 DVE-70 1
61039809 DVE-70 1
61040691 DVE-70 1
61040905 DVE-70 1
61044439 DVE-70 1
OE-300 DVE-70 1
DVE-190 DVE-70 1
OE-164 DVE-70 1
OE-87 DVE-70 1


the firebird version is Firebird 2.1.1 (Windows Build)

the number if records on table tbbrushes is 99926 (some brush properties)
The number of recors on table TBRELSHAPE (relation between brush and shape) is 99926
the number of records of table tbshape is 16 (types of shape)




---
Luís Carlos Dill Junges ©

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

>

________________________________
De: Svein Erling Tysvær <svein.erling.tysvaer@...>
Para: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Enviadas: Quarta-feira, 1 de Julho de 2009 12:00:00
Assunto: RE: [firebird-support] Optimization - Select Query





How many records are there in this table? If it is only 12, then a selectivity of 0.083333 is as good as it can be, and not the source of any slowness.

In order to give you the best possible help, we need to know Firebird version, query (or interesting parts of the stored procedure), plan, and some more information about selectivity - if you use combined indexes, then it would be good to know approximate selectivity of individual parts as well (adding the primary key to the end of an index, makes its selectivity brilliant, but that doesn't help if you only use the preceding fields in your WHERE clause).

Set

-----Original Message-----
From: firebird-support@ yahoogroups. com [mailto:firebird-support@ yahoogroups. com] On Behalf Of Luis Carlos Junges
Sent: 1. juli 2009 16:31
To: firebird-support@ yahoogroups. com
Subject: Res: [firebird-support] Optimization - Select Query

thanks for the explanation. . now... i looked at the indexes and found that the biggest one that must be reduced and, consequently, will make the procedure quicker.

TABLE:tbrelshape
BRUSHID
SHAPE
WIDTH
LENGTH
THICKRADIUS

Primary key(brushid, shape)

the index has a selectivity of 0.083333 and the others 20 indexes used at the procedure have value lower than 0.005...

the index is the shape field from the table which is a foreign key to a table TBSHAPE will all possible shapes.

the population of the table is as follow (brushid is always different):

1 = CON
145 = CYL
551 = PAIR
78373 = SINGLE
20207 = SPLIT
16 = TRIPLE
256 = TSPLIT
1 = WEDGE

well, running the procedure for shape TRIPLE, for example, it is really quick while for SINGLE, the problem i am talking about appears....

i will thanks for any idea of how i should modify the indexes in such order to get a procedure always fast as possible Or how i could organize this little data in such way to get a procedure always fast...

---
Luís Carlos Dill Junges ©

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

____________ _________ _________ __
De: Dimitry Sibiryakov <sd@ibphoenix. com>
Para: firebird-support@ yahoogroups. com
Enviadas: Terça-feira, 5 de Maio de 2009 14:28:21
Assunto: Re: [firebird-support] Optimization - Select Query

> The problem is that after some time it requires the 8~10 seconds again. It seems that after some time the optimization table (or whatever is) is deleted.

It is not "optimization table" but "data cache". To make you query to
be always fast you must analyse it's plan and, perhaps, change the query
or data structure. May be add needed index or remove unneeded one.

SY, SD.

____________ _________ _________ _________ _________ __
Fale com seus amigos de graça com o novo Yahoo! Messenger
http://br.messenger .yahoo.com/

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

------------ --------- --------- ------

++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++

Visit http://www.firebird sql..org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoeni x.com

++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
Yahoo! Groups Links





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

SET TERM !! ;
CREATE PROCEDURE GetCrossReference
(
partnumber varCHAR(30),
company varchar(30),
classification varchar(30)
)

RETURNS (
partnumber1 varchar(30),
partnumber2 varchar(30),
status integer
)


AS
DECLARE VARIABLE currentbrushid VARCHAR(30);
DECLARE VARIABLE similarbrushid VARCHAR(30);
DECLARE VARIABLE nbshunts1 integer;
DECLARE VARIABLE nbshunts2 integer;
DECLARE VARIABLE nbbevels1 integer;
DECLARE VARIABLE nbbevels2 integer;
DECLARE VARIABLE nbpad1 integer;
DECLARE VARIABLE nbpad2 integer;
DECLARE VARIABLE nbclip1 integer;
DECLARE VARIABLE nbclip2 integer;
DECLARE VARIABLE nbrmat1 integer;
DECLARE VARIABLE nbrmat2 integer;
DECLARE VARIABLE nbhole1 integer;
DECLARE VARIABLE nbhole2 integer;
DECLARE VARIABLE nbterm1 integer;
DECLARE VARIABLE nbterm2 integer;

DECLARE VARIABLE nbsamebevel integer;
DECLARE VARIABLE nbsameshunt integer;
DECLARE VARIABLE nbbevels integer;
DECLARE VARIABLE nbshunts integer;
DECLARE VARIABLE nbpads integer;

DECLARE VARIABLE nbsamepad integer;

declare variable cname2 varchar(30);
declare variable class2 varchar(30);
declare variable passproperties integer;


declare variable sim_shape varchar(30);


declare variable sim_width float;

declare variable sim_length float;
declare variable sim_radius float;
declare variable sim_thick float;

BEGIN
passproperties = 1;
FOR select tbbrushes.brushid from tbbrushes where brushid like UPPER(:partnumber)
INTO :similarbrushid
DO
BEGIN
select shape,swidth,slength,sradius,sthick from tbrelshape where brushid=:similarbrushid into :sim_shape, :sim_width,:sim_length,:sim_radius,:sim_thick;
IF (similarbrushid <> '') THEN
BEGIN


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

DO
BEGIN
select tbbrushes.company from tbbrushes where brushid=:currentbrushid into :cname2;

passproperties = 1;
nbshunts1 = 0;
nbshunts2 = 0;
nbbevels1 = 0;
nbbevels2 = 0;
nbpad1 = 0;
nbpad2 = 0;
nbclip1 = 0;
nbclip2 = 0;
nbrmat1 = 0;
nbrmat2 = 0;
nbhole1 = 0;
nbhole2 = 0;
nbterm1 = 0;
nbterm2 = 0;
nbsamepad=0;
nbsamebevel=0;
nbsameshunt=0;
nbbevels=0;
nbshunts=0;
nbpads=0;


select count(*) from tbrelshunt where brushid=:similarbrushid group by brushid into :nbshunts1;
select count(*) from tbrelshunt where brushid=:currentbrushid group by brushid into :nbshunts2;

nbshunts1 = nbshunts1 + 1;
nbshunts2 = nbshunts2 + 1;

IF ( nbshunts1 = nbshunts2) THEN
BEGIN

END
ELSE
BEGIN
passproperties = 0;
END

select count(*) from tbrelbevel where brushid=:similarbrushid group by brushid into :nbbevels1;
select count(*) from tbrelbevel where brushid=:currentbrushid group by brushid into :nbbevels2;

nbbevels1 = nbbevels1 + 1;
nbbevels2 = nbbevels2 + 1;

IF ( nbbevels1 = nbbevels2) THEN
BEGIN
IF ( nbbevels1 <> 1) THEN
BEGIN
FOR select count(*) from (select bevel,angle from (select bevel,angle from tbrelbevel where brushid=:similarbrushid union all select bevel,angle from tbrelbevel where brushid=:currentbrushid ) group by bevel,angle having count(*) = 2) into :nbsamebevel
DO
BEGIN
nbsamebevel = nbsamebevel + 1;
IF ( nbsamebevel = nbbevels1 ) THEN
BEGIN
nbbevels = nbsamebevel;
END
ELSE
BEGIN
passproperties = 0;
END

END

IF ( nbbevels <> nbbevels1 ) THEN
BEGIN
passproperties=0;
END
END
END
ELSE
BEGIN
passproperties = 0;
END

select count(*) from tbrelpad where brushid=:similarbrushid group by brushid into :nbpad1;
select count(*) from tbrelpad where brushid=:currentbrushid group by brushid into :nbpad2;

nbpad1 = nbpad1 + 1;
nbpad2 = nbpad2 + 1;


IF ( nbpad1 = nbpad2) THEN
BEGIN

IF ( nbpad1 <> 1) THEN
BEGIN
FOR select count(*) from (select pad from (select pad from tbrelpad where brushid=:similarbrushid union all select pad from tbrelpad where brushid=:currentbrushid ) group by pad having count(*) = 2) into :nbsamepad
DO
BEGIN
nbsamepad = nbsamepad + 1;
IF ( nbsamepad = nbpad1 ) THEN
BEGIN
nbpads = nbsamepad;
END
ELSE
BEGIN
passproperties = 0;
END

END

IF ( nbpads <> nbpad1 ) THEN
BEGIN
passproperties=0;
END
END

END
ELSE
BEGIN
passproperties = 0;
END



select count(*) from tbrelclip where brushid=:similarbrushid group by brushid into :nbclip1;
select count(*) from tbrelclip where brushid=:currentbrushid group by brushid into :nbclip2;

nbclip1 = nbclip1 + 10;
nbclip2 = nbclip2 + 10;


IF ( nbclip1 = nbclip2) THEN
BEGIN

END
ELSE
BEGIN
passproperties = 0;
END

select count(*) from tbrelhole where brushid=:similarbrushid group by brushid into :nbhole1;
select count(*) from tbrelhole where brushid=:currentbrushid group by brushid into :nbhole2;

nbhole1 = nbhole1 + 10;
nbhole2 = nbhole2 + 10;


IF ( nbhole1 = nbhole2) THEN
BEGIN

END
ELSE
BEGIN
passproperties = 0;
END

select count(*) from tbrelrmaterial where brushid=:similarbrushid group by brushid into :nbrmat1;
select count(*) from tbrelrmaterial where brushid=:currentbrushid group by brushid into :nbrmat2;

nbrmat1 =nbrmat1 + 10;
nbrmat2 =nbrmat2 + 10;

IF ( nbrmat1 = nbrmat2) THEN
BEGIN

END
ELSE
BEGIN
passproperties = 0;
END

select count(*) from tbrelterminal where brushid=:similarbrushid group by brushid into :nbterm1;
select count(*) from tbrelterminal where brushid=:currentbrushid group by brushid into :nbterm2;

nbterm1 =nbterm1 + 10;
nbterm2 =nbterm2 + 10;

IF ( nbterm1 = nbterm2) THEN
BEGIN

END
ELSE
BEGIN
passproperties = 0;
END

IF ( :passproperties = 1 ) THEN
BEGIN
PARTNUMBER2 = similarbrushid;
PARTNUMBER1 = currentbrushid;
status = 1;

class2 = 'NoCOmpanyDefined';
select cclass from tbcompany where cname = :cname2 into :class2;

if ( :company <> '' ) then
begin
if ( :cname2 = :company ) then
begin
suspend;
end
else
begin
if ( :classification <> '' ) then
begin
if ( :class2 = :classification ) then
begin
suspend;
end
end

end
end
else
begin
if ( :classification <> '' ) then
begin
if ( :class2 = :classification ) then
begin
suspend;
end
end
else
begin

suspend;

end
end



END
ELSE
BEGIN
passproperties = 1;
END

END
END

ELSE

BEGIN




END
END
END !!
SET TERM ; !!













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