Is there any way to speed up the response time of the application if it is creating and seeding around 120k of records on the application startup?

Question

In BooksList I have around 60k records and in Readers I have around 20 only.

Now, to populate the bridge table (many-to-many) I have to assign each BookList record at least 2 Readers.

It's done but on the loading of app for the first time, it takes around ~30 seconds.

Is it normal?

Is there any way to speed it up?

Note: this is the requirement and I have to do it this way means I have to seed the data on the first load but my concern is performance.

foreach (var n in BooksList)   //60k records
{
    int innerLoopCounter = 0;

    foreach (var t in ReadersList)  //only 20 registered readers
    {
        BooksReadersList.Add(new BooksReaders() { BooksID = n.BooksID, ReaderID = t.ReaderID });

        innerLoopCounter++;

        if (innerLoopCounter > 2)   //should not be more than 2.
            break;
        
    }
}


BooksDbContext.BooksReaders.AddRange(BooksReadersList);

BooksDbContext.ChangeTracker.DetectChanges();

base.Seed(BooksDbContext);

The BooksReadersList is a list of type BookReaders which has 3 columns (ID, Book_ID, Reader_ID).

The BooksList is a list of type Books which as 2 columns (ID, BookTitle) and ReaderList is a list of type Readers which as 2 columns (ID, ReaderName).

This isn't enough code to make an accurate assessment of your problem. We don't know the type of `BooksList`, the type of `n`, or the type of `BooksReadersList`. EF's behavior is not predictable from the code you've posted. — Robert Harvey, Apr 02 '21 at 17:53
I suspect you could make this whole thing nearly instantaneous with a couple of well-chosen SQL statements. Sixty-thousand records is not a lot, even if it's multiplied by 20. Provide a better description of your problem, and we can probably give you a better way to solve it. — Robert Harvey, Apr 02 '21 at 17:54
@RobertHarvey actually, the task says that i shall be done through the EF i.e. creating the data and storing into the localdb. — Lucifer, Apr 02 '21 at 18:00
What I was kinda hoping for is an answer to why the books list is being endlessly duplicated. Why couldn't everyone simply use the same books list? — Robert Harvey, Apr 02 '21 at 18:02
@RobertHarvey no no, a task that I need to do for my entry test they have given me to solve. — Lucifer, Apr 02 '21 at 18:19
The first two readers in ReaderList get all the books, since `innerLoopCounter` is reset to 0 for every Book. — Heretic Monkey, Apr 02 '21 at 18:25
@HereticMonkey that's why its taking time. right? If i directly use something like .Take(3) then it would be reduced? correct? — Lucifer, Apr 02 '21 at 18:26
I don't know. I just thought I'd point it out :). I wouldn't think it would matter that much, since the enumerator is pretty efficient. Depends on how `ReadersList` is created. But something to try, certainly. — Heretic Monkey, Apr 02 '21 at 18:30
@HereticMonkey it somehow reduced the time by around 10 seconds. bravo — Lucifer, Apr 02 '21 at 18:43

score 0 · Answer 1 · answered Apr 02 '21 at 19:23

I'm guessing here based on the code shown in the question. I created a LINQ-style Cartesian product of BooksList and the first two readers from ReadersList, based on this answer by McKay to Is there a good LINQ way to do a cartesian product?. I don't know if this will be faster, but it moves the looping out of user code. Hopefully, EF will translate that into some efficient SQL. Not as efficient as SqlBulkCopy or plain old SQL statements written out, but given the constraints...

var BooksReadersList = BooksList.SelectMany((b) => ReadersList.Take(2), 
    (book, readers) => readers.Select(reader => new BooksReader 
        { book.BooksID, reader.ReaderID }));

BooksDbContext.BooksReaders.AddRange(BooksReadersList);

BooksDbContext.ChangeTracker.DetectChanges();

base.Seed(BooksDbContext);

Is there any way to speed up the response time of the application if it is creating and seeding around 120k of records on the application startup?

1 Answers1