c++ - 有没有办法从此 sql 语句和逻辑中删除 select count(*)

Question

我有一些 C++ 代码可以在行存在时更新列，否则什么都不做。这是我的代码现在的样子，选择计数 (*) 大约需要 15-20 秒。我想让这段代码更有效率。

RunUpdateLockAliveTimeStamp

  sCommand << "update APP_CC_LOCKS set ALIVETIMESTAMP = ";
  sCommand << " SYSTIMESTAMP(7)";
  sCommand << " where TRANSACTIONID = ?";

RunSelectCountDataCubeLock

  sCommand << "select count(*) from APP_CC_LOCKS";
  sCommand << " where (((SCENARIOID=? and YEARID=? and ENTITYID=? and PARENTID=? and VALUEID=?) and (LOCKTYPE = ?)) or (LOCKTYPE = ?)) and (TRANSACTIONID != ?) and ((TIMEINSERTED < (select MAX(TIMEINSERTED) from APP_CC_LOCKS ";
  sCommand << " where TRANSACTIONID = ?)) or ((TIMEINSERTED = (select MAX(TIMEINSERTED) from ";
  sCommand << “ APP_CC_LOCKS where TRANSACTIONID = ?)) and TRANSACTIONID <= ?))";

主要

        DWORD dwIterationNumber = 0;

        // trying to accquire lock
        cStartTime = DateTimeUtil::currentDateTimeAsMilliseconds();

        while (bLockWasAcquired == false)
        {
              // try to accquire lock
              numBlockingRecords = -1;

              hr = cAccessor.RunSelectCountDataCubeLock(csTransID, lScenario, lYear, lEntity, lParent, lValue, &numBlockingRecords);
              xfm_throw_propagate(FAILED(hr), hr);

              // There's no block record then lock is temporary accquired until we resolve collision
              if (numBlockingRecords == 0)
              {
                    // The lock is now accquired
                    bLockWasAcquired = true;
                    break;
              }

              if (dwIterationNumber > g_lNumIterBeforeUpdatingTimeStamp)
              {
                    //csSQLCmd.clear();
                    hr = cAccessor.RunUpdateLockAliveTimeStamp(csTransID);
                    xfm_throw_propagate(FAILED(hr), hr);
              }
        }

Answer 1

我不认为 count(*) 是导致查询变慢的原因，而是在 where 子句中执行的两个子选择。

...((TIMEINSERTED < (select MAX(TIMEINSERTED) from APP_CC_LOCKS where TRANSACTIONID = ?))    
or ((TIMEINSERTED = (select MAX(TIMEINSERTED) from APP_CC_LOCKS where TRANSACTIONID = ?))

可以替换为单个子选择:

((TIMEINSERTED <= (select MAX(TIMEINSERTED) from APP_CC_LOCKS where TRANSACTIONID = ?))

这可能会减少一半的执行时间。