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.
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.
Code:
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')