Large, very big, possibly huge performance impact when using CLOB

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Fri 08 Mar 2019 14:30

There's a significant performance impact when inserting rows to a table that contains a CLOB column (about 7x slower). This is very unfortunate because strings are mapped to CLOB by default. Here's what I did:

Devart.Data.Oracle.EFCore 9.6.696
Oracle 11.2.0.2.0
Connection mode: direct

I've created 2 simple entities in C#

Code: Select all

public class DataWithClob
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}

public class DataWihoutClob
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}
I've mapped one string to NVARCHAR(2000) and the other to CLOB

Code: Select all

modelBuilder.Entity<DataWihoutClob>().Property(e => e.Value).HasColumnType("nvarchar2(2000)");
modelBuilder.Entity<DataWithClob>().Property(e => e.Value).HasColumnType("clob");
Then I've inserted 1000 items into each table. Length of the string is the same in both cases (36 characters)

Code: Select all

var stopwatch = new Stopwatch();
stopwatch.Start();
using (var transaction = _context.Database.BeginTransaction())
{
    _context.Entities.AddRange(data);
    _context.SaveChanges();
    transaction.Commit();
}
stopwatch.Stop();
Inserts with NVARCHAR(2000) took about 2 seconds, inserts with CLOB took about 14 seconds.

Same insert using SQL completes in a fraction of a second in both cases.

Performance profiler shows that Devart.Data.Oracle.OracleLob.WriteLobCache seems to be causing the performance issue. I've found a (maybe) related post in Delphi DAC forum viewtopic.php?t=35225 The solution was apparently to turn off Lob Cache. I have not found such an option in Entity Framework adapter.

This makes me a very sad developer. Please advise.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Wed 13 Mar 2019 14:31

You can set up your application to use NVARCHAR instead of CLOB by default via config.CodeFirstOptions.UseNonLobStrings=true. Refer to https://www.devart.com/dotconnect/oracl ... tions.html .

Also, NVARCHAR will be used if you set max length for string properties explicitly either via .HasMaxLength(2000) or via [MaxLength(2000)].

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Thu 14 Mar 2019 12:48

Unfortunately, my actual use case (not the benchmark) requires strings that are longer than 4000 characters so I have no choice but to use CLOB.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Fri 15 Mar 2019 15:01

Rytis wrote: Thu 14 Mar 2019 12:48 Unfortunately, my actual use case (not the benchmark) requires strings that are longer than 4000 characters so I have no choice but to use CLOB.
In this case, the best performance would be with loading data via OracleLoader.

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Mon 18 Mar 2019 10:29

I would write oracle specific code only as a last resort (that's why I'm using Entity Framework after all). Is there really no way to turn off CLOB cache or something else that would improve performance with CLOB data type?

Side note:

I've tested the same inserts with beta EF provider from Oracle and it does not suffer from this kind of performance degradation with CLOB.

Code: Select all

1000 Inserts
-----------------------------------------------------------------
|    Data Type   | DevArt EF Provider | Oracle Beta EF Provider |
-----------------------------------------------------------------
| NVARCHAR(2000) |         2s         |            0.5s         |
-----------------------------------------------------------------
|      CLOB      |         16s        |            0.5s         |
-----------------------------------------------------------------

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Fri 22 Mar 2019 19:12

We will investigate the question and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Tue 26 Mar 2019 15:50

We are getting the following result:

Code: Select all

NVARCHAR(2000) with Devart OCI: 00:00:02.4546542
NVARCHAR(2000) with Devart Direct: 00:00:02.3591570
CLOB with Devart OCI: 00:00:05.1780717
CLOB with Devart Direct: 00:00:06.8982510
Please modify this code so that we can reproduce the 8x difference (2s vs 16s):

Code: Select all

using Devart.Data.Oracle;
using Microsoft.EntityFrameworkCore;
using System;
using System.Diagnostics;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            //var monitor = new OracleMonitor() { IsActive = true };

            TestDevartNvarcharOCI();
            TestDevartNvarcharDirect();

            TestDevartClobOCI();
            TestDevartClobDirect();
        }

        static void TestDevartNvarcharOCI()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(@"User Id=alexsh;Password=alexsh;Server=orcl1120;license key=...;");

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Nvarchar38444[] dataChar = new Nvarchar38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Nvarchar38444() { Id = i, Nvarcharcolumn = new string('a', 2000) };
                    dataChar[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Nvarchar38444s.AddRange(dataChar);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                stopwatch.Stop();
                Console.WriteLine("NVARCHAR(2000) with Devart OCI: {0}", stopwatch.Elapsed);
            }
        }

        static void TestDevartNvarcharDirect()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(@"User Id=alexsh;Password=alexsh;Server=dboracle;Direct=True;Sid=orcl1120;license key=...;");

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Nvarchar38444[] dataChar = new Nvarchar38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Nvarchar38444() { Id = i, Nvarcharcolumn = new string('a', 2000) };
                    dataChar[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Nvarchar38444s.AddRange(dataChar);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                stopwatch.Stop();
                Console.WriteLine("NVARCHAR(2000) with Devart Direct: {0}", stopwatch.Elapsed);
            }
        }

        static void TestDevartClobOCI()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(@"User Id=alexsh;Password=alexsh;Server=orcl1120;license key=...;");

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Clob38444[] dataClob = new Clob38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Clob38444() { Id = i, Clobcolumn = new string('a', 2000) };
                    dataClob[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Clob38444s.AddRange(dataClob);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                stopwatch.Stop();
                Console.WriteLine("CLOB with Devart OCI: {0}", stopwatch.Elapsed);
            }
        }

        static void TestDevartClobDirect()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(@"User Id=alexsh;Password=alexsh;Server=dboracle;Direct=True;Sid=orcl1120;license key=...;");

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Clob38444[] dataClob = new Clob38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Clob38444() { Id = i, Clobcolumn = new string('a', 2000) };
                    dataClob[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Clob38444s.AddRange(dataClob);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                stopwatch.Stop();
                Console.WriteLine("CLOB with Devart Direct: {0}", stopwatch.Elapsed);
            }
        }
    }
}

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Wed 27 Mar 2019 14:43

