MetaData caching in Village

[Thomas Vandahl <tv...@apache.org>]

Hi folks,

I'm currently trying to fix that long outstanding problem of excessive
meta-data access in Village. I find that I could need some net.wisdom.
So if someone could try to reproduce/confirm my thoughts I'd be grateful.

- The problem is most probably in Column.populate()
- Obviously, some caching takes place in Schema, but it doesn't work for
QueryDataSets, just for DataSets and TableDataSets
- I found a hint that it is better to fetch the ResultMetaData from a
prepared statement that is just prepared but not executed.
- Would it be "legal" to create a PreparedStatement on a connection that
has other statements/resultsets open?
- I'd like to use the existing caching mechanism, which creates Schema
objects from "select * from tablename". Would that cover aliased
columns, too?
- If I don't want to introduce additional dependencies, the lifetime of
the cache (being a Hashtable) would be the lifetime of the JVM. Some
ideas for orderly shutdown/invalidation would be helpful.
- Would someone be willing to verify my fixes against different
databases, namely Sybase, MS-SQL and Oracle?

Thanks in advance.

Bye, Thomas

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

Re: MetaData caching in Village

[Thomas Fischer <fi...@seitenbau.net>]

> > That caching in schema is the one that I remembered a month ago. It's a
> > pity that the connection is not provided in the constructor
QueryDataSet
> > (ResultSet resultSet). Thed most difficult task is to create an
adequate
> > key for caching, as one can connections to multiple databases, and
these
> > databases can have different table structure. I do not currently have a
> > good idea for the caching key.
>
> Actually, there is ResultSet.getStatement().getConnection().

Ak ok, did not find that. So one can use the caching in the Schema object
(I would not use twqo caches for the same thing)

But still the alias problem is unsolved...

      Thomas


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

Re: MetaData caching in Village

[Thomas Vandahl <tv...@apache.org>]

On 12.08.10 09:39, Thomas Fischer wrote:
> That caching in schema is the one that I remembered a month ago. It's a
> pity that the connection is not provided in the constructor QueryDataSet
> (ResultSet resultSet). Thed most difficult task is to create an adequate
> key for caching, as one can connections to multiple databases, and these
> databases can have different table structure. I do not currently have a
> good idea for the caching key.

Actually, there is ResultSet.getStatement().getConnection().

> To summarize, my personal opinion at this point is that the caching is too
> much trouble for a dying project. But if you want to further pursue this I
> can at least offer some thoughts.

I'm scratching my own itch here. I need to apply some Torque stuff to
Postgres and there performance really sucks. I guess, however, even if
we throw Village away some day, we still need some replacement which in
turn needs to have the metadata problem solved. So nothing is lost here.

Bye, Thomas.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

RE: MetaData caching in Village

[Thomas Fischer <fi...@seitenbau.net>]

> I'm currently trying to fix that long outstanding problem of excessive
> meta-data access in Village. I find that I could need some net.wisdom.
> So if someone could try to reproduce/confirm my thoughts I'd be grateful.
>
> - The problem is most probably in Column.populate()

Seems a fair guess, since Column.populate() and Schema.populate() are the
only methods which use the Metadata class.

> - Obviously, some caching takes place in Schema, but it doesn't work for
> QueryDataSets, just for DataSets and TableDataSets

That caching in schema is the one that I remembered a month ago. It's a
pity that the connection is not provided in the constructor QueryDataSet
(ResultSet resultSet). Thed most difficult task is to create an adequate
key for caching, as one can connections to multiple databases, and these
databases can have different table structure. I do not currently have a
good idea for the caching key.

> - I found a hint that it is better to fetch the ResultMetaData from a
> prepared statement that is just prepared but not executed.

Hm if the data is cached and so the metadata is used only once I'd not care
too much about performance.

> - Would it be "legal" to create a PreparedStatement on a connection that
> has other statements/resultsets open?

Sorry, no idea, but see above.

> - I'd like to use the existing caching mechanism, which creates Schema
> objects from "select * from tablename". Would that cover aliased
> columns, too?

Good point. I'd fear you would need the original query for that. But you do
not want to cache alias types. An alias can be an alias for one column in
one case and for another column in another case. And, even worse, probably
you can have an alias name that has the same as an original table column
name:
Presume you have a table author with columns id, name, then  'd guess the
following sql query is valid: select id, id + 1 as name from author, where
you'd fetch the colum type for name from the cache and it would tell you
varchar but in reality it's number. Thats probably the reason why no
caching takes place for queries (and I'm asking myself whether this is
possible)

> - If I don't want to introduce additional dependencies, the lifetime of
> the cache (being a Hashtable) would be the lifetime of the JVM. Some
> ideas for orderly shutdown/invalidation would be helpful.

No idea, village does not know about the torque shutdown event currently,
so I do not think that clearing the cache before jvm shutdown is possible.
But the schema cache has the same problem.

> - Would someone be willing to verify my fixes against different
> databases, namely Sybase, MS-SQL and Oracle?

I'd offer to run tests against oracle 10 if you tell me what I should do.

To summarize, my personal opinion at this point is that the caching is too
much trouble for a dying project. But if you want to further pursue this I
can at least offer some thoughts.

    Thomas


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org