Memory usage in SubmitChanges()

[Cosine]

I'm using linqconnect 4.1 151 sqlite 4.3 146 to create and populate a database. The records I'm inserting are 68 bytes each. My code reads a csv file, and for each record generated, I create a record object, fill in the fields, and call InsertOnSubmit(). For the purpose of debugging and analysis, I use a counter to keep track of how many records have been created, and when I reach whatever limit I choose, I submit them all. I'm also using a timer to determine the rate of insertion.

What I'm seeing is that inserting 100,000 records is taking between 50 and 100 seconds, and using around 400 MB of memory. Needless to say, at that rate the system runs out of memory fairly quickly. Since I don't see other memory usage complaints in the forum, this must be something I'm doing wrong, but I haven't been able to find my problem.

I've tried wrapping the SubmitChanges() call in a transaction, but that made no difference. I also tried closing and disposing the context after each call to SubmitChanges() but that also made no difference.

The datacontext is created from an existing database, and for each run I create a new database by changing the file name in the connect string.
Here's pseudocode for what I'm doing.

Code: Select all

using (SQLiteConnection connection = dbUtil.CreateConnection(dbFile))
{
    dbUtil.CreateDatabase(connection);

    // use linqConnect to add records to the database
    context = new MainDataContext(connection.ConnectionString);

    while (there are still records)
    {
	if (count == limit)
	{
	    DateTime start = DateTime.Now;	// for timing insertions per second
	    context.SubmitChanges();
	    DateTime end = DateTime.Now;	// for timing insertions per second
	    TimeSpan timer = end.Subtract(start);
	    Debug.WriteLine(String.Format("Total time for insert of {1} records: {0} MS",
						  timer.TotalMilliseconds, recordsDone));
	    count = 0;
	}
		
	get a record
	fill in the fields
	call InsertOnSubmit(rec)
    }
}

I have put breakpoints before and after the SubmitChanges() call to make sure that the memory is being allocated there. I also use the same parsing code to generate equivalent records and write them to a binary file. I can write a 2.6 million record binary file in about a minute; I expected the database population to be close to that speed.

This must be some mistake I'm making. Where should I start looking?

Thanks,
Dennis

[MariiaI]

We couldn't reproduce the issue regarding memory usage with LinqConnect 4.1.151 in our environment.
We are sending you the sample project to the e-mail address you provided in your forum profile, please make changes to it so that the issue could be reproduced and send it back to us or send us your sample project.

[Cosine]

I solved my memory usage issue when I was building a sample project to send in for review. When I created the original project inside a much bigger solution, I used the System.Data.SQLite package that I downloaded via NuGet instead of Devart.Data.SQLite. When I switched to the Devart dll, the memory usage leveled out. I inserted over 10 million records into a database and memory usage never went over 450 MB, including the overhead of Visual Studio and the debugger.

I'm still not seeing very good insertion speed, though. For the 10 million inserts, done in groups of 500,000, the rate was around 3500 inserts per second.

I'd still like to see the sample project you offered, if only to see any optimizations you're doing. It would be interesting to see the difference between the code in a SQLite sample and the mySQL sample that ships with the package.

Thanks,
Dennis

[MariiaI]

We have made a test application that demonstrates inserting of 2 million records via the DataContext and via the SQLiteCommand. We are sending it to the e-mail address you provided in your forum profile, please test it and notify us about the results.

Please pay attention to the following points:
- we compare the performance of DataContext with a parameterized SQLiteCommand, not a plain SQL, due to the fact that DataContext uses parameters when building SQL commands;
- when possible, LinqConnect tries to perform update operations in batches; with SQLite, disabling batch updates may improve performance, as requests to the 'server' are relatively cheap;
- we recommend you to open one common transaction for performing all inserts.

[Cosine]

Mariial,

I never got an email from you. I've checked my profile, and the email address there is correct. Could you please try resending?

Thanks,
Dennis

[MariiaI]

We've sent you the sample again. Please check that the letter is not blocked by your mail filter.
If there are any problems with this, please specify some other mail address where we can send the sample (you can contact us at 'support at devart dot com' for this).