0

Currently using VS2013 Express and SQL Server 2012 Express, to prototype an application. Either or both packages may be upgraded to paid versions later, as required.

I am fairly new to C#, but consider myself an intermediate-level VBA programmer for Excel/Access.

The application will load a database with data. In the module I am currently developing, ~17k records are generated from each raw data file- this will be dramatically higher in the next module, however. But the loading process is PAINFULLY slow using the Entity Framework. A previous version used table adapters, and took a reasonable amount of time.

As I understand, generating framework views might speed it up. I have found several entries on this and other sites, including:

Fastest Way of Inserting in Entity Framework Visual Studio 2013 and Entity Framework

Is using the Framework even the best approach? Are there things I can do within the confines of the Express versions of VS and SQL to optimize the performance?

Community
  • 1
  • 1
user3483624
  • 13
  • 2
  • More links: http://blogs.msdn.com/b/adonet/archive/2008/06/20/how-to-use-a-t4-template-for-view-generation.aspx http://msdn.microsoft.com/en-us/library/bb896240(v=vs.100).aspx http://blogs.msdn.com/b/appfabriccat/archive/2010/08/06/isolating-performance-with-precompiled-pre-generated-views-in-the-entity-framework-4.aspx – user3483624 Apr 01 '14 at 05:02
  • EF is not great for bulk inserts. If you insert everything at once, it can be done in a single transaction, but it would use a tremendous amount of local resources to prepare the context with all those records. If you do them one at a time, you'll be communicating with the database way too many times. If you want to try to improve things, try batching them into groups, maybe 100 at a time, and see if that helps. – Joe Enos Apr 01 '14 at 05:04
  • Just keep in mind that along with the convenience of EF, you get all the overhead of the data context, entity state, change tracking, etc. If your data process is simple enough, plain old ADO.NET is still the fastest way to execute SQL through .NET. – Joe Enos Apr 01 '14 at 05:07
  • You mention a raw data file, It would be significantly faster if you could use the SqlBulkCopy object (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx). As other posters have mentioned, using raw ADO.NET might be a better solution in this particular situation. EF is really handy when you want to work with your data as objects. If you're just reading an input file and spitting out records using a parameterized query is probably just as easy to write and it'll run quicker. – Craig W. Apr 01 '14 at 05:19

1 Answers1

0

You can try compile you Linq query or try make Entity faster any other way, but don't waste your time, use ADO.NET in this case.

brewerof
  • 92
  • 2
  • 7