locked
Slow first query RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    I wonder if there is a way to make the first query response faster in EF6. I think on the first query EF compiles the model that's why it takes so much.

    Thank you.

    Thursday, September 5, 2019 7:05 PM

All replies

  • User283571144 posted

    Hi cenk1536,

    I wonder if there is a way to make the first query response faster in EF6. I think on the first query EF compiles the model that's why it takes so much.

    As far as I know, if you are using EF 6.2, you could use a Model Cache which loads a prebuilt edmx when using code first; instead, EF generates it on startup. This will make the first query faster.

    More details, you could refer to below codes:

    public class MyDbConfiguration : DbConfiguration
    {
        public MyDbConfiguration() : base()
        {
            var path = Path.GetDirectoryName(this.GetType().Assembly.Location);
            SetModelStore(new DefaultDbModelStore(path));
        }
    }

    Best Regards,

    Brando

    Friday, September 6, 2019 5:36 AM
  • User-1104215994 posted

    Is there any side effect of using the cache you suggested?

    Friday, September 6, 2019 5:45 AM
  • User283571144 posted

    Hi cenk1536,

    In my opinion. there is no side effec.By default Entity Framework(not Core) will generate an EDMX behind the scenes at startup. If you have a rather large EF model, it can take a lot of time. The above codes compares the timestamp between the assembly of your context against the edmx.  If they do not match, the model cache is deleted and rebuilt.

    Best Regards,

    Brando

    Friday, September 6, 2019 6:14 AM
  • User-1104215994 posted

    I tried your sample code in my dev laptop with IIS express on <g class="gr_ gr_261 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="261" data-gr-id="261">debug</g> mode but nothing changed I guess. First response takes 9 seconds but after that it takes 500ms.

    Friday, September 6, 2019 6:43 AM
  • User-1104215994 posted

    I changed the code as follows:

     public MyDbConfiguration() : base()
            {
                SetModelStore(new DefaultDbModelStore(Directory.GetCurrentDirectory()));
            }

    And getting access denied to IIS express <g class="gr_ gr_45 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="45" data-gr-id="45">edmx</g>.

    Friday, September 6, 2019 7:18 AM
  • User-1104215994 posted

    In the production <g class="gr_ gr_21 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="21" data-gr-id="21">server</g> this code worked but nothing changed. <g class="gr_ gr_64 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="64" data-gr-id="64">Still</g> first response took more time.

    public class MyDbConfiguration : DbConfiguration
    {
        public MyDbConfiguration() : base()
        {
            var path = Path.GetDirectoryName(this.GetType().Assembly.Location);
            SetModelStore(new DefaultDbModelStore(path));
        }
    }

    Friday, September 6, 2019 5:41 PM
  • User-1104215994 posted

    I also tried this on the production server but still the same. First response takes 10 seconds, others 1500ms.

    public MyDbConfiguration() : base()
            {
                SetModelStore(new DefaultDbModelStore(Directory.GetCurrentDirectory()));
            }

    Saturday, September 7, 2019 7:56 AM
  • User283571144 posted

    Hi cenk1536,

    I guess your EF model is very big, so it takes so long time. I suggest you could also consider generating Pre-compiled Views to speed up your EF query.

    View generation can have a significant enhancement on startup performance. 

    Details about how to use it, you could refer to this article:

    Pre-generated mapping views

    Best Regards,

    Brando

    Monday, September 9, 2019 1:58 AM
  • User-1104215994 posted

    It is not big actually. My application pool <g class="gr_ gr_93 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="93" data-gr-id="93">recycle</g> every day at 02:00 AM and after <g class="gr_ gr_83 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="83" data-gr-id="83">that</g> the first response takes 15 seconds.

    Monday, September 9, 2019 8:20 AM
  • User283571144 posted

    Hi cenk1536,

    According to the MSDN article:

    One of the biggest drags on performance is the startup time involved with the first use of a context in an application process. You can do a lot to improve that startup time, though. Hopefully you’ve already learned these tricks from my own writing or other resources, such as the MSDN doc on performance considerations at bit.ly/3D6AiC.

    A startup step that often hampers performance is the view gener­ation of mapping views, where EF creates the relevant SQL to query against each of the entity sets in the model. These views get leveraged as your app runs so that for certain queries, EF doesn’t have to work out the SQL on the fly. View generation happens whether you created your model with the EF Designer or with Code First.

    You can pre-generate these views and compile them into the application to save time. 

    More details, you could refer to this article:

    Best Regards,

    Brando

    Tuesday, September 10, 2019 5:45 AM