I’ve got to load dat aform a huge flat comma-separated file into BW. I thought I might get some performance edge using Data Services – at the current client it’s Data Integrator 11.7.
I have set the flat file source ro use parralel process threads; as the number of CPUs on the machine is 4, I went by the book and set 4 parralel processes. Minimised type conversions, made sure there is no codepage conversion etc.
Still, the load performance is not as good. What’s worse, from performance monitor I see that the server stays idle for most of the time, and only one al_engine processes is actually running.
Well, my expectation was that the server would be crunching the file and heavily using all CPUs to push the data to BW. Have I missed anything in the setup, or it’s just the way it is and I cannot beat performance of flat file load directly from BW application server?
Add a Map Operation right before the BW Loader and discard all data. That will show you haw fast DS is without loading any data. I assume it will be times better proving it is BW everything is waiting for.
The BW loader should have a commit size and a number of loaders - at least in recent versions. Using those settings does greatly improve the performance.
I’ve found that the maximum data package size (RSCUSTV6) in BW had been set to 20000. When I changed it to 50000 the load performance from DI has got much much better. Still, the load performance from Flat File as a BW Source System has also improved and still looks like a better option (with one exception, though, that uploading source files to the file server is easier for me than to the BW app server).
Now let me speculate a bit: the DI job server is a 4 CPU machine that theoretically should read flat files better, utilizing multithreading. That means, if I increase the package size in RSCUSTV6 to some (much) higher value, then file reading by BW may become a bottleneck, while the DI would read files faster. I haven’t seen any BW system, though, where that setting would be more than 50000.
The question is mostly to BW people who might have played with the maximum package size – does it make sense to try and set that package size ridiculously high?
One thing you should do immediately is setting the number of loaders flag in the BW target object to something like 8. (If you have a package size option only but no number of loaders, then your DS version would be pretty old, we have changed that 1.5 years ago?)
Initially I though you did that but reading your post again, you talk about the file reader and DOP but not the number_of_loaders, do you?
What happens as I assume is, the first few 1000 rows of the flat file are read by DS and transformed - that takes 2ms. The loader keeps collecting that data until the package size is reached, so after 20ms it got its 20’000rows and now calls the corresponding BAPI function in BW to load this package. While the BAPI is being called, the loader cannot do anything else, the upstream objects are put into a wait state as well as they have filled all the input buffers of the downstream transforms and filling them up even further does not make much sense. After 500ms the BAPI returned and the loader can collect the next batch of 10000rows, which again takes just 20ms. So for each 20ms CPU consumption, you have 500ms wait time for BW - result is an idle DataServices server.
By using number_of_loaders =8 you have 8 BAPI calls simultaneously, using 8 ABAP processes on the server. The BAPIs will still be slower than what DS can process, but if you have a BW server with 200 CPUs you might get into a balance where DS starts to sweat and you would go for DOP.
Werner, I wish I could use number of loaders (I’m aware of such option), but as I mentioned it’s old Data Integrator (11.7), hence I can only play with the package size setting on both sides.
So I thought as follows:
Network link between DI and BW is quite good, and both servers are powerful enough.
There is likely a certain performance overhead with calling a BAPI (can you please validate this assumption?).
So the bigger package I manage to pass in one BAPI call, the better. When I increased the allowed package size on BW side.
Obviously there should be some limit to which I can increase that package size without hitting something else, and that’s what I’m trying to find.
(BOB member since 2004-12-17)