C#•12mo ago

Raw SQL Query Question

I have a raw SQL query that I need to execute against a database that's external to my application. The query is expected to return >5,000,000 records. Therefore, returning the records in memory (list) causes the application to crash due to running out of memory, even on a server with 122GB. Is it possible to enumerate over the records instead, so that I don't have to deal with them all in memory?

c#
public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

c#
public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

13 Replies

Angius•12mo ago

Can you really not paginate this data?

engineertdogOP•12mo ago

The actual query itself takes ~15 minutes to run via SSMS, and there's nothing I can do about that because it's a 3rd party system I can show you if you'd like, but not really. It's merging a bunch of tables together

x0rld 👻 🎃•12mo ago

you have access to the database ?

engineertdogOP•12mo ago

Yes I believe read-only for this client though

x0rld 👻 🎃•12mo ago

you can't do a view and paginate the view ?

Angius•12mo ago

You could try .AsEnumerable() instead of .ToList(), this will give you the ability to process each record individually without loading them all: <>

engineertdogOP•12mo ago

I'd have to look into that. If I can, how would that change the C# execution? Just run the execute as normal?

Angius•12mo ago

https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#buffering-and-streaming

engineertdogOP•12mo ago

I did try to use AsEnumerable, but I received a message that my dbContext changed or was no longer valid

x0rld 👻 🎃•12mo ago

cause you don't inject your dbcontext

  using (DbContext tempContext = new DbContext(con)) {

Angius•12mo ago

Jimmacle•12mo ago

can you put the query results into a temporary table then query that in pages?

engineertdogOP•12mo ago

Yeah, the data controller and the data processor are separate I could probably do that, would just have to figure out how to set it up I fixed the context issue. It's just sort of an odd way to do it because I was trying to keep DB logic separate from app logic. But since this is only 1 function required for the abstract for a specific use-case, it'll be fine

Gaming

Programming

Raw SQL Query Question