I've made a small modification to your code and here's the result I'm getting:

Code: Select all

NVARCHAR(2000) with Devart Direct: 00:00:02.8097106
CLOB with Devart Direct: 00:00:15.3007380
I don't have OCI so I've removed the tests.

Here's the full code:

Code: Select all

using Microsoft.EntityFrameworkCore;
using System;
using System.Diagnostics;

namespace ConsoleApp1
{
    internal class Program
    {
        private static string _connectionString;

        private static void Main(string[] args)
        {
            var licenseKey = Environment.GetEnvironmentVariable("DevArtKey");
            var user = Environment.GetEnvironmentVariable("OracleUser");
            var password = Environment.GetEnvironmentVariable("OraclePass");
            _connectionString = $"direct=true;User Id={user};Password={password};Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEXDB)));license key={licenseKey}";
            TestDevartNvarcharDirect();

            TestDevartClobDirect();

            Console.WriteLine("All your base are belong to us. Press any key to continue...");
            Console.ReadKey();
        }

        private static void TestDevartNvarcharDirect()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(
                _connectionString);

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Nvarchar38444[] dataChar = new Nvarchar38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Nvarchar38444() { Id = Guid.NewGuid(), Nvarcharcolumn = new string('a', 2000) };
                    dataChar[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Nvarchar38444s.AddRange(dataChar);
                    _context.SaveChanges();
                    transaction.Commit();
                }

                stopwatch.Stop();
                Console.WriteLine("NVARCHAR(2000) with Devart Direct: {0}", stopwatch.Elapsed);
            }
        }

        private static void TestDevartClobDirect()
        {
            var builder = new DbContextOptionsBuilder<alexshModel>();
            builder.UseOracle(
                _connectionString);

            using (var _context = new alexshModel(builder.Options))
            {
                _context.Database.EnsureDeleted();
                _context.Database.EnsureCreated();

                Clob38444[] dataClob = new Clob38444[1000];
                for (int i = 0; i < 1000; i++)
                {
                    var item = new Clob38444() { Id = Guid.NewGuid(), Clobcolumn = new string('a', 2000) };
                    dataClob[i] = item;
                }

                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var transaction = _context.Database.BeginTransaction())
                {
                    _context.Clob38444s.AddRange(dataClob);
                    _context.SaveChanges();
                    transaction.Commit();
                }

                stopwatch.Stop();
                Console.WriteLine("CLOB with Devart Direct: {0}", stopwatch.Elapsed);
            }
        }

        internal class alexshModel : DbContext
        {
            public DbSet<Clob38444> Clob38444s { get; set; }

            public DbSet<Nvarchar38444> Nvarchar38444s { get; set; }

            public alexshModel(DbContextOptions options) : base(options)
            {
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<Clob38444>().Property(p => p.Clobcolumn).HasColumnType("clob");
                modelBuilder.Entity<Nvarchar38444>().Property(p => p.Nvarcharcolumn).HasColumnType("nvarchar2(2000)");
            }
        }
    }

    internal class Nvarchar38444
    {
        public Guid Id { get; set; }

        public string Nvarcharcolumn { get; set; }
    }

    internal class Clob38444
    {
        public Guid Id { get; set; }

        public string Clobcolumn { get; set; }
    }
}
Last edited by Rytis on Tue 02 Apr 2019 07:03, edited 2 times in total.

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Tue 02 Apr 2019 07:01

@Shalex can you reproduce the problem or do you need more info from me?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Wed 03 Apr 2019 16:00

We have reproduced the following case and are investigating it:

Code: Select all

-----------------------------------------------------------------
|    Data Type   | DevArt EF Provider | Oracle Beta EF Provider |
-----------------------------------------------------------------
| NVARCHAR(2000) |         1.2s       |            0.8s         |
-----------------------------------------------------------------
|      CLOB      |         5s         |            0.6s         |
-----------------------------------------------------------------
We will notify you about the result.

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Thu 18 Apr 2019 08:30

Any updates on the issue?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Sat 20 Apr 2019 14:35

The investigation is in progress. Once we have any results, we will notify you.

Rytis
Posts: 7
Joined: Fri 08 Mar 2019 13:43

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Rytis » Mon 19 Aug 2019 15:36

Shalex wrote: Sat 20 Apr 2019 14:35 The investigation is in progress. Once we have any results, we will notify you.
How is the investigation going along?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Large, very big, possibly huge performance impact when using CLOB

Post by Shalex » Fri 23 Aug 2019 16:17

We are working on the issue. We will notify you about the result.

KevinMazet
Posts: 1
Joined: Fri 19 Nov 2021 15:04

Re: Large, very big, possibly huge performance impact when using CLOB

Post by KevinMazet » Fri 19 Nov 2021 15:08

Hi! We encounter the same problem with a huge performance impact to select rows in a huge table with a CLOB column. Changing the column type to a varchar type is not possible due to length limitations.

Using an OracleConnection to retrieve the data for this specific table would break the architecture of our data access layer. We would be a lot of work and make the code confusing, so we would like to avoid it.

Regarding the performance impact, with a data set of 100 000 rows with clobs that have less than 4000 characters, it takes us 6 min to retrieve the rows in the application while it takes ~10s with the same table with a varchar typed column instead of the clob. The overhead is +3500%.

By chance, do you have any fix or other alternatives so far ?

Thanks in advance

Post Reply