When update statement, the execute time sometime taking 60 second sometimes taking 1-2 sec. Total row count around 18 million. CARDID is our PK. Almost each 2-3 sec our system send a request to that query. You can find the query on below;
UPDATE C
SET
[CARDTYPE] = @NEWCARDTYPE,
[ICPGROUPID] = @NEWICPGROUPID,
[CARDNUMBER] = @NEWCARDNUMBER,
[PINCODE] = @NEWPINCODE,
[SCODE] = @NEWSCODE,
[STATUS] = @NEWSTATUS,
[CUSTOMERID] = @NEWCUSTOMERID,
[PROFILEID] = @NEWPROFILEID,
[BALANCE] = @NEWBALANCE,
[BALANCETYPE] = @NEWBALANCETYPE,
[DECIMALS] = @NEWDECIMALS,
[CURRENCY] = @NEWCURRENCY,
[STARTDATE] = @NEWSTARTDATE,
[EXPIRATIONDATE] = @NEWEXPIRATIONDATE,
[PINCOUNT] = @NEWPINCOUNT,
[PINDATE] = @NEWPINDATE,
[INITIALPIN] = @NEWINITIALPIN,
[INITIALSCODE] = @NEWINITIALSCODE,
[UPDATEDATE] = GETDATE(),
[ACTIVATIONDATE] = @NEWACTIVATIONDATE,
[REGISTERDATE] = @NEWREGISTERDATE,
[UNREGISTERDATE] = @NEWUNREGISTERDATE,
[ORDERID] = @NEWORDERID,
[RELATEDCARD] = @NEWRELATEDCARD,
[LOADCOUNT] = @NEWLOADCOUNT,
[REDEEMCOUNT] = @NEWREDEEMCOUNT,
[ISCHANGED] = 1,
[ISPROCESSED] = 0
FROM dbo.[CARD] C
WHERE
CARDID = @CARDID
Also we have some indexes on the that table. What can we do? We are really stuck. We added one filter more but it did not work.