Michael Bell’s Blog

With my in-memory log-hit table I talked about in my last entry, I started getting these errors a few times a day, and it was enough to bomb the whole app:
Column ‘blahblah’ is constrained to be unique.  Value ‘-4862′ is already present.
Column ‘blahblah’ is constrained to be unique.  Value ‘-4847′ is already present.
Column ‘blahblah’ is constrained to be unique.  Value ‘-4768′ is already present.

Let me show you how the ‘blahblah’ column is built in the table construction:
DataColumn dc;

dc = new DataColumn(“HitID”, typeof(Int32));
dc.Unique = true;
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
_MyTable.Columns.Add(dc);

So how in the world is this happening? Let me show the piece of code that was throwing the exception:
DataRow row = MemoryTables.HitList.NewRow();
row["FileID"] = Convert.ToInt32(context.Request.QueryString["fi"]);
row["FileName"] = fi.Name;
row["Username"] = _Username;
row["MemberID"] = _MemberID;
row["Success"] = true;
row["Bytes"] = fi.Length;
MemoryTables.HitList.Rows.Add(row);

Fairly straight forward.. right? With each hit to an image, this thing fires and inserts a new row into the in-memory table. About 20 rows get inserted a second. After much trial and error, I completely eliminated this problem by re-arranging a few lines of code. The new code reads:
DataRow row = MemoryTables.HitList.NewRow();
MemoryTables.HitList.Rows.Add(row);
row["FileID"] = Convert.ToInt32(context.Request.QueryString["fi"]);
row["FileName"] = fi.Name;
row["Username"] = _Username;
row["MemberID"] = _MemberID;
row["Success"] = true;
row["Bytes"] = fi.Length;

By creating a new row and adding it to the table before I populate it with any values, I eliminate my problem. I’d love to hear an explanation if anyone has one…

I’ve been running image hosting sites for a couple of years. Through my trials I discovered a few things. Running the web server AND the sql server off the same box when you are getting a couple of million image hits a day can be trying on your system. The first version of my image hosting site wrote logs as they came in. 1 image hit actually equalled 3 queries to the DB. That was due to a lack of experience in both architecture and ASP.NET.

In July I decided to rewrite my ASP.NET 1.1 in 2.0. I have plenty of ram in my box, and i knew I wanted to do less queries to my database. What I wound up doing was an in-memory datatable that gets a record for each hit. I used the TableNewRow datatable event to check the record count with each insert. Probaboy not the best method… but I went with it. A more preferable way to do it would be to use a Timer object and firing on the Tick event. In any case, once I reach 10k records, I do a “dump” to the database of the in-memory stuff, and then clean my in-memory table.

As for the “dump”… well… the redesign could not have come at a better time. I was able to utilize the new .NET 2.0 SqlBulkCopy class. What this amounted to was a 4 second query every 10 minutes. Compare that to the alternative of doing several inserts a second… and you can start to see the advantage of in the in-memory/SqlBulkCopy dump technique.

After I released my new version of my image hosting site, I found out REAL fast that there were some problems related to sharing in-memory datatables that are accessed (read, write) constantly (writes 15-20 times a second).

It took me quite a bit of trial and error to figure out how to get it stablized. The server I was working on had dual xeon processors with hyper-threading enabled. What I found was that when this combination was being used, and I was logging hits to an in-memory datatable 15-20 times a second, different threads were trying to log to this table at the same time, causing all hell to break loose.

The fix – briefly lock the table during writes:
lock (MemoryTables.HitList)
{
    MemoryTables.HitList.Rows.Add(row);
}

The dump/rebuild procedure also requires a lock, but it’s very brief:
using (SqlConnection sourceConnection = new SqlConnection(_ConnectionString))
            {
                sourceConnection.Open();

                            // Insert into Event Log so we know logging is taking place as expected;
                            EventLog MyLog = new EventLog();
                            MyLog.Source = SourceName;
                            MyLog.WriteEntry(“Hit data has been dumped. HitList Records: ” + _HitList.Rows.Count, EventLogEntryType.Information);
                        }
                        catch (Exception ex)
                        {
                            throw;
                        }
                        finally
                        {
                            _HitList.Clear();
                        }
                    }
                    #endregion
                }
            }

I half-expected the locking to cause a delay problem in my logging abilities, but I’ve noticed no bottlenecks at all, and this method has been operating trouble free for about a month now.

I got this into a “release” state today.. and thought I’d put it out there for anyone who wants to use it. It’s a Windows looking time picker control. Have a look!

http://www.MichaelKBell.com/TimePicker

So, you’ve got the code in place to create a site in IIS via c# in one of your web apps, but you want the site ASP.NET version to default to 2.0. I had the problem and couldn’t find a simple “switch” to flip, so I wound up writing this little piece of code that goes in and manually remaps the files that need it, from the 1.1 framework to the 2.0. After that runs, your IIS ASP.NET version will read 2.0, and act accordingly

ManagementObjectSearcher searcher = new ManagementObjectSearcher(@”root\MicrosoftIISv2″,
    “SELECT * FROM IIsWebVirtualDirSetting WHERE Path = ‘” + pathToRoot.Replace(@”\”, @”\\”) + webSiteName + “‘”);