Geeks With Blogs

News

Dave Noderer's Blog South Florida does .NET!

Recently I was using Entity Framework 6.1.1 to implement functionality for a windows service.

I had used code first, that is creating a class in code then attaching to a db context and migrating it into a table on SQL Express 2014 but these had been small projects without a lot of data and hosted on full size sql servers and most projects start with an existing SQL Server so code first is not used.

This time through testing processing a dozen or two transactions was no problem and I was not worried about database size. The code is reading a csv file from a secure web service then importing and processing. These transactions include signature pad data whose fields can be quite lengthy, over 10kb in some cases as it is base64 encoded. The text and data are transformed into fields on a form including .jpg bytes for the signatures and then exported as a .tiff file.

During testing I kept asking for production file samples but only got the odd test transaction or two.. so when a large production file (over 20k transactions) hit there were problems, no surprise!

After handling a few other issues the program would error out after importing 300-400 transactions.

One issue is that the signatures were bigger than the test articles but the main problem is that if you make a class in code first like this:

public class foo()

{

public string text1 {get; set;}

public string text2 {get; set;}

}

In the database you end up with a table foo and all the fields are nvarchar(max). Even this is not so bad if you only have a small bit of data in the fields and the row fits within an 8k database page.

The problem is that with multi-kilobyte fields additional pages are allocated for each row in multiple tables and disk space quickly expands.

The error was that the database was filled which happens when SQL Express hits 10 gigabytes (SQL Express 2014).

Luckily this is easily fixed by annotating with the MaxLength so my classes now look like:

 

public class foo()

{

[MaxLength(50)]

public string text1 {get; set;}

[MaxLength(100)]

public string text2 {get; set;}

}

Run the database migration commands and now all 20k transactions fit in a few hundred megabytes.

Note that I’m not too worried about longer term storage, this information will be quickly purged and only has to handle a day or two of transactions at a time. If that changes we can always upgrade to a standard sql server instance…

Simple fix and common sense but some of us learn hard!!

Posted on Saturday, December 20, 2014 9:47 AM c# , Entity Framework 6 , Windows Services , SQL Express | Back to top


Comments on this post: EF 6.1 Code First Field Lengths – Reminder to self!

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Dave Noderer | Powered by: GeeksWithBlogs.net