StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would test it first to be sure. Performance doesn't have to be that bad.</p> <p>If you need to enter all rows in one transaction, call it after all of AddToClassName class. If rows can be entered independently, save changes after every row. Database consistence is important.</p> <p>Second option I don't like. It would be confusing for me (from final user perspective) if I made import to system and it would decline 10 rows out of 1000, just because 1 is bad. You can try to import 10 and if it fails, try one by one and then log. </p> <p>Test if it takes long time. Don't write 'propably'. You don't know it yet. Only when it is actually a problem, think about other solution (marc_s).</p> <p><strong>EDIT</strong></p> <p>I've done some tests (time in miliseconds):</p> <p>10000 rows:<br/></p> <p>SaveChanges() after 1 row:18510,534<br/>SaveChanges() after 100 rows:4350,3075<br/>SaveChanges() after 10000 rows:5233,0635<br/></p> <p>50000 rows:</p> <p>SaveChanges() after 1 row:78496,929<br/> SaveChanges() after 500 rows:22302,2835 <br/>SaveChanges() after 50000 rows:24022,8765<br/></p> <p><strong>So it is actually faster to commit after n rows than after all.</strong></p> <p>My recommendation is to:</p> <ul> <li>SaveChanges() after n rows. </li> <li>If one commit fails, try it one by one to find faulty row.</li> </ul> <p>Test classes:</p> <p>TABLE:</p> <pre><code>CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [SomeInt] [int] NOT NULL, [SomeVarchar] [varchar](100) NOT NULL, [SomeOtherVarchar] [varchar](50) NOT NULL, [SomeOtherInt] [int] NULL, CONSTRAINT [PkTestTable] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>Class:</p> <pre><code>public class TestController : Controller { // // GET: /Test/ private readonly Random _rng = new Random(); private const string _chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; private string RandomString(int size) { var randomSize = _rng.Next(size); char[] buffer = new char[randomSize]; for (int i = 0; i < randomSize; i++) { buffer[i] = _chars[_rng.Next(_chars.Length)]; } return new string(buffer); } public ActionResult EFPerformance() { string result = ""; TruncateTable(); result = result + "SaveChanges() after 1 row:" + EFPerformanceTest(10000, 1).TotalMilliseconds + "<br/>"; TruncateTable(); result = result + "SaveChanges() after 100 rows:" + EFPerformanceTest(10000, 100).TotalMilliseconds + "<br/>"; TruncateTable(); result = result + "SaveChanges() after 10000 rows:" + EFPerformanceTest(10000, 10000).TotalMilliseconds + "<br/>"; TruncateTable(); result = result + "SaveChanges() after 1 row:" + EFPerformanceTest(50000, 1).TotalMilliseconds + "<br/>"; TruncateTable(); result = result + "SaveChanges() after 500 rows:" + EFPerformanceTest(50000, 500).TotalMilliseconds + "<br/>"; TruncateTable(); result = result + "SaveChanges() after 50000 rows:" + EFPerformanceTest(50000, 50000).TotalMilliseconds + "<br/>"; TruncateTable(); return Content(result); } private void TruncateTable() { using (var context = new CamelTrapEntities()) { var connection = ((EntityConnection)context.Connection).StoreConnection; connection.Open(); var command = connection.CreateCommand(); command.CommandText = @"TRUNCATE TABLE TestTable"; command.ExecuteNonQuery(); } } private TimeSpan EFPerformanceTest(int noOfRows, int commitAfterRows) { var startDate = DateTime.Now; using (var context = new CamelTrapEntities()) { for (int i = 1; i <= noOfRows; ++i) { var testItem = new TestTable(); testItem.SomeVarchar = RandomString(100); testItem.SomeOtherVarchar = RandomString(50); testItem.SomeInt = _rng.Next(10000); testItem.SomeOtherInt = _rng.Next(200000); context.AddToTestTable(testItem); if (i % commitAfterRows == 0) context.SaveChanges(); } } var endDate = DateTime.Now; return endDate.Subtract(startDate); } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload