0

I am hosting a .Net Web Api on azure, the api is just updating one row in a sql server database. Here is my Updatecode:

using (Storage ctx = new Storage())
        {
            string json = JsonConvert.SerializeObject(ev, _jsonsettings);
            Data ex = ctx.dbsData.FirstOrDefault();
            if (ex == null)
                ctx.dbsData.Add(new Data() { Json = json, LastUpdate = DateTime.Now });
            else
            {
                ex.Json = json;
                ex.LastUpdate = DateTime.Now;
                ctx.Entry(ex).State = EntityState.Modified;
            }
            ctx.SaveChanges();
            return new HttpResponseMessage(HttpStatusCode.OK);
        }

and i have a second method which only clears the table:

 [HttpDelete]
    public HttpResponseMessage Clear()
    {
        using (Storage ctx = new Storage())
        {
            ctx.dbsData.RemoveRange(ctx.dbsData);
            ctx.SaveChanges();
            return new HttpResponseMessage(HttpStatusCode.OK);
        }
    }

See also my DbContext Class:

 public class Storage : DbContext
{
    public DbSet<Data> dbsData { get; set; }

    public Storage(string connectionstring) : base(connectionstring)
    {
        Configuration.LazyLoadingEnabled = false;
        Configuration.ProxyCreationEnabled = false;
        Configuration.ValidateOnSaveEnabled = false;
        Configuration.AutoDetectChangesEnabled = false;
    }

    public Storage() : this("tom")
    {

    }
}

My Problem is, that both methods are extremly slow. I found out if i remove the SaveChanges() Command it executes way faster. So my question is how can i improve performance and why does it take that long to update/delete one single row?

  • The reason it is faster when you remove SaveChanges() is because without it your db is not updated. SaveChanges() is the point in which the query is actually executed on your database. for one row, it shouldn't be slow though. However more info is needed, how big is the json data you stored into one of the columns? does it save eventually or is it just timing out? – MartinM Jan 17 '17 at 10:18
  • i know that savechanges() updates the database. What i don't understand is why it is that slow (~10sec). My json has a size of about 500kb. If i try it locally on my sqlexpress server it is stored immediatly without any delay. –  Jan 17 '17 at 11:08
  • Can you please provide more info on what Azure do you use? Is it a VM or App Service with a Azure SQL Database? When you try locally you mean you set your Azure WebApi connectionstring to your local sqlexpress? Have you tried setting your local connection string to the Azure SQL Database? – Petre T Jan 17 '17 at 15:56
  • I am using an app service (basic) and a sql server database (basic 5 dtus). if think just for testing it should be enough. –  Jan 17 '17 at 16:22

1 Answers1

-3

It totally depend on how much data is in your table ctx.dbsData. ctx.dbsData.RemoveRange(ctx.dbsData); marks those many rows for deletion only. But changes will only reflect after calling SaveChanges(); For faster processing you can have async calls.

Pavvy
  • 353
  • 3
  • 6
  • 15
  • Be careful when saying "For faster processing you can have async calls". Whilst using async calls can give greater performance overall, the statement in isolation is untrue. here http://stackoverflow.com/a/30043327/692830 is a good answer comparing the difference to SaveChanges and SaveChangeAsync. Also what you say would be better served as a comment, it does not provide an answer to the question, just more questions – MartinM Jan 17 '17 at 10:55
  • the table has always only one row –  Jan 17 '17 at 11:09
  • @ManuelBleimuth then it should not take that much of time. I need to see your code. – Pavvy Jan 17 '17 at 11:22
  • @Pavvy you can see my whole code above, i have no idea why it's that slow. My json normally has about 500kb, but that should not take affect on the delete command. –  Jan 17 '17 at 12:44