Need help building an Index

[doom1701]

As I've mentioned in other places, I am not a Progress guy, but I play one on TV. We've got a bolt on Dispatching system that ties into our ERP application; the Dispatching system has an "Importer" process that, once every two minutes, looks for new or updated order records in the ERP database. The ERP is Progress, the Dispatching system is SQL Server.

The ERP system maintains a transdt and transtm field on every record; as you'd guess, these are set to the date and time any time any time the system changes a record. The dispatch system is using these fields to find any changes since the last import. These fields are not indexed, though, so this query has to go through the entire order table--during peak times, it's taking longer than two minutes to run, so we've got a sqlsrv process eating gobs of CPU for the bulk of the day.

A good index would fix the issue, but there's a lot of finger pointing going on between the Dispatching software and Infor (Infor seems to have EVERYONE playing finger pointing games right now). I control the database, though, and an index (from what I understand) is basically controlled only by it's storage area and name, so a future upgrade shouldn't be a concern.

That's the long explanation. The query is even longer. There's an upgrade coming that cleans it up a little, but I've been told the "Where" clause in the new version is using the same fields.

SELECT     1 AS x_Type, whse AS x_Location, orderno AS x_OrderNumber, stagecd AS x_StageCode, transtype AS x_TransType, invno AS x_InvoiceNumber, 
                      cono AS x_Cono, ordersuf AS x_ShipmentNumber, enterdt AS x_OrderDate, promisedt AS x_DateExpected, transdt AS x_UpdateDate, 
                      transtm AS x_UpdateTime, custpo AS x_PONumber, shipviaty AS x_ShipVia, slsrepout AS x_Salesman, takenby AS x_WrittenBy, custno AS x_CustNum,
                       shipto AS x_JobNum, shiptonm AS x_ShipToName, shiptoaddr AS x_ShipToAddress, shiptocity AS x_ShipToCity, shiptost AS x_ShipToState, 
                      shiptozip AS x_ShipToZip, shipinstr AS x_ShippingInst, refer AS x_Reference, route AS x_Route, totordamt AS x_TotalAmount, 
                      totcost AS x_TotalMarketCost, taxamt AS x_TotalTax, totweight AS x_TotalWeight, totcubes AS x_TotalVolume
FROM         PUB.oeeh h
WHERE     (transdt >= '7/21/2009') AND (cono = 1) AND (transtype NOT IN ('QU', 'CS', 'DO', 'RA')) AND (whse IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', 
                      '12', '13', '14', '15', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26')) AND (stagecd >= 1) AND (transdt > '7/21/2009') OR
                      (transdt >= '7/21/2009') AND (cono = 1) AND (transtype NOT IN ('QU', 'CS', 'DO', 'RA')) AND (whse IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', 
                      '12', '13', '14', '15', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26')) AND (stagecd >= 1) AND (transdt = '7/21/2009') AND (transtm >= '1432')

 

[TomBascom]

There is some risk that adding an index might change the index selection for some 4GL queries since 4GL index selection is a compile-time process. Usually it would improve performance if that happened but there are pathological situations where it might make performance worse and there are situations where bone-headed programming that turns FIRST records into magic records could, potentially, break something. So you need to test.

Another possibility would be to add a new, custom, field and an index for that field. This would avoid the downside of indexing on existing fields since no existing queries would reference a new field. Ideally you are running OE10 so that you could use a DateTime field (which SQL would be happy about) but if not you could cobble one together from another data type. You would keep the new field synchronized by use of a write trigger. You would also need to initialize it after you add it.

Also make sure that you are periodically running "update statistics" to keep the SQL side of this efficient.