locked
Visual Studio 2017 .edmx - Update Model from Database painfully slow RRS feed

  • Question

  • I'm building a new application using Entity Framework 6 database first in Visual Studio 2017. At first, updating the .edmx from my database only took a matter of seconds, but recently (we now have around 50 tables) it has become painfully slow. Even if there are no chagnes to the database and I run "Update Model from Database", it takes 3.75 minutes. That is becoming a productivity killer. Is this the expected performance? Is there any way to speed it up?
    Monday, October 23, 2017 9:25 PM

Answers

  • We're up to 65 in the database with, I think, 63 in the model. I'm running an Intel Core m7-6Y75 processor with 16 GB ram. While updating, the CPU usage is only hovering around 20% and RAM isn't through the roof either. It's looks like it's just taking its sweet time. 

    Shortly after opening a support ticket with Microsoft yesterday, we stumbled upon a fix/work-around: Setting the database property Legacy Cardinality Estimation to ON. This allowed mine to run in 12 seconds instead of over 5 minutes. I'm told setting the compatibility level to Sql Server 20112 (110) also works, but I haven't tried it. I'll post back if MS has a different official answer.




    • Edited by _xr280xr_ Friday, November 3, 2017 5:28 PM
    • Proposed as answer by Javier PT Wednesday, March 28, 2018 10:05 AM
    • Marked as answer by _xr280xr_ Wednesday, March 28, 2018 2:14 PM
    Friday, November 3, 2017 5:27 PM

All replies

  • Hi friend,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about ADO.NET Entity Framework and LINQ to Entities. Since our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum to seek for a better support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 24, 2017 6:01 AM
  • What version of SQL Server are you using? In using Google on your particular problem, there seems to be a ways to resolve the issues of slow update of the model using DB first, starting with applying service packs to SQL server.

    Are you using MS SQL Server Express on the development machine with DB on the dev machine and updating the model there?

    Tuesday, October 24, 2017 1:06 PM
  • Thank you Sara and DA924x. I'm using a local instance of SQL Server 2016 sp1. The only thing I could find in my search was referring to older versions of Visual Studio and bugs that had already been fixed. I do have a cumulative update for sp1 downloaded that I need to install. I will try that ASAP, check if there is any change and post back here with the results.
    Tuesday, October 24, 2017 6:06 PM
  • After installing the sp1 cumulative update, I added some more tables and it took 5:35 to update the edmx and another minute for the designer to update. Is there any way to log what is going on to determine where the slowness is occurring? It's very concerning that by adding 5 new tables it slowed it down by 2 minutes and this project is still new. I also learned it's taking my co-worker's computer over 10 minutes when he updates it.
    Thursday, November 2, 2017 10:35 PM
  • How many tables are on the model? And with VS now of days, one has to have a computer that has the horsepower to work with it effectively where things are not slow due to the computer doesn't meet the specs on CPU speed.

    I know of nothing that can tell where the bottleneck could be if any, unless you are using something like Process Explorer where one can drill down into a running process and see what is happening. 

    Friday, November 3, 2017 1:14 PM
  • We're up to 65 in the database with, I think, 63 in the model. I'm running an Intel Core m7-6Y75 processor with 16 GB ram. While updating, the CPU usage is only hovering around 20% and RAM isn't through the roof either. It's looks like it's just taking its sweet time. 

    Shortly after opening a support ticket with Microsoft yesterday, we stumbled upon a fix/work-around: Setting the database property Legacy Cardinality Estimation to ON. This allowed mine to run in 12 seconds instead of over 5 minutes. I'm told setting the compatibility level to Sql Server 20112 (110) also works, but I haven't tried it. I'll post back if MS has a different official answer.




    • Edited by _xr280xr_ Friday, November 3, 2017 5:28 PM
    • Proposed as answer by Javier PT Wednesday, March 28, 2018 10:05 AM
    • Marked as answer by _xr280xr_ Wednesday, March 28, 2018 2:14 PM
    Friday, November 3, 2017 5:27 PM
  • I am glad it's working out for you. :)
    Friday, November 3, 2017 6:54 PM
  • http://www.sqlconsulting.com/news1701.htm

    The SQL2016 Cardinality Estimator has ... issues

    Monday, January 15, 2018 9:31 AM