none
Entity Framework updates Extremely SLOW unless 2012 Compatibility

    Question

  • We have a 2016 SQL Server database.  We have to update our edmx model from database.  The Update Model from Database and the Update Wizard takes a painfully long time unless we first go into the Sql Server 2016 properties and options and set the Compatibility level to SQL Server 2012 (110).  Three developers are experiencing this issue and after publishing a 2016 Sql Server database always need to go in and change the Compatibility level to 2012 from 2016.  We have one developer that updates on his local box with it set to Sql Server 2016 and isn't seeing this issue.

    There must be a setting that we're missing - anyone know or have an idea? 

    TIA

    Harry

    Wednesday, February 07, 2018 3:43 PM

Answers

  • Hi Harry,

    >> The Update Model from Database and the Update Wizard takes a painfully long time unless we first go into the Sql Server 2016 properties and options and set the Compatibility level to SQL Server 2012 (110).  

    Based on my understanding, the above happens due to cardinality estimator in SQL Server 2016. The CE updates for levels 120 and above incorporate assumptions and algorithms that work well on modern data warehousing and on OLTP workloads. For detailed information, please refrt to the following:

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

    SQL Server 2016 new features to deal with the new CE

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.

    Thursday, February 08, 2018 3:15 AM
  • There is actually a setting in the database project properties now that you can set Cardinality to ON so that every build will set it to on for that database.
    • Marked as answer by Mimosa Arts Wednesday, February 14, 2018 7:37 PM
    Wednesday, February 14, 2018 7:36 PM

All replies

  • What's your version of EF, perhaps that could use an update too?

    Lots of tools do a lot of back and forth, that might be faster on a local box.  Hey maybe that's how Microsoft tested it, lol.

    Josh



    • Edited by JRStern Wednesday, February 07, 2018 5:07 PM
    Wednesday, February 07, 2018 5:06 PM
  • Hi Harry,

    >> The Update Model from Database and the Update Wizard takes a painfully long time unless we first go into the Sql Server 2016 properties and options and set the Compatibility level to SQL Server 2012 (110).  

    Based on my understanding, the above happens due to cardinality estimator in SQL Server 2016. The CE updates for levels 120 and above incorporate assumptions and algorithms that work well on modern data warehousing and on OLTP workloads. For detailed information, please refrt to the following:

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

    SQL Server 2016 new features to deal with the new CE

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.

    Thursday, February 08, 2018 3:15 AM
  • Thanks Hannah

    Basically:

    Turn on the Legacy Cardinality Estimation and leave the Compatibility level set to 2016

    How would I do this in a post SQL statement such that it would set this each time I publish the database?


    Thanks

    Harry

    Monday, February 12, 2018 3:57 PM
  • Hi Harry,

    >> How would I do this in a post SQL statement such that it would set this each time I publish the database?

    As far as I’m concerned, ALTER DATABASE SCOPED CONFIGURATION enables several database configuration settings at the individual database level, and I assume that you’ll need to set this configuration when a database is published. Or you can use its equivalence TRACE FLAG 9481 when an instance of SQL Server 2016 starts.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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, February 13, 2018 6:49 AM
  • There is actually a setting in the database project properties now that you can set Cardinality to ON so that every build will set it to on for that database.
    • Marked as answer by Mimosa Arts Wednesday, February 14, 2018 7:37 PM
    Wednesday, February 14, 2018 7:36 PM