C#•4d ago

High SQL Server memory usage with EF6 composite key batch existence check

Hi everyone, I'm having trouble optimizing data saving in EF6. Currently, I'm processing a batch of 30–40 records, and for each record, I call a method that checks whether the record exists in the database using a composite primary key.

public T ItemExistsInDatabase<T>(Expression<Func<T, bool>> expr) where T : class
{
    return _unitOfWork.Set<T>().Where(expr).SingleOrDefault();
}

public void Save(IEnumerable<Model> data)
{
    foreach (var currentRow in data)
    {
        var existingRecord = _repository.ItemExistsInDatabase<Model>(x => x.key1 == currentRow.key1 && x.key2 == currentRow.key2);

        if (existingRecord != null)
        {
            if (currentRow.RemovedOn == null)
            {
                //existingRecord = _mapperController.GetMapper<OADataPumpMapperProfile>().Map(currentRow, existingRecord);
            }
            else
            {
                _repository.Remove(existingRecord);

            }
        }
        else
        {
            if (currentRow.RemovedOn == null)
            {
                _repository.Add(currentRow);
            }
        }
    }

    _repository.SaveChanges();
}

public T ItemExistsInDatabase<T>(Expression<Func<T, bool>> expr) where T : class
{
    return _unitOfWork.Set<T>().Where(expr).SingleOrDefault();
}

public void Save(IEnumerable<Model> data)
{
    foreach (var currentRow in data)
    {
        var existingRecord = _repository.ItemExistsInDatabase<Model>(x => x.key1 == currentRow.key1 && x.key2 == currentRow.key2);

        if (existingRecord != null)
        {
            if (currentRow.RemovedOn == null)
            {
                //existingRecord = _mapperController.GetMapper<OADataPumpMapperProfile>().Map(currentRow, existingRecord);
            }
            else
            {
                _repository.Remove(existingRecord);

            }
        }
        else
        {
            if (currentRow.RemovedOn == null)
            {
                _repository.Add(currentRow);
            }
        }
    }

    _repository.SaveChanges();
}

This isn't particularly fast, so I'm trying to improve it. I tested some GPT-generated methods that check existence using a long WHERE ... OR ... OR clause, and also using IN, but while these approaches are faster, they result in roughly twice as much memory usage on the SQL Server side, which I couldn't resolve. Is it even possible to make this process both faster and not memory-hungry for SQL Server? And what exactly causes such high memory consumption in those bulk comparison approaches? Thanks in advance!

13 Replies

greyfox•3d ago

Why is the memory usage a problem?

CryyOP•3d ago

Actually, I'm not sure. My client thinks the memory usage is high, but maybe it doesn't really matter. It’s possible that SQL Server is just using the memory because it can — and will release it when needed. We haven't run into any out-of-memory errors so far 🙂 I'd also like to add that this method is called multiple times, each time with a batch of around 40 records.

greyfox•3d ago

SQL Server will typically try to grab as much memory as possible by default regardless of how much is actually needed. 90%+ is normal enough.

CryyOP•3d ago

Alright, you're probably right. I'll try running it on the client's system and see if it causes any issues. But just out of curiosity — why does pulling more records at once repeatedly consume so much more memory compared to fetching them one by one?

greyfox•3d ago

Dunno, you'd have to examine the precise SQL being executed. Have you tried monitoring it with SQL Profiler? Once you get the SQL scripts you could look at the execution plan and see if there are any table scans being done.

CryyOP•3d ago

SQL looks ok. It's simple select with where with lots of or or where with in ... pretty clean an easy to execute imo.

greyfox•3d ago

If there are some joins you could try moving parts of the where clause into the joins. If there are subqueries you could try moving those out of the query. Generally you pay for execution speed with memory usage, unless the script was pulling back a horrifying amount of data.

Angius•3d ago

None of your code seems to be async

Unknown User•3d ago

Message Not Public

CryyOP•this hour

Not sure I understand. Just to clarify — the methods you're referring are shown together here just for context, but they're not part of the same class or layer. ItemExistsInDatabase<T>() is implemented in the repository layer and internally uses UnitOfWork.Set<T>(). The Save(...) method is part of the service layer, which works only with the repository interface and doesn’t touch UnitOfWork directly. As for the rest of your comment (about currentRow, RemovedOn, and _mapperController.GetMapper), I’m not entirely sure what you found problematic — would you mind elaborating a bit? I’d be happy to clarify or adjust if needed.

Unknown User•this hour

Message Not Public

CryyOP•this hour

YEah ...it's NET 4.7.2 and EF6 :/

Unknown User•this hour

Message Not Public

Gaming

Programming

High SQL Server memory usage with EF6 composite key batch existence check

Did you find this page helpful?