DBVis 6.5 slow Object Tree (Tables List) on a MySQL 5.0

I just Updated to DBVis 6.5 and had to notice that you guys still have not improved the performance of the Tables List in the tree. Anyway, I really love DBVis and even more I love the way it handles Database Profiles. I've tweaked the MySQL profile to use a not use a HEAVY query on the information schema to retrieve a list of Table or View names. The current 6.5 version uses the following Query to retrieve the list of Table Names: SELECT TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS FROM information_schema.tables WHERE table_schema = 'mysql' AND table_type NOT IN ('view') ORDER BY TABLE_NAME On a not so small Database this query could take several seconds... (4,016s for me) The information_schema in MySQL in known to be damned slow... So I replaced the Query to get the Tables Names for the Object Tree with the following: SHOW FULL TABLES FROM `mysql` WHERE `table_type` NOT IN ('view') This is pretty much faster... (only 0.015s for me) Anyway... I have attached a unified-diff and a patched version of the mysql.xml profile. Maybe someone finds it helpfull ;-) I also replaced the DDL Tab (create Table Definition) because the "DDL Viewer" showed an incorrect Create Table statement regarding foreign keys... also the SQL formater is not so good in displaying CREATE TABLE statements. btw: I have a little Déjà vu, some time ago (~2 years) I posted "DBVis 5.1 slow Object Tree (Tables List) on a MySQL 4.1" http://www.minq.se/forum/message.jspa?messageID=5700

Hi Mathias, > I just Updated to DBVis 6.5 and had to notice that you guys still have not improved the performance of the Tables List in the tree. > > Anyway, I really love DBVis and even more I love the way it handles Database Profiles. I've tweaked the MySQL profile to use a not use a HEAVY query on the information schema to retrieve a list of Table or View names. > [...] > > SHOW FULL TABLES FROM `mysql` WHERE `table_type` NOT IN ('view') > > This is pretty much faster... (only 0.015s for me) Thanks for this tips. The last time you suggested this you did not include the FULL keyword and the WHERE clause, which caused it to include Views in the result as well. I was not aware of these extensions in 5.x, so I will look at getting this into the profile for 5.x (the extensions do not work for older versions of MySQL). > [...] > I also replaced the DDL Tab (create Table Definition) because the "DDL Viewer" showed an incorrect Create Table statement regarding foreign keys... also the SQL formater is not so good in displaying CREATE TABLE statements. Can you please describe the problem you see when you use the default DDL tab definition? If there is an error, we will correct it. > [...] Best Regards, Hans

Hi Hans, my main Issue with the DDL viewer was that it was missing Keys for Foreign Key Constraints, that seems to be fix now as I see for each Foreign Key CONSTRAINT an related INDEX. Anyway, I still have the following issues with it: - not quoted identifiers (tablename, columns, keys) - implicit default values are not shown - SQL Formater doesn't handle ON DELETE / ON UPDATE for Foreign Keys - and I prefer the MySQL slang of "KEY" and "UNIQUE KEY" instead of "INDEX" and "CONSTRAINT ... UNIQUE" Apart from this, I think if a DBMS supports a command to get the blueprint of a database object (like SHOW CREATE TABLE...) it should be used... As the DBMS is the only one who really knows how the object could be created. That should not be in the hands of an outside application... The attached "Bild-0009.png" shows the DDL with the Original mysql.xml, and "Bild-0010.png" shows my modified version. wich uses "SHOW CREATE TABLE ..." Best Regards, Mathias

Hi Mathias, > [...] > Anyway, I still have the following issues with it: > - not quoted identifiers (tablename, columns, keys) This is controlled via Tool Properties for the database, in the Delimited Identifiers category. So you can have it either way. > - implicit default values are not shown True. > - SQL Formater doesn't handle ON DELETE / ON UPDATE for Foreign Keys I agree that it formats these in an odd way. > - and I prefer the MySQL slang of "KEY" and "UNIQUE KEY" instead of "INDEX" and "CONSTRAINT ... UNIQUE" That's fair, but I bet there are others who prefer the more standard terms. > Apart from this, I think if a DBMS supports a command to get the blueprint of a database object (like SHOW CREATE TABLE...) it should be used... As the DBMS is the only one who really knows how the object could be created. That should not be in the hands of an outside application... I see your point, but there are many databases that do not provide a command for this. DbVisualizer also need a mechanism for creating the DDL from metadata. This allows us to provide features like the Alter Table dialog (need to programatically find the difference between the current DDL and the desired DDL to generate ALTER statements), and eventually a tool for comparing tables in different schemas, among other things. But you are of course free to edit the profile and use your own DDL tab. > The attached "Bild-0009.png" shows the DDL with the Original mysql.xml, and "Bild-0010.png" shows my modified version. wich uses "SHOW CREATE TABLE ..." Thanks. I have compared them and I cannot see that there are any errors in the DDL generated by DbVisualizer, only the formatting differences and the omission of the implicit defaults you mentioned. Best Regards, Hans

Hi Mathias, For your info, I have integrated your suggestion about using SHOW FULL TABLES FROM `mysql` WHERE `table_type` NOT IN ('view') instead of the SELECT statement. It will be included in the next release. Thanks for the tip, Hans