Hey all,
EDIT: Purpose of this Website: Its called Utopiapimp.com. It is a third party utility for a game called utopia-game.com. The site currently has over 12k users to it an I run the site. The game is fully text based and will always remain that. Users copy and paste full pages of text from the game and paste the copied information into my site. I run a series of regular expressions against the pasted data and break it down. I then insert anywhere from 5 values to over 30 values into the DB based on that one paste. I then take those values and run queries against them to display the information back in a VERY simple and easy to understand way. The game is team based and each team has 25 users to it. So each team is a group and each row is ONE users information. The users can update all 25 rows or just one row at a time. I require storing things into cache because the site is very slow doing over 1,000 queries almost every minute.
So here is the deal. Imagine I have an excel spreadsheet with 100 columns and 5000 rows. Each row has two unique identifiers. One for the row it self and one to group together 25 rows a piece. There are about 10 columns in the row that will almost never change and the other 90 columns will always be changing. We can say some will even change in a matter of seconds depending on how fast the row is updated. Rows can also be added and deleted from the group, but not from the database. The rows are taken from about 4 queries from the database to show the most recent and updated data from the database. So every time something in the database is updated, I would also like the row to be updated. If a row or a group has not been updated in 12 or so hours, it will be taken out of Cache. Once the user calls the group again via the DB queries. They will be placed into Cache.
The above is what I would like. That is the wish.
In Reality, I still have all the rows, but the way I store them in Cache is currently broken. I store each row in a class and the class is stored in the Server Cache via a HUGE list. When I go to update/Delete/Insert items in the list or rows, most the time it works, but sometimes it throws errors because the cache has changed. I want to be able to lock down the cache like the database throws a lock on a row more or less. I have DateTime stamps to remove things after 12 hours, but this almost always breaks because other users are updating the same 25 rows in the group or just the cache has changed.
This is an example of how I add items to Cache, this one shows I only pull the 10 or so columns that very rarely change. This example all removes rows not updated after 12 hours:
DateTime dt = DateTime.UtcNow;
if (HttpContext.Current.Cache["GetRows"] != null)
{
List<RowIdentifiers> pis = (List<RowIdentifiers>)HttpContext.Current.Cache["GetRows"];
var ch = (from xx in pis
where xx.groupID == groupID
where xx.rowID== rowID
select xx).ToList();
if (ch.Count() == 0)
{
var ck = GetInGroupNotCached(rowID, groupID, dt); //Pulling the group from the DB
for (int i = 0; i < ck.Count(); i++)
pis.Add(ck[i]);
pis.RemoveAll((x) => x.updateDateTime < dt.AddHours(-12));
HttpContext.Current.Cache["GetRows"] = pis;
return ck;
}
else
return ch;
}
else
{
var pis = GetInGroupNotCached(rowID, groupID, dt);//Pulling the group from the DB
HttpContext.Current.Cache["GetRows"] = pis;
return pis;
}
On the last point, I remove items from the cache, so the cache doesn't actually get huge.
To re-post the question, Whats a better way of doing this? Maybe and how to put locks on the cache? Can I get better than this? I just want it to stop breaking when removing or adding rows.
