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@...>
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.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

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