CODEWITHSUNDEEP
sql-serverperformanceentity-frameworkado.net
Daveo
Daveo

Reputation: 19872

SQL using a lot of CPU

I have a database sync job where I loop over 1000s of rows in XML and update DB tables with any changes found in the XML using EF4.1

My code is :

using (context = new MyDbContext())
 {
      foreach (var row in document.Elements("row"))
      {
           //DO UPDATING IN HERE.
           //Part of which I call
            var results = context.Set(type).SqlQuery("select top 1 * from " + type.Name + " where ExternalId=@p0", id);  // this is the SQL running too long
              return results.Cast<MyObject>().FirstOrDefault();
        }
}

The "Select top 1..." query seems to be the one failing. What could be the problem. Should I have my context declared within the For Loop ?

Upvotes: 1

Views: 285

Answers (2)

Reza ArabQaeni
Reza ArabQaeni

Reputation: 4907

Recommended to first load all data from database and then update each entity in foreach on result collection.

Upvotes: 0

BrokenGlass
BrokenGlass

Reputation: 160902

One explanation I can see for this being slow would be that you don't have an index in on ExternalId in the table you are querying - so you are performing a full table scan instead of an index lookup.

Your query currently doesn't make much sense though - you have a foreach but return the first result that you are loading - why the loop then?

Another problem if running this in a loop is that you are doing N database round-trips which are very costly. If you are just trying to load a set of entities that match some ids found in your XML I would load them in batches instead. Just do a Contains() query and pass in an array of ids.

Upvotes: 1

Related Questions