Read BLOB too slow

Read BLOB too slow

Postby ths » Fri 14 Aug 2015 06:44

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
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Tue 18 Aug 2015 14:28

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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Wed 19 Aug 2015 07:48

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.
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Wed 19 Aug 2015 10:39

Please try the OCI connection (via Oracle client). Does it work faster?
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Wed 19 Aug 2015 11:39

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.
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Wed 19 Aug 2015 12:56

Try using different values of the DirectUtils.PacketSize property. Does it make any difference?
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Wed 19 Aug 2015 13:22

The default was 4096 and 30 sec.
By 8192 it is about 14 sec.
By 800 it hangs ... 12 min
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Wed 19 Aug 2015 14:39

ths wrote: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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Wed 19 Aug 2015 14:46

By OCI using Oracle Instant Client 11.2 it is 3 seconds.
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Thu 20 Aug 2015 07:59

Please specify the type of your VPN (openvpn, pptp, L2TP, IPsec etc.) and its version so that we can create a test environment.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Thu 20 Aug 2015 08:14

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.
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Fri 21 Aug 2015 11:26

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.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Fri 21 Aug 2015 11:33

Thank you in advance!
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Re: Read BLOB too slow

Postby Shalex » Wed 26 Aug 2015 07:19

We have sent a download link to the email address specified in your forum profile.
Shalex
Devart Team
 
Posts: 7703
Joined: Thu 14 Aug 2008 12:44

Re: Read BLOB too slow

Postby ths » Fri 28 Aug 2015 07:38

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
ths
 
Posts: 53
Joined: Thu 29 Dec 2011 16:18

Next

Return to dotConnect for Oracle