We have created a web service and real time job where a single entry of a huge table is selected and afterwards enriched with data from other tables. Example: The web service request contains a customer number, the web service response contains the customer name. In the real time job the name is selected from BUT00.
Sounds quite easy, however the select from the huge table (say BUT00) is not pushed down to the database as the optimized SQL shows and the complete table is loaded to the memory, which is quite time consuming.
Is there any easy option or best practice to force SQL pushdowns in real time jobs?
Yes, if possible use a lookup with the appropriate cache spec (no_cache). Remember that if you select pre_load_cache or demand_load_cache and there are changes to the data you are looking up you won’t see those changes until the real-time service cycles.
If you are using regular tables/queries then you may want to set the cache setting on your source table(s) to No.