Lokheed's Locutions

Batching sql writes for performance

ron@jamesaxler.com — Sun, 31 Jul 2011 18:01:59 GMT

The software I write primarily in my current job all surrounds a conference event management system, handling all aspects of creating and operating a trade conference with upwards of 3,500 attendees. The centerpiece of the main conference we host is a round-robin of face-to-face appointments that we frequently refer to internally as “speed dating”. Spread out over several hotel ballrooms are 976 tables split into three separate tracks. Sitting at each table is a representative from a travel agency, and rotating through the tables are representatives from travel suppliers (cruise operators, hoteliers, tour operators, etc.). The travel supplier has 4 minutes to pitch their product to the travel agent, and then a chime goes off and the supplier moves to the next table in their track. Over the course of four days this results in 290,040 individual face-to-face appointments.

In order to drive the 4-minute meetings I wrote a DotNetNuke module that allows a conference administrator to first set up the parameters of the meetings and then populate the tables. They are able to specify the number of tracks (a distinct loop in which a supplier moves through), the number of tables per track, the number of sessions (I.E. – the number of distinct loops within a track), and the duration of the appointments. With those numbers I can generate the number of physical tables required for the event. The system also lets the user specify the physical rooms in which the tables reside, and enforces virtual “break tables” at the beginning or end of the loops and the rooms, so that a supplier is always given sufficient time to either move to the next room or move back to the beginning of a loop after reaching the last table.

With all the tables defined, next the administrator specifies which companies are permanently seated at which tables, and also specifies the starting position of each supplier at the beginning of the event. With all of that information, the system is then able to loop through everything and generate the actual ScheduleMap appointment records, enabling us to provide each attendee with a full schedule of who they are meeting and when. That loop is where I ran into my performance issue.

Initially as I looped through the table definitions and appointment times, I would write a single record to the database for each appointment. This resulted in more than 290,000 individual round trips to the database. On my local machine, with the web server and database server all house locally, a speed dating event with 976 tables and 290,040 appointments took about five minutes to generate. That was kind of a long time, but since the appointments only need to be generated infrequently I was not terribly concerned. When I moved that code to our Beta environment the appointment generation time grew to 12 minutes. I was more concerned, but again for an infrequent process I was not panicked.

Unfortunately, once that solution was pushed to production, the runtime to generate all 290k appointments took over an hour. I did the math, and I realized that if the round trip to the database in production took only 1/100th of a second longer than the same transaction in beta, for 290k round trips that tiny, imperceptible increase turned into 48 minutes of overhead. I don’t care how infrequent the process is, a full hour was simply too long.

Enter the power of the SqlBulkCopy class. Instead of making a round trip for each individual record, I created an in-memory data table with the same structure as my target database table:

DataTable scheduleMap = new DataTable("Scheduler_ScheduleMap");    
scheduleMap.Columns.Add("Id", typeof(int));     
scheduleMap.Columns.Add("MapId", typeof(int));     
scheduleMap.Columns.Add("TableId", typeof(int));     
scheduleMap.Columns.Add("TableName", typeof(string));     
scheduleMap.Columns.Add("MemberId", typeof(int));     
scheduleMap.Columns.Add("SupplierId", typeof(int));     
scheduleMap.Columns.Add("StartTime", typeof(DateTime));     
scheduleMap.Columns.Add("Rank", typeof(int));     
scheduleMap.Columns.Add("AppointmentNumber", typeof(int));     
scheduleMap.Columns.Add("SessionId", typeof(int));     
scheduleMap.Columns.Add("SchedulerEventId", typeof(int));

Then as I looped through my schedule, instead of writing to the database I wrote a record to my in-memory data table. Finally, once all of the new rows were created, I made the batched call to write them to the database (having previously deleted any existing records for that event):

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);    
bulkCopy.DestinationTableName = "dbo.Scheduler_ScheduleMap";     
bulkCopy.WriteToServer(scheduleMap);

