> CREATE TABLE STK_STOCKTRANSACTION (
> ID INTEGER DEFAULT 0 NOT NULL,
> TRANSACTIONTYPE VARCHAR(6),
> STOCKCODE VARCHAR(25),
> SALESORDERNO VARCHAR(15),
> UOM VARCHAR(3),
> REFNO VARCHAR(15),
> BINLOCATION VARCHAR(8),
> ISACTVE VARCHAR(1),
> ACCOUNTCODE VARCHAR(8),
> TRANSACTIONDESC VARCHAR(60),
> TRANDTETME TIMESTAMP,
> PERIOD INTEGER,
> FINANCIALYEAR INTEGER,
> SYSDTETME TIMESTAMP,
> USRNME VARCHAR(8),
> WAREHOUSECODE VARCHAR(4),
> VALUATIONCOST DECIMAL(18,5),
> SELLINGPRICE DECIMAL(18,5),
> SELLINGPRICEPER DECIMAL(18,5),
> VALUATIONCOSTPER DECIMAL(18,5),
> OLDCOSTPRICEPER DECIMAL(18,5),
> QTY1 DECIMAL(18,5),
> QTY2 DECIMAL(18,5),
> COSTPRICE DECIMAL(18,5),
> OLDCOSTPRICE DECIMAL(18,5),
> COSTPER DECIMAL(18,5),
> SOURCEID INTEGER,
> SOURCETYPE VARCHAR(15),

You told us, that SOURCETYPE is not a [VAR]CHAR but a numeric type. Ts,
ts, ts ...

> YEARENDADJ VARCHAR(1),
> PROCESSCURRENCY VARCHAR(3),
> PROCESSPRICE DECIMAL(18,5),
> PROCESSPER DECIMAL(18,5),
> PROCESSDISCOUNT DECIMAL(18,5),
> PROCESSEXCHANGERATE FLOAT,
> PROCESSEXCHANGERATETYPE VARCHAR(15),
> POSID INTEGER,
> SOURCELEDGER VARCHAR(15),
> SOURCEDETAILID INTEGER,
> TRACKINGNO1 VARCHAR(30),
> TRACKINGNO2 VARCHAR(30),
> TRACKINGNO3 VARCHAR(30),
> TRACKINGNO4 VARCHAR(30)
> );
>
>
>
>
> /******************************************************************************/
> /**** Primary Keys ****/
> /******************************************************************************/
>
> ALTER TABLE STK_STOCKTRANSACTION ADD PRIMARY KEY (ID);
>
>
> /******************************************************************************/
> /**** Indices ****/
> /******************************************************************************/
>
> CREATE INDEX STK_STOCKTRANSACTION_IDX1 ON STK_STOCKTRANSACTION (FINANCIALYEAR, PERIOD);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX10 ON STK_STOCKTRANSACTION (STOCKCODE, TRANSACTIONTYPE, TRANDTETME, ID);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX11 ON STK_STOCKTRANSACTION (TRANDTETME, STOCKCODE, WAREHOUSECODE, TRANSACTIONTYPE);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX12 ON STK_STOCKTRANSACTION (ID);
> CREATE INDEX STK_STOCKTRANSACTION_IDX13 ON STK_STOCKTRANSACTION (TRANDTETME, ID);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX14 ON STK_STOCKTRANSACTION (STOCKCODE, WAREHOUSECODE, TRANDTETME, ID);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX15 ON STK_STOCKTRANSACTION (STOCKCODE, TRACKINGNO1, TRACKINGNO2, TRACKINGNO3, TRACKINGNO4, TRANDTETME, ID);
> CREATE INDEX STK_STOCKTRANSACTION_IDX16 ON STK_STOCKTRANSACTION (SOURCELEDGER, TRANSACTIONTYPE, REFNO, SOURCEDETAILID);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX2 ON STK_STOCKTRANSACTION (STOCKCODE, TRANSACTIONTYPE, WAREHOUSECODE, TRANDTETME, ID);
> CREATE INDEX STK_STOCKTRANSACTION_IDX3 ON STK_STOCKTRANSACTION (FINANCIALYEAR, PERIOD, STOCKCODE, WAREHOUSECODE, TRANSACTIONTYPE);
> CREATE INDEX STK_STOCKTRANSACTION_IDX4 ON STK_STOCKTRANSACTION (STOCKCODE, WAREHOUSECODE, TRANSACTIONTYPE);
> CREATE INDEX STK_STOCKTRANSACTION_IDX5 ON STK_STOCKTRANSACTION (STOCKCODE, WAREHOUSECODE, FINANCIALYEAR, PERIOD, TRANSACTIONTYPE);
> CREATE INDEX STK_STOCKTRANSACTION_IDX6 ON STK_STOCKTRANSACTION (FINANCIALYEAR);
> CREATE DESCENDING INDEX STK_STOCKTRANSACTION_IDX7 ON STK_STOCKTRANSACTION (STOCKCODE, TRANDTETME, ID);
> CREATE INDEX STK_STOCKTRANSACTION_IDX8 ON STK_STOCKTRANSACTION (WAREHOUSECODE, STOCKCODE);
> CREATE INDEX STK_STOCKTRANSACTION_IDX9 ON STK_STOCKTRANSACTION (SOURCETYPE, TRANSACTIONTYPE, REFNO);

Hmm, could be a bit over-indexed?

I don't know your access patterns, but you know that each index makes
delete/insert/update performance worse, especially when an index need to
be updated by non-sequential values in e.g. batch processes, changing a
bunch of records.

I have run through some tests on indexes delete/insert/update wise.
Although I'm not ready to publish the results, I can only say, each
additional (useless) index can decrease DML performance dramatically.
The page cache plays an important role here as well.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com/