Page 1 of 1
Query to gather Metadata not optimized
Posted: Sat 11 Oct 2008 14:40
by chihebbs
Hello, I noticed that this query is passed to mysql server to gather Metadata about tables columns "SELECT cast('' as char(1)) TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LENGTH, NUMERIC_PRECISION DATA_PRECISION, NUMERIC_SCALE DATA_SCALE, (case IS_NULLABLE when 'YES' then 1 else 0 end) NULLABLE, COLUMN_DEFAULT DEFAULT_VALUE FROM information_schema.COLUMNS WHERE TABLE_NAME = 'realtablename' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION". Since a server can contain several (in our case more 100) schemas with the same table name, the gathered rows can be very big. is it possible that the query is more specific and include in in the where part of it " and TABLE_SCHEMA='realschemaname', so it does not return unwanted column names from other tables not in the required schema.
Thanks in advance you for your reply.
Re: Query not optimized to gather Metadata
Posted: Sun 12 Oct 2008 03:13
by eduardosic
You can try
or
Code: Select all
SELECT
cast('' as char(1)) TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH DATA_LENGTH,
NUMERIC_PRECISION DATA_PRECISION,
NUMERIC_SCALE DATA_SCALE,
(case IS_NULLABLE when 'YES' then 1 else 0 end) NULLABLE,
COLUMN_DEFAULT DEFAULT_VALUE
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'DataBaseName' AND
TABLE_NAME = 'TableName'
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
with mi work's fine
Posted: Sun 12 Oct 2008 09:15
by chihebbs
The Query is passed to mysql server by MyDAC component. It should be optimized at the component level.
Posted: Sun 12 Oct 2008 20:16
by eduardosic
chihebbs wrote:The Query is passed to mysql server by MyDAC component. It should be optimized at the component level.
If you are using TMyMetadata you can make this:
Code: Select all
MyMetaData1.Close;
MyMetaData1.Restrictions.Values[ 'TABLE_SCHEMA' ] := 'DataBaseName';
MyMetaData1.Open;
please, see MyDAC Help.
Posted: Mon 13 Oct 2008 06:05
by chihebbs
The Query is passed to mysql server IMPLICITLY by MyDAC component when TMyQuery.Options.DefaultValues = True
Posted: Mon 13 Oct 2008 09:52
by Challenger
Thank you for information. We will fix this in the next build of MyDAC. As a temporary solution you can specify schema explicitly in the table name.
Posted: Mon 13 Oct 2008 17:57
by chihebbs
Thanks challenger. We will wait for the next build. Our application does not permit to know which database beforehand, because the concrete Schema is picked up on the fly according to certain criteria. I hope that the next build will be soon.
Posted: Mon 20 Oct 2008 14:12
by chihebbs
Maybe to Optimize more gathering of Metadata, the Result Set of the query sent to Mysql server to gather information about columns should be Cached for future use, since seldom tables would be changing between queries in real life applications and it would cost a lot less to query the Status of the table from information_schema.`TABLES` (CREATE_TIME,UPDATE_TIME columns), if the table structure has changed since the last query, than to ask for the details of all columns for each query. Thank you in advance for your answer.
Posted: Wed 22 Oct 2008 07:29
by Challenger
Thank you for assistance. We will consider your suggestion and perhaps implement it in future versions.
Posted: Sun 06 Sep 2009 02:20
by chihebbs
Any News about the optimalization ?
Posted: Mon 07 Sep 2009 08:23
by Dimon
We have investigated this probelm and have not found causes to cache the metadata information. Please describe in more detail when you need this functionality.
Re: Query to gather Metadata not optimized
Posted: Mon 07 Sep 2009 11:11
by chihebbs
chihebbs wrote:Hello, I noticed that this query is passed to mysql server to gather Metadata about tables columns "SELECT cast('' as char(1)) TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LENGTH, NUMERIC_PRECISION DATA_PRECISION, NUMERIC_SCALE DATA_SCALE, (case IS_NULLABLE when 'YES' then 1 else 0 end) NULLABLE, COLUMN_DEFAULT DEFAULT_VALUE FROM information_schema.COLUMNS WHERE TABLE_NAME = 'realtablename' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION". Since a server can contain several (in our case more 100) schemas with the same table name, the gathered rows can be very big. is it possible that the query is more specific and include in in the where part of it " and TABLE_SCHEMA='realschemaname', so it does not return unwanted column names from other tables not in the required schema.
Thanks in advance you for your reply.
It is not about caching the results, please read the quoted description. As far as 5.80.0.48 is concerned nothing has been done to fine-graine the implicit query.
Posted: Tue 08 Sep 2009 09:18
by Dimon
MyDAC uses a schema name for getting the metadate information.
I can not reproduce the problem. Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill a table.
Posted: Sun 20 Sep 2009 04:14
by chihebbs
Hi Dimon, as advised, I sent you a small example. Hope to hear from you soon. Thank you
Posted: Mon 21 Sep 2009 11:48
by Dimon
Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.