In one project we encountered a performance problem with storing files in an Oracle database (and this post is in essence not relateded to Oracle or databases in general): It took around ~45 sec (2MB file) and ~100 sec (4MB file) to store the files as Blobs. The files were read into on big byte array and handed over to ADO.NET. Naively thinking, even if this occupied the whole machine (i.e. scalability goodbye), in terms of performance this should probably be burstmode-fast. Obviously this was not the case and it turned out that the problem was caused somewhere in the ADO.NET data provider for Oracle.
Between Gerhard (with his object mapper) and Carl (being the Oracle specialist), I (being somewhat experienced with .NET) was involved in tracking the problem down.
My wild guess was that the data provider is just a layer above the oracle client lib (C-Lib), in which case it would move a memory block of 2 or 4 MB respectively via P/Invoke. This would include special GC handling (large object heap), pinning, marshaling, memcopy, physical memory allocations, page faults, etc.. From this assumption I deduced that quite a few of the possible issues would vanish if we moved several small memory blocks, say 4kB, instead. In other words a streaming or a chunking approach might help.
It was just a guess, yet Gerhard proved that it actually did solve the problem. If you are interested in the Oracle specifics I recommend reading Gerhards post… .
There’s actually two lessons to learn:
- P/Invoke and marshaling of large amounts of memory can be quite costly in terms of performance. Streaming/chunking ist much more efficient (and in case of server side applictions much more scaleable).
- As anyone with some knowledge about logical reasing knows: A false statement may lead to a correct conclusion. So I cannot be sure about statement #1. It may be the false presumption that actually led to a working solution.
Well, right now I’ll have to live with that unsatisfying situation. “Hier seh’ ich nun, ich armer Tor, und bin so klug als wie zuvor…” (German saying, not translatable…)
Thats all for now folks,
AJ.NET
AJ's blog 
Leave a Reply