Read BLOB too slow

[ths]

Hello,
I have performance problem reading BLOB from Oracle in the following code:

using (OracleDataReader dr = oraCommand.ExecuteReader())
{
while (dr.Read())
{
if (dr["blobfield"] != DBNull.Value)
byte[] bTest = (byte[])dr["blobfield"];
...
}
}

For the field with 2 MB size the read takes about 30 second. I am quite sure that the time is taken by reading. I can remove the IF and the time will be the same. So, first time I use the field, it will be read, either in IF or in assignment, when IF is removed. The assignment itself does not take any time when IF part is not removed.

The call to dr["blobfield"] for the BLOB with 2-3 MB data costs 30 seconds.

Can it be done better?
Thanks,
Vladimir

[Shalex]

We cannot reproduce the problem at the moment.

1. Please replace

Code: Select all

    using (OracleDataReader dr = oraCommand.ExecuteReader()) {
        while (dr.Read()) {
            if (dr["blobfield"] != DBNull.Value) {
                byte[] bTest = (byte[])dr["blobfield"];
            }
        }
    }

with

Code: Select all

    var start = DateTime.Now;
    using (OracleDataReader dr = oraCommand.ExecuteReader()) {
        Console.WriteLine(DateTime.Now - start);

        while (dr.Read()) {

            start = DateTime.Now;
            if (dr["blobfield"] != DBNull.Value) {
                Console.WriteLine(DateTime.Now - start);

                start = DateTime.Now;
                byte[] bTest = (byte[])dr["blobfield"];
                Console.WriteLine(DateTime.Now - start);
            }
        }
    }

and specify the output to find out the line of code which takes 30 seconds to execute.

2. Tell us the exact (x.x.x) version of your dotConnect for Oracle, version and capacity (x86 or x64) of your Oracle server and Oracle client.

[ths]

Hello,
the version is 8.4.457. The problem is the same with Oracle 11 and Oracle 12 servers. There is no client (direct connect).
The time is consumed on the first data access via dr["blobfield"] as i already mentioned.
However, I have more information now. The problem appears when the data are obtained to my local client from the database server in the customer network over VPN. But I do not think that the problem is slow internet.
I cannot reproduce the problem in our local network when both client and database server are in the same local domain.

[Shalex]

Please try the OCI connection (via Oracle client). Does it work faster?

[ths]

I have prepared a simple test using Oracle Instant Client and the difference is a factor of 10.
However, this would be very incovenient for us to change the whole workflow to OCI.

[Shalex]

Try using different values of the DirectUtils.PacketSize property. Does it make any difference?

[ths]

The default was 4096 and 30 sec.
By 8192 it is about 14 sec.
By 800 it hangs ... 12 min

[Shalex]

I have prepared a simple test using Oracle Instant Client and the difference is a factor of 10.

Please specify the exact number of seconds in the OCI mode.

[ths]

By OCI using Oracle Instant Client 11.2 it is 3 seconds.

[Shalex]

Please specify the type of your VPN (openvpn, pptp, L2TP, IPsec etc.) and its version so that we can create a test environment.

[ths]

We use for test the preconfigured notebook from our customer. On this notebook the WatchGuard vpn client software is installed.
Please let me know if this information is enough.

[Shalex]

Currently the largest possible value of DirectUtils.PacketSize is 8192. We will create an internal build for you with increased possible value and send it to you next week.

[ths]

Thank you in advance!

[Shalex]

We have sent a download link to the email address specified in your forum profile.

[ths]

Hello,
thank you very much for the installation.
My first test show the following results:
PacketSize: 4096
Time: 00:00:32.3383421
PacketSize: 8192
Time: 00:00:14.1022192
PacketSize: 12288
Time: 00:00:08.7046884
PacketSize: 16384
Time: 00:00:08.9386854
PacketSize: 24576
Time: 00:00:08.5018910
PacketSize: 32767
Time: 00:00:08.7982872