---In firebird-support@yahoogroups.com, <tdw@...> wrote :

 

---In firebird-support@yahoogroups.com, <listas@...> wrote :

Note, stored procedures and triggers are prepared\optimized once when loaded into metadata cache. Regards, Vlad

> Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right  before first execution?

  Exactly.

> What does "right before first execution" mean?

  Hmm... "right before first execution" mean *exactly* "right before first execution" ... what is confusing here ?

  Application prepare the statement which referenced some stored procedure. Engine must resolve all objects
referenced by statement to create execution tree. Engine looks for object in metadata cache and, if not found,
reads system catalogue and construct necessary object. It involves creating execution tree for this object, of course.
Then ready to use execution tree put into the metadata cache (with other necessary bits of information).

  So, about "right before first execution" - probably "at first reference" is more clear ?

> What is the lifecycle of "metadata cache"?

  Object (stored procedure\trigger) put into metadata cache at first reference. Object could be removed from metadata
cache when index or relation (table) is dropped. Object instance is marked as obsolete when user alter corresponding
procedure\trigger. In this case new instance of object will be created and put into metadata cache. Old instance will
not be used by newly prepared statements.

  In SS metadata cache is shared by all attachements. In CS\SC each attachment have its own private copy of
metadata cache.

  It is not recommended (by docs, since IB times) to alter objects when there are other attachments present.

  SET STATISTICS doesn't invalidate any objects in metadata cache.

> So what's the answer to my question, re a process which keeps a connection open permanently and repeatedly re-runs
> the same procedures in different transactions? Does it need to drop and recreate the connection in order to take account
> of any SET STATISTICS done by other people, or not?

     Hope now you can answer on questions above by yourself :)

Regards,
Vlad