I didn’t have to worry about unique id’s in the identity column, because on the bulk WriteToServer command the database itself automatically ignores the Id value being passed in and generates the next id to be assigned.

I threw in some instrumentation to time how long the appointment generation took, and how long the write to the database took. The results were astounding. On my local machine, where previously the appointment generation had taken five minutes, it took 29 seconds to generate the in-memory appointment records and another 3 seconds to write those records in bulk to the database. That’s five minutes reduced down to 32 seconds. In beta, as expected, it took a little longer. The total process took a total of 47 seconds, down from twelve minutes. Where the solution really shined was in production. Where previously the appointment generation had taken over an hour, now the entire thing was completed in one minute and 37 seconds. That’s a huge win, and it made the business owners very, very happy.

So remember, scalability matters. Round trips to the database are expensive, progressively more so as you move more data at once. If you are moving more than a few records at a time, SqlBulkCopy is your friend.

More ...

Relocating the DotNetNuke partial postback progress bar–Take 2

ron@jamesaxler.com — Sun, 13 Feb 2011 16:01:54 GMT

Two weeks ago I posted this blog entry in which I showed my solution in moving the DNN partial postback progress bar. It turns out that there is a significant flaw in that solution. Briefly, the solution was to use jQuery to locate the the element that contains the progress bar - $("div[id$='_UP_Prog']") – and then appending that element to a new element that I created as the desired new location for the progress bar.

This works fine on initial page load and for the first time the module performs a partial postback. On any subsequent partial postback, however, the progress bar disappears. It is neither in the old or the new location. Why did that happen?

In retrospect, it’s obvious: the _UP_Prog div lies outside the update panel performing the partial postback, but the new target location for the progress bar lies inside the update panel. When the update panel re-renders, the relocated progress bar is no longer there. The script to relocate the progress dutifully fires, but the source location is no longer there either.

This is a problem.

My first attempt at a solution involved wrapping the _UP_Progress element in a hidden div so that it would never be seen, and then cloning it to the new location instead of appending. This works but it also creates two elements with the same id, which is a Very Bad Thing.

My ultimate solution was to hide the _UP_Prog element, and then create my new progress bar element and control its visibility myself on partial postback.

By HTML looks like this:

And then my javascript looks like this:

