Geetings.
I'm wondering about this use case I have at hand:
ADD TABLE "tTable" AREA "xxx"" DUMP-NAME "dumpXXX" ADD FIELD "WareHouse" OF "tTable" AS integer FORMAT ">>9" INITIAL "0" POSITION 2 MAX-WIDTH 4 ORDER 20 ADD FIELD "Pallet" OF "tTable" AS integer FORMAT ">,>>>,>>9" INITIAL "0" POSITION 3 MAX-WIDTH 4 HELP "Número de Pallet" ORDER 30 ADD FIELD "Kgr" OF "tTable" AS decimal FORMAT ">>,>>>,>>9.99" INITIAL "0" POSITION 4 MAX-WIDTH 17 DECIMALS 2 ORDER 40 ADD FIELD "DateIn" OF "tTable" AS date FORMAT "99/99/99" INITIAL ? POSITION 5 MAX-WIDTH 4 ORDER 50 ADD FIELD "DateOut" OF "tTable" AS date FORMAT "99/99/99" INITIAL ? POSITION 6 MAX-WIDTH 4 ORDER 60 ADD INDEX "PK" ON "tTable" AREA "yyy" UNIQUE PRIMARY INDEX-FIELD "WareHouse" ASCENDING INDEX-FIELD "Pallet" ASCENDING ADD INDEX "DatesIndex" ON "tTable" AREA "yyy" INDEX-FIELD "WareHouse" ASCENDING INDEX-FIELD "Pallet" ASCENDING INDEX-FIELD "DateIn" ASCENDING INDEX-FIELD "DateOut" ASCENDING . PSC cpstream=ISO8859-1 . 0000001403
This table records date of arrival and leave date of pallets on freezer warehouse They charge by kilogram, and by number of days:
I came to this query:
FOR EACH tTable WHERE (tTable.Warehouse = 1) AND (DateIn >= date1 AND DateIn <= date2) OR (DateIn < date1 AND DateOut >= date1): END.
With this, I get pallets that:
Before 1st of month Last of month After
________+_____________________________+______________
enter ------+-----------------leave +
enter ------+-----------------------------------------------+------------ leave
+ enter-------------------leave +
+ enter-------------------+-------- leave
fell on the period of charge... (the case where enter and leave before or after the period, are not taken on account)
I wonder if the DatesIndex would support the query on a efficient way?
The existing indexes will not properly support your query. The first issue is the Pallet column in the index, because of that the query will read all records for that WareHouse.
The second issue is that is soon as Progress sees a range it will stop looking at the rest of the columns in the index and use brute force on the records themselves to do the filtering.
You need to add two indexes... one on WareHouse DateIn and one on WareHouse DateOut
Then the query would need to be like this....
FOR EACH tTable
WHERE
(tTable.Warehouse = 1 AND
tTable.DateIn >= date1 AND tTable.DateIn <= date2) /*--- uses index on warehouse datein ---*/
OR
(tTable.Warehouse = 1 AND /*--- uses index on warehouse dateout ---*/
tTable.DateOut >= date1):
IF tTable.DateIn < date1 THEN NEXT.
END.
In theory you could make set the index names so that the DateOut index gets picked first (alphabetically) but I prefer not to depend on those kind of index rules for application code.
Thanks!
Reingenieering right now...