MySQL Bugs: #48791: ADO.NET Entity Data Model Wizard Taking Hours

Bug #48791	ADO.NET Entity Data Model Wizard Taking Hours
Submitted:	16 Nov 2009 6:06	Modified:	26 May 2011 18:22
Reporter:	passsha passsha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.2.1	OS:	Windows
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Hello,

I'm currently working with Visual Studio 2008 and the ADO.NET Entity Data Model Wizard. I'm connecting to a MySQL database using the MySQL/Connector plug in available. I have a database with 63 tables, each with foreign key constraints on several columns.

I'm working on the data access layer for a multi-tier system. The ADO.NET Entity Data Model Wizard is taking hours to update the edmx file from the database. This happens with the initial import the wizard performs and also when I need to "Update model from database". This means every time I make a small change to the database I have to wait for 2-3 hours for the model to update.
The database engine is InnoDb
This only seems to happen if I have foreign key constraints enabled.

How to repeat:
You should have a database with large amount of tables more and they must have a foreign keys constraints. Run Visual Studio 2008, run Entity Data Model Wizard and try to add all tables from the database.

Hi Passsha and thanks for your report.

Is it possible for you to attach mysqldump of database in question (I think I do not need the actual data) so I can load it and test? Also, what is your MySQL server version and environment? Is MySQL server on same box?

I have tried to use different workstaions and operation systems such as MS Windows Xp SP2 and SP3, Microsoft Windows 7 also. All of workstations have dual-core CPU and more then 2GB of RAM. MySQL Server version is 5.0.45 it is located on the same workstation. And I also tried to use remote server its MySQL version is 5.0.27.

Using provided script with attached project (both private as they contain sensitive info) I am able to verify this. The time needed to update model scales up drastically with every new entity introduced, not possible to work with at all.

Good evening! 
Is there any solution? It's not possible to wait for more than 3 hours. We can't work at all. 
Regards Pavel.

We have encountered the same problem. In our database (InnoDB) there are 130 tables with foreign keys, and model generation now takes about three hours…

Can you help us to understand and to solve this problem?

Thank you in advance!
Ivan Shkuropadsky (Russia)

Hello again!
May be the following information would be helpful for you to localize this bug...

We tried to use Model Generator ("edmgen.exe" from Microsoft) without Wizard. 
Running "%windir%\Microsoft.NET\Framework\v3.5\edmgen.exe" takes ONLY FEW seconds for our database that contain more than 130 tables now!
This can get us intermediate results: set of standard files for Entity Framework (*.csdl, *.msl, *.ssdl and *.cs files).
(But there are no known ways for us to generate *.edmx automatically from these files...)

So we suppose, that this bug concerns those steps of model generating, which follow the database analysis (may be constructing of the diagram to get "EF Designer content"?)

Hope, this information can help you to solve the problem.
Sincerely yours,
Ivan Shkuropadsky (Russia)

P.S. Command-line instructions for base Entity Framework files generation (*.csdl, *.msl, *.ssdl and *.cs files):
______________________________________________________________________
"%windir%\Microsoft.NET\Framework\v3.5\edmgen.exe" /mode:FullGeneration /project:Test /provider:MySql.Data.MySqlClient /c:"server=localhost;User Id=root;Password=pass;Persist Security Info=True;Connect Timeout=300000;Default Command Timeout=300000;database=test" /entitycontainer:TestEntities /namespace:TestModel /language:CSharp

"%windir%\Microsoft.NET\Framework\v3.5\edmgen.exe" /mode:EntityClassGeneration /incsdl:.\Test.csdl /outobjectlayer:.\Test.Objects.cs /language:CSharp

Thanks for workaround Ivan.

To all, I will try to escalate this problem.

This is not a  bug per se in the connector. Yes, it is taking a long time and yes that is undesireable but it is the server that is taking a long time.  The wizard generates several queries during it's operation.  I have attached the first query that I found that took a great deal of time (nearly 2 hours).  There may be others that take a great deal of time.  The real problem here is the fact that MySQL cannot execute this query any faster.  This may be due to very slow information schema tables which are used extensively.

We will continue to look at this and look for ways that we can speed this us.  We will also talk to the server guys and try to identify what about this query is taking a long time.

This is a huge problem for us too.  Are there any updates or workarounds for this matter besides the command line workaround?

Hi, 

is there any update on this situation?

To complement Ivan Shkuropadsky post:

http://blogs.msdn.com/b/dsimmons/archive/2007/12/07/how-to-use-your-existing-csdl-msl-ssdl...

Sorry for marking this as not a bug back then.  The assumption was that heavy use of IS tables was causing the problems.

Fixed in 6.0.7, 6.1.5, 6.2.4, 6.3.6, 6.4.0

Noted in 6.0.7, 6.1.5, 6.2.4, 6.3.6, 6.4.0 changelogs.

In the ADO.NET Entity Data Model Wizard, the time to update a model 
scaled abnormally as the number of entities increased. 

CHANGESET - http://lists.mysql.com/commits/138251