0

I have a simple web app in ASP.NET MVC which uses Sql CE for the backend database, using EF. Everything works fine, except that I'd like to keep a log of impressions for users hitting an api in the app- I get enough requests that it crashes the server. I've tried a couple options which do not perform well enough and I had to disable them. I'm keying off a user id in the database which has a unique index on it.

-in all cases, if the member doesn't exist in the db, a new record is created and saved.

  • in v1, look up member in db, increment an impression counter, save the record back. This was way too slow and crashed the server.

  • in v2, I simply checked if the record existed and create it. if it exists, I do nothing. This is still too slow, but faster than v1.

I'm looking into keeping an in-memory db for this next, and store it out to sql ce occasionally. My concern is, will this still not work if I store up 1000 items in a few seconds, and then try to store them in a single update? It feels like sql ce may just not be capable of doing what I need at all, or am I overlooking some simple performance optimization?

Brady Moritz
  • 8,624
  • 8
  • 66
  • 100
  • what you're facing is a simple performance issue with EF+SQL CE. I also had this issue, and it got way faster (about 15 times as fast!) with an already open connection passed to the context - seems like EF with CE has an issue with connection handling, as to opening the connection along with the first query would be very time consuming, especially if your database schema doesn't match your POCO classes exactly or the database was created on another OS (or sometimes even access issues to the file). – DevilSuichiro Oct 03 '15 at 21:34
  • ok, I thought it used connection pooling under the hood, but I'll test this out. – Brady Moritz Oct 03 '15 at 22:15
  • Use the TableDirect interfaces via raw ADO.net, and implement connection handling/warmup as mentioned above – ErikEJ Oct 04 '15 at 06:59
  • So I moved some things into memory and then do the writes occasionally in a thread. This is working well. However, I'm now trying to upgrade my project from EF4 to EF6 (along with migrating vs2012 to 2013, etc) and for some reason it has issues with "too many connections open" now. It feel slike every site visitor is keeping a connection open, until it runs out. Besides updating, all the normal site code is identical! Any idea how ef6 could cause this weird issue? – Brady Moritz Oct 10 '15 at 05:10

1 Answers1

0

I think it will be a good idea to consider SQL Server Express Edition/LocalDB as an alternative for SQL Server CE in this case. SQL Server CE is lightweight database for desktop applications or for mobile devices by design. CE 4.0 was improved for ASP.NET apps but its performance will be not so good in common cases.

Yuriy Gavrishov
  • 4,341
  • 2
  • 17
  • 29