0

I am facing a performance issue with an update statement. It takes too long to update, but when I perform a select on that updating table it completes very fast.

UPDATE mo
   SET mo.RNCNEID    = ts.RNCID,
       mo.SiteNEID   = ts.SiteID,
       mo.SectorNEID = ts.SectorID
FROM COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR AS mo
 INNER JOIN COPS_UMTS_HUAWEI.Aggregation.tsector   AS ts  
     ON mo.rncname      = ts.rncname
    AND mo.cellid       = ts.cid
    AND ts.VendorID     = 6
    AND ts.TechnologyID = 2
WHERE mo.RNCNEID IS NULL;

After running the below query:

SELECT COUNT(*)
FROM   COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR WITH (NOLOCK)
WHERE  RNCNEID IS NULL;

...it completes very fast.

  • 1
    Please consider reading about Asking query performance questions – mustaccio Jan 3 at 15:21
  • You might want to check for large 'children' tables with foreign keys pointing to the columns you are trying to update. – Scott Hodgin Jan 3 at 16:00
  • Another possibility that I've seen before is an indexed view that makes use of the table being updated.In those cases, the view has to be re-run. Also what@ScottHodgin mentioned, checking any foreign key constraints can be problematic as well. – BCM Jan 3 at 16:41
  • Try it again, but this time remove the with(nolock) form the query. If it doesn't end fast so you are getting slowed down by locks – jean Jan 3 at 16:51
1

It takes too long to update but when I do select on that updating table its completes very fast .

If the UPDATE query is taking a long time, but the SELECT part is fast when run on its own, then one possible reason is that the UPDATE is getting blocked due to incompatible locks being held by another session.

You can check for this while UPDATE query is running by, for example, running sp_WhoIsActive:

EXEC sp_WhoIsActive;

screenshot of sp_whoisactive results showing wait_info with lck_m_x waits

In this screenshot, I'm trying to run an UPDATE in session_id 57, but it's blocked by another session that's holding locks that my query needs. You can see this in the wait_info and blocking_session_id columns, which show LCK_M_X waits (which is an exclusive lock, usually required by another UPDATE query running) and 53 (the other session that is holding the lock).

After running the below query...Query completes very fast .

Running that count(*) query will likely make sure all of the pages needed for the query are in memory (meaning it will be faster, because they won't have to be read from disk first). This probably just masking the blocking issue, as the blocking query finishes faster when the pages are in memory.

| improve this answer | |
  • I have verified there is no blocking while update query is executing .I want to know why update is not able to get required data in Cache but select is able to do so ? – Kamran Lari Jan 6 at 7:44
  • Version used - SQL SERVER 2008 R2, table tHuawei_CELL_Ncell_DY_HR has around 26 million records, table tsector has 40K records. It seems to me after doing select with (nolock) and predicate same as update query statistics is created , which is helping the update statement but I am not sure about it how a executing query can consider the new statistics and change the plan to execute the statement efficiently . – Kamran Lari Jan 6 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.