CODEWITHSUNDEEP
informix
cppcoder
cppcoder

Reputation: 23105

Informix DELETE query taking long time

In production, I am facing this problem.

There is a delete which is taking long time to execute and is finally throwing SQL error of -243.
I got the query using onstat -g.

Is there any way to find out what is causing it to take this much time and finally error out?

It uses COMMITTED READ isolation.

This is causing high Informix cpu usage as well.

EDIT

Environment - Informix 9.2 on Solaris

I do not see any issue related to indexes or application logic, but I suspect some informix corruption.
The session holds 8 locks on different tables while executing this DELETE query.
But, I do not see any locks on the table on which the delete is performed.

Would it be something like, informix is unable to get lock on the table?

Upvotes: 1

Views: 1594

Answers (2)

cppcoder
cppcoder

Reputation: 23105

Informix was restarted ungracefully many times, which led to informix instability.
This was the root cause.

Upvotes: 0

Trent
Trent

Reputation: 3103

DELETE doesn't care about your isolation level. You are getting 243 because another process is locking the table while you're trying to run your delete operation.

I would put your delete into an SP and commit each Xth record:

CREATE PROCEDURE tmp_delete_sp (
  p_commit_records INTEGER
) 
RETURNING 
  INTEGER, 
  VARCHAR(64);

DEFINE l_current_count INTEGER;

SET LOCK MODE TO WAIT 5; -- Wait 5 seconds if another process is locking the table.

BEGIN WORK;

FOREACH WITH HOLD
  SELECT .....

  DELETE FROM table WHERE ref = ^^ Ref from above;

  LET l_current_count = l_current_count + 1;

  IF (l_current_count >= p_commit_records) THEN
     COMMIT WORK;
     BEGIN WORK;
     LET l_current_count = 0;
  END IF;

END FOREACH;

COMMIT WORK;

RETURN 0, 'Deleted records';
END PROCEDURE;

Some syntax issues there, but it's a good starting block for you. Remember, inserts and updates get incrementally slower as you use more logical logs.

Upvotes: 1

Related Questions