LINQ to SQL database insert slows down as number of entity grows.

[lc4pro]

Hi,

I am experiencing slowdown in insert time as number of inserts keep increasing. To give you some background. I am using PostgreSQL database and my tables are as following(only relations are shown).

FOLDERS_TABLE
(
id BIGSERIAL NOT NULL PRIMARY KEY,
parent BIGINT,
...
);

FILES_TABLE
(
id BIGSERIAL NOT NULL,
folder_id BIGINT NOT NULL REFERENCES FOLDERS_TABLE (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
...
);

METADATA_TABLE
(
file_id BIGINT NOT NULL REFERENCES FILES_TABLE (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
...
);

This is intended to mimic file structure on regular file system. My application lets you create folder tree and lets you manage files in each folder just like a file system.
I have a loop that adds N number of files to database one after another means DataContext.SubmitChanges() is called after each file is added to a selected folder. The issue I am facing is that when I am adding thousands of files in a folder from a loop one after another the insert time (time to finish DataContext.SubmitChanges() call) keeps increasing as the number grows large, i.e. for 10 milliseconds for first file to 200 milliseconds for 20000th file and 500 milliseconds for 50000th file.

My initial guess is that it has to do with entity cache as it keeps growing. I am wondering if it's expected behavior and if there is solution to this?

Thank you.

[MariiaI]

We recommend you to create a new instance of DataContext to send each set of the objects to the database. DataContext is a lightweight object, and it is recommended to use a new DataContext for each unit of work instead of keeping it for a long time.

JIC: Entity cache may be disabled via the EntityCachingMode property:

Code: Select all

dc.EntityCachingMode = EntityCachingMode.None;

If these options do not help you solve the issue, please send us a small test project/specify the code sample so that we are able to investigate it in more details and find the solution for you.