One of our Bex workbook query is timing out intermittently. Mostly in the morning and sometimes in the afternoons and evenings. There is no specific trend except it times out once every morning. The workbook query executed has the same selection for all the runs and is never changed.
We have increased web dispatcher timeout and appropriate icm time outs but we dont believe increasing timeout is an ideal solution.
One thing i am puzzled about is, only the early morning first run takes several work processes in the backend BW system and is timing out. Whereas later runs use less work processes and read the same tables quickly. Could this be due to the amount of data.
Another aspect here is sometimes when the run takes place immediately after a timedout run or successful run, the query outputs data immediately (in 15 seconds sometimes), it is here we observe that the backend work processes are not doing any reads on the fact tables. This implies that sometimes its either reading from some sort of query cache of sorts. Can you please point me to any documentation regarding how queries cache data.
Today, due to the impact its having on the Morning runs, we have further revised web dispatcher time out, ICM times outs in PROCTIMEOUT,TIMEOUT etc....however it still timed out when ran via web dispatcher and directly on a specific ABAP server. The error when it timed out is "Traffic Control: Nettimeout (30) exceeded by peer:". My troubleshooting yielded we need to review icm/traffic_control However we are on NW 701 (EHP1) and not on NW 730. Does any one know how we can accomodate increasing icm/traffic_control
Also any thoughts on why workbook query run times differ from time to time. I have checked the sql statements and corresponding reads etc...but i couldn't find significant difference.