Wait operation Time Out: 2 projects, same database, heavy load

Question

We have two projects which are using the same database.

As one of the projects undergoes an heavy database operation, the other may do small Task requests like these:

public async Task<ActionResult> GetManaIconAsync(string _mana)
{
    string mana = _mana.Replace("/", "");

    byte[] imageData = await Task.Run<byte[]>(() => mImateManager.GetManaByName(mana));

    return File(imageData, "image/png");
}

public byte[] GetManaByName(string _mana)
{
    using (MyDb db = new MyDb())
    {
        db.Database.Connection.Open();

        IQueryable<ITEM_IMAGES> imageQry = from img in db.ITEM_IMAGES
                       where img.ITEM_IMAGE_TYPE == (int)ImageTypes.Mana
                       select img;

        if (!imageQry.Any())
        {
            return null;
        }

        return imageQry.First(_item => _item.ITEM_NAME == _mana).ITEM_IMAGE;
    }
}

And project2 would crash when hitting the if(!imageQry.AnY()) saying that the wait operation timed out, which is normal since project1 is currently doing heavy work.

Now my question is how could I solve this? What are the options available so that both may do their respective work without impacting (too much) the other?

score 1 · Accepted Answer · answered Sep 18 '14 at 14:30

1

The SQL service broker might be an option for you. Please review: http://technet.microsoft.com/en-us/library/ms166071(v=sql.105).aspx and tell me if this could work for your scenario.

answered Sep 18 '14 at 14:30

Kevin Hendricks

785
1
8
36

I'm not sure if I understand correctly what is a Service Broker. Correct me if I'm wrong: it will take any queries / task I send to it, then proceed with the tasks asynchronously while resuming the main program, so project A may do its heavy load and project B would be able to make its query calls and everything would be treated separately? – hsim Sep 18 '14 at 14:33
That is correct. You can send the heavy requests asynchronously to the service broker and in turn connect with application B to the database. Take a look at: http://technet.microsoft.com/en-us/library/dd576261(v=sql.100).aspx for some more details about scalability or might want to search for an article specific for throttling the service broker – Kevin Hendricks Sep 18 '14 at 14:42
Please make sure to mark the response as answered when it helped you're case. Happy coding! – Kevin Hendricks Sep 18 '14 at 14:48
I'll explore it, do you know any area where I might get a tutorial / example for this? – hsim Sep 18 '14 at 14:50
1

http://social.msdn.microsoft.com/forums/sqlserver/en-US/a508ffc9-aed0-43a4-912e-0347782f5671/service-broker-and-net http://stackoverflow.com/questions/2089184/net-api-for-sql-service-broker – Kevin Hendricks Sep 18 '14 at 15:07

Wait operation Time Out: 2 projects, same database, heavy load

1 Answers1