LINQ to SQL database insert slows down as number of entity grows.
Posted: Tue 08 Oct 2013 17:53
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.
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.