Query to gather Metadata not optimized

chihebbs · Post by **chihebbs** » Sat 11 Oct 2008 14:40

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.

eduardosic · Post by **eduardosic** » Sun 12 Oct 2008 03:13

You can try

Code: Select all

Show Fields from TableName

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

chihebbs · Post by **chihebbs** » Sun 12 Oct 2008 09:15

The Query is passed to mysql server by MyDAC component. It should be optimized at the component level.

eduardosic · Post by **eduardosic** » Sun 12 Oct 2008 20:16

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.

chihebbs · Post by **chihebbs** » Mon 13 Oct 2008 06:05

The Query is passed to mysql server IMPLICITLY by MyDAC component when TMyQuery.Options.DefaultValues = True

Challenger · Post by **Challenger** » Mon 13 Oct 2008 09:52

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.

chihebbs · Post by **chihebbs** » Mon 13 Oct 2008 17:57

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.

chihebbs · Post by **chihebbs** » Mon 20 Oct 2008 14:12

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.

Challenger · Post by **Challenger** » Wed 22 Oct 2008 07:29

Thank you for assistance. We will consider your suggestion and perhaps implement it in future versions.

chihebbs · Post by **chihebbs** » Sun 06 Sep 2009 02:20

Any News about the optimalization ?

Dimon · Post by **Dimon** » Mon 07 Sep 2009 08:23

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.

chihebbs · Post by **chihebbs** » Mon 07 Sep 2009 11:11

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.

Dimon · Post by **Dimon** » Tue 08 Sep 2009 09:18

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.

chihebbs · Post by **chihebbs** » Sun 20 Sep 2009 04:14

Hi Dimon, as advised, I sent you a small example. Hope to hear from you soon. Thank you

Dimon · Post by **Dimon** » Mon 21 Sep 2009 11:48

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.

Devart Forums

Query to gather Metadata not optimized

Query to gather Metadata not optimized

Re: Query not optimized to gather Metadata

Re: Query to gather Metadata not optimized