$(document).ready(function () {
$("div[id$='_UP_Prog']").wrap("

Relocating the DotNetNuke Partial Postback Progress Bar

ron@jamesaxler.com — Thu, 27 Jan 2011 13:49:00 GMT

When partial postback is enabled for a DotNetNuke module, DNN very helpfully wraps the entire module in an update panel and then places an animated progress bar at the bottom of the panel that becomes visible during partial postbacks. In most cases this is just fine, but on my current project the business owner requested that the progress indicator be moved to a different location because it was frequently not visible below the fold. I looked to see if DNN had any built in functionality to specify the location of the progress bar, and quickly discovered that none existed. No worries, though, a little dollop of jQuery and everything was working exactly as the business owner wanted.

When Partial Postbacks Make Stylesheets Disappear

ron@jamesaxler.com — Thu, 30 Dec 2010 20:43:00 GMT

Today I stumbled across a very interesting issue, and I thought I would share it. I am currently working on a large DotNetNuke module, and as a part of it I am making use of the jQuery UI libraries. I included a link to the stylesheet for the jQuery UI theme I am using, and it seemed to work just fine... until I triggered a partial postback and saw all of my pretty jQuery UI formatting get lost. The first trick I learned, and the one that got me 90% of the way there, is to make use of the PageRequestManager to add a function for handling the formatting after a partial postback completes. The javascript looks like this:

jQuery(document).ready(function () {
     Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);

     function EndRequestHandler(sender,args) {
          SetUI();
     }

     function SetUI() {
          jQuery("a[id$='NewEventLinkButton']").button({
               icons: {
                    primary: "ui-icon-plusthick"
               }
          });
     }

     SetUI();
});

Obviously, I have quite a bit more in the SetUI() function, but that gives you the idea. The function gets called when the page initially loads, and on the return from a partial postback it gets fired again thanks to the end request handler.

I found it to be working like a charm in every major browser, except for some reason IE was losing the formatting. At first I thought the end request handler was not firing, but a quick bit of debugging proved that it was. The script was firing, but the required styles were no longer there. After digging a little deeper, I discovered this culprit:

According to the XHTML spec, you cannot have a Style element within the Body of an html document, it must be loaded in the Head. As a practical matter, in virtually every instance loading a stylesheet from inside the body works fine, and even in IE it works fine on initial load. In IE, however, on a partial postback the reference to the stylesheet gets lost. This is that extremely rare and elusive case of Internet Explorer actually working correctly according to specs.

I didn't want to move the contents of the jQueryUI custom stylesheet into my module.css, so I did the next logical thing. In the base class for my DNN module (you do use a common base class for all of your DNN modules, right?) I added the following code:

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            ScriptManager sm = ScriptManager.GetCurrent(Page);
            if (!sm.IsInAsyncPostBack)
            {
                HtmlLink l = new HtmlLink();
                l.Href = ResolveUrl(ControlPath + "scripts/css/smoothness/jquery-ui-1.8.7.custom.css");
                l.Attributes.Add("rel", "stylesheet");
                l.Attributes.Add("type", "text/css");
                l.Attributes.Add("media", "all");
                Page.Header.Controls.Add(l);
           }
        }

As soon as I did that, and removed the incorrect link from the module user control itself, everything worked exactly as it should.
More ...

When your DotnetNuke upgrade goes wrong

ron@jamesaxler.com — Sun, 06 Jun 2010 01:59:00 GMT

Over the past few weeks I have upgraded more than a dozen DotNetNuke websites to the current latest version, v5.4.2. For some of those sites it was a trivial upgrade coming from a very recent version, but for many of them it was a long trip from as far back as v4.6.2 released in October of 2007. Happily, most of the updates went flawlessly. Some of them, however, hit a few bumps in the road along the way. After finishing my last update, I thought it would be worthwhile to write about some of the common pitfalls in performing a DNN upgrade, and how to deal with them when they come up.

Rule #1 – Backup, backup, backup

The first and most important thing to do before starting any upgrade is to back up both your database and your file system. Backing up the file system should be simple enough for anyone performing the upgrade – if you have access to be able to upload the DNN upgrade files, then you have access to back up the existing files. Do not skip this step, it is not optional. Depending on how your website is hosted, backing up the database will either be trivial if you have direct access to the database server, or it may be more indirect if you have to rely upon your web hosting company to perform the backup. Any baseline competent web host should be performing regular database backups anyway, but if you are relying upon your host’s technical support it is well worth the extra time to contact them and make sure you have a backup that is no more than 24 hours old.

It is an immutable law of the universe that if you fail to perform your backups and just confidently upload the latest DNN upgrade package, that is the time that the update will blow up in the worst possible way. Without backups to roll back to, you will be stuck with a broken website and no good options to fix it. Don’t set yourself up for that kind of easily avoidable headache.

Rule #2 – Know your upgrade path

If you are only jumping from last month’s release to this month’s then you have nothing to worry about. But if you are upgrading a site that has gotten several months (or years!) behind, you need to carefully plan out your upgrade path. You can’t just run the 4.5.2 update against a site running 4.9.5, it will almost certainly fail. Then again, you don’t need to laboriously walk through every single interim release either. There are certain waypoints along the upgrade path that, as long as you hit them, seem to keep all (or most) of the upgrade gremlins at bay. Will Strohl has an excellent blog post, Suggested DotNetNuke Site Upgrade Path(s), that he has continued to maintain with the current optimum upgrade path. I have made use of this post on numerous occasions and can wholeheartedly vouch for its accuracy.

Rule #3 – Know your web.config

I am not saying that you need to be intimately familiar with every single line in your web.config and what it does (although that certainly wouldn’t hurt), but there are some very important things to know about what is going on in there. You need to know if you have placed any custom items in your web.config that are not part of the default DNN installation – perhaps you have installed a custom module that required a configuration setting, or perhaps you even wrote your own module and placed some code in the App_Code folder. Anything that is different from the norm, you need to know what it is and why it is there. Beyond that, it is very helpful to use a tool like WinDiff to compare your current web.config with the release.config of the DNN install package that matches your current version. If you see lots of differences, even if it is only rearranged but otherwise identical lines, it is well worth the effort to resolve those differences and get your web.config as close as possible to the release.config. In my basic DNN websites, the only lines that are different are the SiteSqlServer keys, the machineKey, and sometimes there is a key for the DNN installation date. Keeping your web.confg as close as possible to the release.config will go a long way to helping your upgrades go smoothly.

Common upgrade issues

So, you diligently performed your backups, carefully planned your upgrade path, meticulously cleaned up your web.config, and the upgrade failed. Now what? Well, here are some of the issues I have run into lately.

Complete failure, the site won’t even load

In this case, the first thing to do is to set the customErrors key in your web.config to “Off”. Please note that the value is case sensitive, so setting it to “off” instead of “Off” will not work. Once you can see the actual exception, hopefully it will be descriptive enough to give you some idea of how to resolve the issue. One common way it can fail is if you have something in the App_Code folder that is no longer needed and not compatible with the current .NET Framework assembly. In most cases the DNN upgrade will automatically remove deprecated code such as the old App_Code/HTML code base, but sometimes it lingers on. If the exception you are looking at is coming from anywhere in the App_Code folder, look at your web.config and remove the offending entry from the section. DotNetNuke no longer has any core modules in the App_Code folder, so if you have something there that you didn’t put there yourself, you can safely delete it.

Another error I have run into recently that caused a complete failure was a version mismatch of the Telerek.Web.UI.dll assembly. The resolution was to simply look in the DNN full install package for that version, find the correct dll, and manually upload it to the /bin folder. I don’t know how it got missed during the regular update (and I am confident the error was on my part and had nothing to do with the quality of the DNN upgrade package), but simply re-uploading the correct version of the offending .dll resolved the issue.

If you have resolved your issue, don’t forget to go back and set the customErrors key in your web.config back to “RemoteOnly”. Failure to do so will leave you open to airing your dirty exception laundry to the entire world. Don’t do that.

The upgrade appeared to succeed but you are stuck on the upgrade screen

On more than one occasion I have seen the upgrade appear to perform flawlessly, clicked here to view my portal, and been immediately bounced back to the upgrade screen. The second time around no updates are even run, but the assembly version and the database version do not match. I still don’t know why this happens, but the only resolution I have found for this issue is to manually update the Version table in the database with a new record to match the assembly version. Keep in mind that if your installation is using an object qualifier in the database, the table name will be dbo.[object qualifier]_Version instead of just dbo.Version. Once you have manually updated the version in the database, your site should load normally.

The upgrade appeared to succeed, but now you are seeing problems on your newly upgraded site

This is where there are no good, pat answers. Your first line of troubleshooting is to log in as a site administrator and look at the Event Viewer in the Admin section of the site. With any luck there will be a big red exception there with a verbose description that will tell you exactly what the problem is. In one case for me recently, I had failed to compare the web.config prior to upgrading and was plagued with all kinds of “object reference not set” errors throughout the website and a flood of red exception log items in the Event Viewer. Going back and cleaning up the web.config and getting rid of some deprecated items in the App_Code folder completely cleared up the issue. If all else fails, now is the time to make use of those backups you made at the start of the process. You did make backups, right?

More ...

Tags: DotNetNuke

A New Technical Blog, Plus Three Sites Updated

ron@jamesaxler.com — Tue, 18 May 2010 01:06:00 GMT

Welcome to my new technical blog. I also updated three websites to DotNetNuke v5.4.1 today - and this site is one of them.

Tags: DotNetNuke