0

I have a big problem with first run query form my SQL Server CE database.

I already applied this optimization Performance and the Entity Framework, but still first query take about 15 sec to run.

Something that I noticed when I run my application for first time first query take about 15 sec to run.

If I close my application and run again, the first query runs immediately. So if I restart my PC and run application again first query take 15 sec to run.

Overall, after two week research on internet I could not find a good way to solve my problem.

I used ANTS Performance Profiler and I noticed my first query takes about 11 sec and form initialization for each page take 4 sec for first time.

I have some questions:

  1. I want to know which resource loaded to Ram when my application start?
  2. why my application is fast in second time run?
  3. how can I load those resource into Ram before application start?
  4. why these resource Remains in Ram until windows restart?

Maybe 15 sec is good but when I run my application from DVD it take 45 sec to run first query.

Edited

I used multiple database for each section of my application.

for example This query Take 11 sec to run form first time:

public void GetContent(short SubjectID)
{
   new QuranOtherEntities(CDataBase.CSQuranOtherEntities))
   {
      CHtmlDesign.HtmlFile = QODB.AdabTbls.First(data => data.ID == SubjectID).Content;
   }
}

Table Structure

enter image description here

KF2
  • 9,887
  • 8
  • 44
  • 77
  • 1
    Can you show us some table structures and your query in question?? Not seeing anything at all isn't really a good start to help you troubleshoot.... – marc_s Dec 09 '12 at 08:48
  • @marc_s:query and table structure added – KF2 Dec 09 '12 at 09:10
  • 1
    And how many rows do you have in that table, roughly? Also: what versions of .NET / Entity Framework / SQL Server CE are you using?? – marc_s Dec 09 '12 at 09:11
  • 1
    Is that your whole Entity Model? Most of the start up time is spend generating the Views. If you have a big model, that could take some time. What performance changes have you seen while using the Code Project article? – Wouter de Kort Dec 09 '12 at 09:14
  • @marc_s: exactly this database contain 10 table in this query i just get a single row that contain about 150 kb string data.i used .net 3.5 and sqlce 3.5.0. – KF2 Dec 09 '12 at 09:19
  • 150kb string data? WHOW. – TomTom Dec 09 '12 at 10:54
  • @TomTom:yeah i saved html string data in my table each field about 150kb – KF2 Dec 09 '12 at 11:45

1 Answers1

1

First(data => data.ID == SubjectID)

Ok, forget entity framework. Grab the generated SQL code, run it through profiler.

This SMELLS like "I have no clue what an index is". Check "Use-the-index-luke.com".

Also make sure that

Maybe 15 sec is good but when I run my application from DVD it take 45 sec to run first query

Actually IS the sql statement. It could easily be loading and initializing your application, in which case you ask a totally irrelevant question here. In this case there is no a lot you CAN do - there still are optimizations etc., but youhave to ask specific questions here, i.e. do your homework and this question here is then totally irrelevant. This includes, btw, the time it takes entity framework to initialize - which is not related to the query per se, but can happen on the first query you run at all.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • exactly my question is not asked good enough,you are right,but i want to know this : ,why my application is fast in second time run? and why these resource Remains in Ram until windows restart? – KF2 Dec 09 '12 at 09:31
  • Well, if that is your question, then ASK IT. In separate questions. not here, in this question - you got an answer for the question you did ask. – TomTom Dec 09 '12 at 10:54