Bad performance when reading CLOBs on Oracle

[dtabuenc]

Currently dapper is setting InitialLongFetchSize to -1 if the command supports it in order to avoid the n+1 problem when reading LONG columns from a database like Oracle. It would be great if it could also set InitialLOBFetchSize to -1 as well. Currently my performance is horrible when reading a query returning CLOB.

[Rojiferio]

Do you have any solution for that?

[mgravell]

Hmmm, I guess this one slipped through some come cracks. In theory this should be copy-pasta of the existing implementation. Are there any other consequences of this property? I'm not an expert on oracle...

[dmitriy-shleht]

Is there a solution to this problem?

[dtabuenc]

I hacked around it by creating a custom parameters class:

    public class OracleClobFetcherParameters : DynamicParameters, SqlMapper.IDynamicParameters
    {
        public OracleClobFetcherParameters()
        {
        }

        public OracleClobFetcherParameters(object template) : base(template)
        {
        }

        public new void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            base.AddParameters(command, identity);
            var oracleCommand = command as OracleCommand;
            if (oracleCommand != null)
            {
                oracleCommand.InitialLOBFetchSize= -1;
            }
        }
    }

[NickCraver]

I'm torn here, it looks like the default is zero (according to the docs), but -1 would bring back the entire contents (where it's bringing back nothing for users today). The tradeoff in speed for some may be a huge performance decrease in the form of bandwidth and memory for others. Also, there was a bug around this, but I can't actually get to the page and what I can find says it's been closed but wasn't really fixed so...I have no clue where it stands.

My current thinking is when we're looking at how we do options in v3, we add this for the user to set, and stick with defaults otherwise.

[dtabuenc]

I agree...it would be best if it's configurable since the tradeoff can be major in either direction.