> 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/
