Query with insert statement takes around 30 minutes

praba_jas · New User Joined: 01 Apr 2005 Posts: 11

Hi All,

My requirement is as follows:

We have a job which has around 10 inserts into table X. Out of the 10 inserts one particilar insert takes around 30 minutes and the rest together takes 30 minutes. Total execution time of the job is 1 hr.

If we split the job into two, the query which takes more query in JOB1 and the rest in JOB2, will the execution time get reduced?

I have read that the table is locked in exclusive mode during insert. If so then JOB2 has to wait till for JOB1 to finish. Is it true?

Regards,
Jasmine

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

How many rows does this rows have?
How many indexes does this table have?
Are other applications reading this table while inserts are going on, if so do those applicatoins perform commits at regular interval.

If table you are inserting is a partioned table then you can create jobs seperated by partioning key ( or a set of partioning keys). Sort/distribute data by same partioning key.

If you go with above suggested way, also check application if it has any other statements, SELECT, DELETE and these better be using whole clustering index key in WHERE clause. Otherwise split jobs will be contending with each other.

dick scherrer · Posted: Fri Dec 08, 2006 3:12 am

Hello,

How is an insert defined?

One (1) row inserted into any database should be sub-second.

What kind of processing does the program have to do before issuing the insert?

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Check when was the last RE-ORG, RUNSTATS done on the table which is taking long time
If the stats are not up to date,the performance decreases.

don't forget to Rebind the program after the Re-org & Runstats.

Thanks,
Ravi