SQL using a lot of CPU

Question

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 ?

What do you mean "failing"? Is it taking lots of CPU/time, or are you getting an error? How many rows are in the table being SELECTed? It might help to see the updating code, too. (maybe you are leaving a transaction open...) — Andrew Barber
– Andrew Barber, Commented Nov 26, 2011 at 0:40
I got an email from my hosting company saying "It has come to our attention that your website is running a query on our SQL server that is using a large amount of CPU. The site starts off ok but after some time the query continues on the SQL server. From my experience it appears as though a connection to the database server is not being closed and thus each connection never closes." — Daveo
– Daveo, Commented Nov 26, 2011 at 0:57
That's not a terribly helpful report from them; I would even suspect whether the query they think is causing the trouble really is, from that. Do they mean a single query - just one instance - is running constantly, but is OK for a while... but eventually causes high CPU, or do they mean the query runs multiple times OK, but then suddenly starts not running OK? What about the application calling this query? Is it experiencing errors? — Andrew Barber
– Andrew Barber, Commented Nov 26, 2011 at 1:03
With 300,000 rows (not very many), I wouldn't be too quick to assume indexing is the problem. Yes; you need proper indexing, but unless this query is running very frequently, I wouldn't see it bringing the whole server to its knees. — Andrew Barber
– Andrew Barber, Commented Nov 26, 2011 at 1:04

BrokenGlass · Accepted Answer · 2011-11-26 00:21:54Z

1

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.

edited Nov 26, 2011 at 0:21

answered Nov 26, 2011 at 0:16

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daveo Over a year ago

I have added an index to that field thanks for the reminder. I will try that for now if that still fails I can try the batching

Reza ArabQaeni · Accepted Answer · 2011-11-26 00:22:10Z

0

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

answered Nov 26, 2011 at 0:22

Reza ArabQaeni

4,91830 silver badges48 bronze badges

2 Comments

Daveo Over a year ago

Not possible as I am syncing about 20 tables some with 200,000 rows in them I do not want to load 1 million rows into memory. I may however try like BrokenGlass suggested and batch them in groups of 50 or so.

Reza ArabQaeni Over a year ago

If 1 million rows exist to work with them, it is better to use batch load.

Collectives™ on Stack Overflow

SQL using a lot of CPU

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related