Friday, December 21, 2012

Public Preview of Gallery Server Pro 3.0

Christmas is the time for giving, so today I give you hands-on ability to play with Gallery Server Pro 3.0. Go to beta.galleryserverpro.com and poke around. You can log in to add, edit or delete items in the album named Public Playground (use account Demo with password demo). The major new features include:

  • UI completely rewritten using modern HTML5/CSS3
  • Skinned user interface
  • Tagging and editable metadata
  • Template-based rendering
  • User ratings of media objects
  • Full screen slide show
  • Flexible sorting
  • Adaptive rendering on mobile devices
  • Video and audio rendering improvements
  • ComponentArt controls replaced with jQuery widgets
  • Remove dependence on MS Ajax library and ScriptManager

Some of these features were discussed in this blog post, so you may want to check that out as well.

The code is not yet at beta status, so for now the website is the only place where you can play with the new version. I will be releasing a beta as soon as I can – hopefully within a few months, but external forces (like client contracts) may delay that.

A beautiful user interface

ss1

Take a look at the gorgeous new design. The default skin is light text on a dark background. This switch was inspired by user feedback where I learned that images are best viewed against an 18% neutral gray background. The background color I chose ended up a little darker than this to enhance the contrast against the text. A light skin is also available for those of you wanting the more traditional dark text on light background.

The icons are modern, monochromatic, and match the trend we see with other applications and operating systems.

The window is divided into three panes. Each pane is resizable and can be docked. Their availability and default visibility can be controlled by the administrator. By default, an album treeview appears in the left pane, the thumbnails or media object is in the middle, and details about the selected items are in the right pane. The contents of each pane are customizable by editing it’s template in the site admin area. Read more about the template feature in the other blog post.

Clicking the thumbnail image takes you to a web-optimized version of the image:

ss2

The toolbar above the image has less buttons than previous versions while providing more functionality. For example, the functionality of the two download buttons in the current version has been merged into the share button:

ss5

A new feature is the ability to get an URL directly to the media file rather than the web page. Right-click the Download file link to grab an URL you can copy into Facebook or any other place.

Information about the image is displayed in the right pane. When you are logged in with an account with edit permission, you can edit a property by clicking on it and typing. Then tab out to save the change. No need to go to a separate edit page.

ss3

A really handy feature is the ability to edit multiple items at once. On the thumbnail view, use the mouse to drag select the desired thumbnails or CTRL-click individual items, then edit the property the same way as described above. This is an easy way to edit hundreds of items at once.

You may not want certain properties to be editable. You have full control over which ones can be changed and how they are created. The metadata page in the site admin area has been overhauled to provide fine-grained control:

ss4

You can change the sort order, display name, whether it applies to albums or media objects, and whether a user can edit it.

Custom properties can be created as well. At the bottom of the default metadata list seen above is a set of twenty custom properties you can use for any purpose. For example, let’s say I have a gallery of items where I want to specify a color. An admin goes to the metadata page in the site admin area and specifies the new property. Here I name it Color, set the default value to Black, apply it to media objects (not albums), and make it editable to anyone with edit permission. Then I click Rebuild to have the custom property created for all my gallery items.

ss6

When the rebuild is finished, I see the property on all my media objects. If I wanted it to appear higher in the list, I can drag that property to the desired location in the admin settings.

ss7

Tagging and template rendering

I covered these topics pretty well in this blog post, so I refer you there.

User ratings

An often requested feature is the ability to rate items. That has now been implemented as one of the properties in the right pane. The default value for ratings is imported from the rating metadata in the file, if it exists. By default all users – even anonymous ones - can rate an item, but this can be adjusted in the admin settings.

ss8

Once you rate an item, the label changes to indicate it now shows your rating. The rating algorithm is smart enough to prevent multiple ratings by the same person from unfairly influencing the value. Items in an album can be sorted by rating so that the highest rated items are always shown first.

ss9

Full screen slide show

I have wanted to improve the slide show mechanism for a long time. I evaluated several jQuery plug-ins and finally found one that had almost all the features I wanted – supersized. Thanks to the beauty of open source, I forked the project on GitHub and made the necessary tweaks to make it work the way I dreamed a slide show should.

ss10

When you start a slide show, the image is shown as large as possible to take advantage of the available screen real estate. A thumbnail bar gives you a quick glance at all the images and offers an easy way to navigate to them. The title appears at the bottom, and you can exit with the X button.

Note that this is not a true full-screen slide show, since the browser frame is still visible. I have gone back and forth over whether I would want it truly full screen (and there are good reasons not to), but in the end I just used the default behavior of the plug-in. You can easily make it truly full-screen by making your browser full screen, usually by hitting F11.

Flexible sorting

The sorting function has been overhauled and vastly improved. You can now sort by any metadata tag in ascending or descending order. To prevent overwhelming the user, a default gallery lets you choose from five properties.

ss11

When anonymous users or logged on users with view-only permission sort an album, it applies only to them. This preference is stored in session for anonymous users and in the user’s profile for logged on users. When an editor changes a sort, they are applying the new sort to all future users.

To change which properties are available to the user for sorting, edit the album template. For example, to allow sorting by Author, go to the Templates page in the site admin area and select the album template and look for the section where the sort options are defined. Then add a row for the Author:

ss12

Notice that we specify data-id=’2’. That number identifies the Author metadata item and can be found on the Metadata screen in the site admin area. Refer to the earlier screen shot of that page to see what I mean. We also hard code the text ‘Author’ rather than specify a variable like the others. After saving the changes, we can now sort by Author:

ss13

You may be wondering about the resource references you see in the template, like {{:Resource:AbmSortByFileName}}. That is a language-agnostic way to specify the text.For example, if the French resource file is installed in the App_GlobalResources directory, users whose browser is set to French will receive the French equivalent of ‘Filename’. You are not required to follow this convention when editing the template. As you might imagine, it is easier to hard code the text than map it to a resource. Those of you who do want to map it to a resource may need to modify the source code to include any resources that aren’t included by default.

Smart phone and tablet support

A goal of version 3 was to make Gallery Server Pro work great on all devices, from a 320 pixel wide smart phone to an iPad to a traditional desktop. To that end, the new version makes use of CSS media queries and other techniques to provide for adaptive rendering on small screens. For example, the gallery automatically hides the treeview when there isn’t enough room on the screen. And the metadata is shown *below* the center pane rather than to the right of it.

I still have some work to go in this area. For example, the grab handles for the splitter bars aren’t clickable on the touch screens I’ve tested. I expect to have these issues resolved for the final release.

What about comments?

The ability to comment on items was a goal for version 3, but for a few reasons I am not including it in this version. The primary reason is time – I already blew my self-imposed deadline of December 2012 for the release, and adding a comment engine would postpone the release by a few weeks.

A second reason is that you can easily plug in a comment engine on your own. For example, you can add the Facebook comment widget by tweaking the template. This blog post described how to hack the the current version of Gallery Server Pro to include the widget. Now that we have templates it will be easier and without the drawbacks of the original approach.

I am still open to adding native comments in a future version. Your feedback will drive whether this is important.

Current status

Most of the browsing and editing functionality is finished, although there are undoubtedly bugs to be squashed. Please report any issues you find.

Most of the remaining work involves the admin pages where I need to replace the remaining ComponentArt controls with HTML equivalents and MS Ajax/ScriptManager with jQuery. I also want to build admin pages for the browser templates and CSS files, but I that may be cut if necessary.

I plan to release a feature-complete beta in the first half of 2013, with the exception of support for upgrades from previous versions. While you guys are hammering on the beta I can work on the upgrade stuff. There is a lot of work to do in this area because version 3 uses Entity Framework Code First Database Migrations, which will ultimately make future upgrades simpler and dramatically reduce the amount of work I have to do to support two database technologies (SQL Server and SQL Server CE). But to get there the upgrade needs to create new tables and migrate data from the old tables.

Thursday, October 18, 2012

Gallery Server Pro now available on Windows Azure

A few days ago the folks at Microsoft made Gallery Server Pro available in Windows Azure Web Sites. This provides another option for getting a gallery up and running in minutes.

Brady Gaster posted a tutorial to show how easy it is to configure your gallery. Thanks, Brady.

gsp_azure

Wednesday, September 19, 2012

Patch for IE10 released

Internet Explorer 10 drops support for visual filters and transitions. Gallery Server Pro uses filters – or more accurately, the third party ComponentArt library uses filters - to fade popup dialogs into and out of existence. As a result, in IE 10 a javascript error is thrown and the dialog is never shown. You’ll most likely notice this when you try to log in. Instead of the login window appearing, either nothing happens or you are informed of an error, depending on your browser settings.

This affects IE10 users in both desktop and Metro modes. There are two ways to fix it – pick the one that works best for you.

Set document mode to IE9

Open the web page that hosts the gallery in a text editor. In most installations this is default.aspx in the root directory. Look for this line near the top:

<meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible" />

Change it to this:

<meta content="IE=9,chrome=1" http-equiv="X-UA-Compatible" />

This tells IE to run the page in IE9 document mode instead of the latest IE10 mode. Filters are still available in IE9 mode, so the dialogs work again.

This is an easy fix that has the added benefit of potentially fixing other IE10 issues that haven’t yet surfaced.

Apply the patch

I updated the 2.6.1 source code to change the transition effect of all dialog controls from Fade to None, effectively stopping ComponentArt from attempting to user filters on the page. Several files were modified, so I assembled them into a patch file that you can copy over the existing files of your website. The patch is located on the download page.

The advantage of this fix is that the gallery continues to run in IE10 document mode. I’m not sure what practical effect this has, but I always prefer to run under the latest when possible.

Thursday, June 14, 2012

Announcing Gallery Server Pro 3.0

I’ve been hard at work these last several months on the next major version of GSP. Things are far enough along now that I feel comfortable releasing a few details of the great things to come in version 3, due later this year.

V3 is the most significant release to date, sporting a modern look and feel and adding the most requested features. Here is a partial list of what is coming:

  • UI completely rewritten using modern HTML5/CSS3
  • Tagging and editable metadata
  • Template-based rendering
  • Comment and rate media objects
  • Custom sorting
  • Adaptive rendering on mobile devices
  • ComponentArt controls replaced with jQuery widgets
  • Remove dependence on MS Ajax library and ScriptManager

I hope to get a few more things into the release, but I’d rather under promise and over deliver than the other way around.

In this post I’ll address the most significant and exciting changes.

Brand new UI

Here is the first public screenshot of the new UI. Click for a closer look.

v3a

The first thing you’ll notice is the new theme. Gone is the bright background, dated icons and unnecessary borders. In their place are a softer background, modern icons, and reduced window fluff. This is in keeping with the trend in UI design we see in iOS devices, the Win phone, and the upcoming Windows Metro.

Running a slide show is one of the most common actions, so its icon is bigger than the rest to bring attention to it.

Next is the three-pane layout. Each pane is resizable and dockable, and their availability and default visibility can be controlled by the administrator. By default, an album treeview appears in the left pane, the thumbnails or media object is in the middle, and details about the selected items are in the right pane. The contents of each pane are rendered from jsRender templates that are user-editable – more on this in the next section.

The contents of each pane create their own scrollbars when required. For example, you can scroll through a long list of thumbnail items while preserving easy access to the Actions menu and album treeview. In the current version, those scroll out of sight.

You can see this in the screenshot below, where I have scrolled about half way down. Also notice that the right pane is docked. The three vertical dots in the middle on the right are draggable to allow you to show the pane.

v3e

Tagging

There is now support for custom tagging of media objects and albums. I have used many tagging systems over the years and have been frustrated by clunky interfaces that are difficult to use. It was critical to me that GSP should have world class tagging that is easy to use and requires as few clicks as possible.

Previous versions of GSP automatically imported tags created in other programs such as Windows Live Photo Gallery. Now you can edit those tags and create virtual albums that map to them. A virtual album is a new concept where an album is based on criteria rather than being mapped to a physical directory. When an album is created from one or more tags, its contents always reflect the latest set of matching tags. For example, I can create an album based on the tags ‘Grandma’ and ‘Birthday’, which will then contain an ever-growing collection of media objects, even when those items are stored in other albums.

Here is what the tag editing looks like:

v3b

When you start typing a tag, a dropdown appears with matches from all tags that have been previously used:

v3c

To reduce clutter and preserve privacy, only tags applied to objects you have permission to view are displayed. Use the keyboard or mouse to select the highlighted tag, which is then added to the list:

v3d

You can also apply tags to multiple items at once. On the thumbnail view, select the thumbnails you want to apply the tags to, then edit the tags the same way as described above. This is an easy way to apply tags to hundreds of items at once.

Template-based rendering

The most frequent request was an easier way to customize the UI. The existing architecture was heavily based on ASP.NET server controls hard coded into ASCX user controls. While .NET developers could download the source code to change things, it was a steep learning curve and, at any rate, not an option for anyone else.

For version 3, I completely replaced it with an architecture of flexible jsRender templates and Web.API callbacks. There are several templates covering the various areas of the screen (header, left pane, right pane, album view, media object view). These templates are managed on a new page in the site admin area:

v3f

In this screenshot, we are looking at the template for the album thumbnail view (shown in the second screenshot of this blog post). The template is 100% jsRender syntax, which is a client side HTML rendering system. If one wishes to execute javascript at the time the template is rendered, enter it on the JavaScript tab. The Target Albums tab lets you select which albums the template applies to, and the Preview tab lets you see the effect of your changes before you save it.

Let’s say you have an album containing documents. For these, a list view is a better presentation than a collection of thumbnails, so let’s create one. On the templates page, be sure the Album gallery item is selected, then click Copy as new. Give it the name List View, then enter the desired HTML:

v3g

Select the Target Albums tab and select the album that will use the template. Then save.

v3h

Now when you view the Documents album, you see a list view:

v3i

The template system brings great power for customizing your gallery. Here are a few ideas:

  • Edit the header template to insert your logo
  • Edit the media object template to move the next/previous arrows to the side
  • Add a shopping cart function
  • Add Facebook integration
  • Place ads in the left pane

Where are we at?

Much work remains to be done, and it will be several months before it is ready. I haven’t yet tackled the sorting, comment engine, and rating function, and I have many instances of the ComponentArt controls to replace with jQuery equivalents (mostly in the site admin area).

I am excited to get this in your hands as quickly as possible and am making every effort to stick to a self-imposed end of 2012 deadline. Thanks to your donations, I am able to work full time on this project. I can’t tell you how much I appreciate your financial support!

Wednesday, April 11, 2012

Making GSP so easy to install no one will pay the $100 install fee

For the longest time I have offered to install GSP with your requested settings for $100. I still do and will continue to do so. But for anyone with a few minutes, it is actually quite easy to install on your own. The Admin Guide has step by step directions for some of the common ways to install the gallery. Today I added one more – how to install the gallery when a web host supports the Microsoft Web Application Gallery (WAG).

To the unfamiliar, the WAG is a collection of open source web applications that can be easily installed.  This scenario is really the easiest way to get a gallery up and running when you know next to nothing about running a web site. All you need to do is create an account with a hosting company and click a couple of next…next links in a wizard. Two minutes later your gallery is ready to go.

There is a new HOW-TO in the Admin Guide called How-To: Install using Microsoft Web Application Gallery on a web host. It shows the process for installing a new gallery at Arvixe. The rest of this blog post is copied from there.

How-To: Install using Microsoft Web Application Gallery on a web host

Some web hosting companies integrate the Microsoft Web Application Gallery (WAG) into their control panel. When available, this is often the easiest way to set up a gallery as it requires minimal technical skills. This is a walkthrough for installing Gallery Server Pro on a new web site at Arvixe through WAG.
  1. Ensure your site is running .NET 4.0 or higher in Integrated Mode. At Arvixe, log in to the Control Panel and click Web Sites in the Hosting Space Menu on the left. Then click the name of your website from the list, as seen here:

    arvixe_websites
  2. The Web Site Properties page appears. Click the Extensions tab and verify your site is running ASP.NET 4.0 (Integrated):

    asp_net_integrated
  3. Click Microsoft Web App Gallery in the Hosting Space Menu and filter the list by the Galleries category. Find Gallery Server Pro in the list and click Install.

    arvixe_gsp1
  4. The Download Web Application dialog appears. Click Next.

    arvixe_gsp2
  5. Fill out the requested fields and click Install.

    arvixe_gsp3
  6. After it is installed a confirmation message appears.

    arvixe_gsp4
  7. That’s it! Click Launch Gallery Server Pro to see your new gallery.

    arvixe_gsp5

Spanish version of 2.6.1 available

A volunteer has created a Spanish translation of 2.6.1. Enjoy!

Thursday, March 1, 2012

Video and audio in Win8/IE10 Metro

Yesterday Microsoft released the Windows 8 Consumer Preview (beta), so I downloaded it and installed it on my laptop. One of the first things I did was use the new Metro interface to look at how Gallery Server Pro looked in Internet Explorer 10. Overall, things looked great until I tried to view one of the videos in the demo gallery:

IE_metro_mp4_with_flash_cropped

That really wasn’t a surprise since the browser templates in GSP are configured to use either Flash or Silverlight for video and audio in IE. However, Microsoft has decided not to support any plug-ins in IE10 when running in Metro. (Plug-ins *are* supported when IE is running in desktop mode.) Instead, Microsoft is encouraging use of native HTML5 <video> and <audio> tags for media. That is great, except out of the box IE only supports H.264 (MP4) for video and MP3/AAC for audio. Any other video or audio format, such as your collection of Flash Video files (FLV), are simply not going to work in IE10 Metro. Period.

As drastic as this seems, I welcome the move away from plug-ins, as I can’t tell you how many hours I have spent fiddling with Flash/FlowPlayer/QuickTime/DIVX/Silverlight scripts and configurations over the years. That has easily been the most difficult challenge with GSP – how to best render any type of file (video, audio, document, image, etc) in any kind of browser.

A plug-in free world, however, brings a different kind of mess. Different browsers support different video and audio codecs, and there isn’t a single format that works in all browsers. But I digress.

Let’s get back to IE10 Metro in Win8. Since IE10 supports H.264 (MP4), we can tweak the browser template so that newer versions of IE that support MP4 get the HTML5 <video> tag, while older versions (before IE9) continue to use Flash. To make this change, run the following SQL scripts against your Gallery Server Pro database:

INSERT INTO [gs_BrowserTemplate] ([MimeType],[BrowserId],[HtmlTemplate],[ScriptTemplate]) VALUES (N'video/mp4',N'ie1to8',N'<a href="{MediaObjectUrl}" style="display:block;width:{Width}px;height:{Height}px" id="gsp_player"></a>',N'window.gspRunFlowPlayer=function(){jQuery("#gsp_player").attr("href",function(){return this.href.replace(/&/g,"%26")});flowplayer("gsp_player",{src:"{GalleryPath}/script/flowplayer-3.2.7.swf",wmode:"opaque"},{clip:{autoPlay:{AutoStartMediaObjectText},scaling:"fit"}})};if(window.flowplayer){gspRunFlowPlayer()}else{jQuery.getScript("{GalleryPath}/script/flowplayer-3.2.6.min.js",gspRunFlowPlayer)};');
GO

INSERT INTO [gs_BrowserTemplate] ([MimeType],[BrowserId],[HtmlTemplate],[ScriptTemplate]) VALUES (N'video/mp4',N'ie',N'<video src="{MediaObjectUrl}" controls autobuffer {AutoPlay}><p>Cannot play: Your browser does not support the <code>video</code> element or the codec of this file. Use another browser or download the file by clicking the download toolbar button above (available only when downloading is enabled).</p></video',N'');
GO

This SQL works against GSP 2.6.0 or higher. Older versions use a different version of FlowPlayer so you’ll have to adjust the script accordingly.

After adding these rows, restart the IIS application pool to clear the cache. When refreshing the page above, it now looks like this:

IE_metro_mp4_with_html5_cropped

That’s better. The built-in video controls offer basic playback features like play and pause, but unfortunately it is missing a full-screen mode. Hopefully Microsoft includes it in the final IE10 release.

This solves the issue of rendering H.264 (MP4) videos. A similar SQL script can be used for handling MP3/AAC audio (sorry, but I don’t have time to put that one together). But how to handle other video and audio types? Unfortunately, I know of no way to get other types to work in IE10 Metro. The good news is that you can still see this content in Win8; you just have to move over to desktop mode first. Microsoft is well aware that tons of plug-in dependent sites will break in IE10 Metro and has added a button on the screen to easily switch to desktop mode for the current page.

Should you run this SQL script against your current gallery? Probably not. All it does is make it possible to view MP4 video in IE10 Metro. Other IE10 scenarios, such as Win8’s desktop mode and IE10 on Vista/Win7 already work just fine (as long as Flash is installed). And there is even a slight downside in that IE9 users will now use the HTML5 video player, which is not as nice as the Flash one (e.g. it is missing a full-screen button).

I will likely incorporate the browser template changes described in this post in the next version of GSP. And I will continue to monitor the current state of all browsers and their video/audio support so that GSP takes advantage of their latest capabilities while working in as many older browsers as possible.

Friday, February 17, 2012

Version 2.6.1 released

Gallery Server Pro 2.6.1 is now available. It contains a handful of bug fixes and – for those using the SQL CE database engine – up to 50% faster synchronization performance. My previous blog post details the performance enhancements.

Upgrading your gallery from 2.5 or 2.6.0 is as easy as copying the files from the upgrade package over your existing files (always do a backup first!). If upgrading from an earlier version or using the DotNetNuke module, refer to the Admin Guide.

The bug fixes in 2.6.1 are shown below. More details can be found in the 2.6.1 Defects Report.

  • A COMException may be thrown when adding an image or performing a synchronization.
  • An IOException may be thrown when adding an image or performing a synchronization.
  • Page may hang during file upload.
  • Profile settings for users do not always reflect the most current data.
  • The server's memory cache is not cleared after uploading media files.
  • Confirmation message may not appear when validating new user account.
  • Moving an album to another gallery causes BusinessException when subsequently
    viewing target gallery.
  • (DotNetNuke) Cannot restore backup file.
  • Error "Maximum request length exceeded" during file upload.
  • IIS worker process crashes when editing role on Manage Roles page.

Thursday, February 16, 2012

SQL CE performance improvements in 2.6.1

Galleries using SQL CE benefited from significant performance improvements in 2.6.0. Retrieval of data was up to 15 times faster than in 2.5. However, many users noticed that synchronize operations still were slower than both SQL Server and the now obsolete SQLite data provider (replaced by SQL CE in 2.5). This week I focused on analyzing the source of the difference with an aim to improve it. I am pleased to say that I succeeded in achieving about a 50% speed improvement with only a minimal change. I will soon be releasing 2.6.1, which contains this benefit as well as several bug fixes.

Before I dig into the details, I want to mention that the upcoming .NET 4.5 runtime is expected to bring additional performance benefits to SQL CE users. The ADO.NET Team just announced some details that show a real world app running 67% faster under .NET 4.5 than .NET 4.0. This will be in addition to the performance enhancements in 2.6.1, so there is a lot of movement in the SQL CE performance arena.

What is taking so long?

Using the Performance Analyzer in Visual Studio 2010, I ran some tests against Gallery Server Pro 2.6.0 using both the SQL CE and SQL Server data providers. I studied gigabytes of data and was able to condense it all down to these two tables:

Table A - 2.6.0, Sync 648 images

 

SQL CE

SQL Server

Duration

38.3 min.

13 min.

% of time inserting records into gs_MediaObjectMetadata

41%

0.21%

Table B - 2.6.0, Sync existing 105 images, option ‘Re-import metadata’ selected (no thumbnail or optimized images created)

 

SQL CE

SQL Server

Duration

9.3 min.

1.6 min.

% of time inserting records into gs_MediaObjectMetadata

83%

4.4%

Table A shows the performance when the sync operation discovers and finds 648 new objects. This requires creating thumbnail and optimized images.

Table B shows a sync of an album that already has 105 images in the gallery. The option ‘Re-import metadata’ is selected, resulting in a sync where all it is really doing is extracting and inserting metadata, since no thumbnail/optimized images need to be created.

Notice that for the first test, SQL CE takes nearly three times longer than SQL Server, with almost half the time spent inserting records into gs_MediaObjectMetadata. This table stores data such as camera model, shutter speed, file size, etc. Each media object in the gallery usually has at least four metadata records, and there are often twenty or thirty for images from digital cameras. In the tests above each image had about 20 metadata items.

Table B shows an even more dramatic difference. When we remove the time-consuming image generation from a sync, more than 80% of the time is spent inserting metadata records in SQL CE, while it is only 4.4% in SQL Server.

It was pretty clear I needed to focus on the metadata insertion routine. Here is the SQL CE version:

InsertMetadataItems260

It loops through each metadata item, converts it into a simple data object, and passes it to the Entity Framework (EF) data context for persisting to the database. Digging into the data further, I could see that nearly all the time in this function was spent executing ctx.SaveChanges().

Ah ha! The villain hath made himself known! SaveChanges is an Entity Framework/.NET method that does a lot of complicated stuff to ensure data integrity. It has been an incredible productivity booster for developers, but it can be much slower than pure ADO.NET. And here we have a perfect example.

Look at the SQL SaveChanges() generates:

insert [gs_MediaObjectMetadata]
([FKMediaObjectId], [MetadataNameIdentifier], [Description], [Value]) values (@0, @1, @2, @3)

select [MediaObjectMetadataId]
from [gs_MediaObjectMetadata]
where [MediaObjectMetadataId] = @@IDENTITY

The first one I expected. The second one made me cringe. It forces SQL CE to query the table to look for the newly assigned ID, when it could avoid the table search by just doing this:

SELECT @@IDENTITY()

Since the inefficient SQL was executing for each metadata insertion, a media object with 20 metadata properties was causing a SELECT query against the table 20 times. In contrast, the SQL Server routine executes a stored procedure where the newly assigned ID is returned as an output parameter, and no SELECT is used.

A few failed attempts

Using the hypothesis that the unnecessary table search after every insert was the root cause, I spent some time looking for a setting or some way to optimize the SQL. In the end, I couldn’t find one. There seemed to be no way to tell EF that there was a better way to do things.

It is well known that the fastest way to interact with data in the .NET world is to use ADO.NET. Although I used EF in the SQL CE data provider, there is no technical reason why I couldn’t replace the EF approach in one place with blazing fast ADO.NET. So I whipped up a replacement function for inserting metadata:

InsertMetadataItems_ADONET

This change made an incredible difference. Repeating the test from Table A showed that the percent of time spent inserting metadata dropped from 41% to 0.91%. By changing one function, suddenly SQL CE was performing within spitting distance of SQL Server.

But then something horrible happened. Before the test finished, the IIS worker process w3wp.exe crashed. It experienced an AccessViolationException with the message ‘Attempted to read or write protected memory. This is often an indication that other memory is corrupt’.

I did some more tests, and the error happened at some random point in every sync I ran. At one point I was able to capture a stack trace:

at System.Data.SqlServerCe.SqlCeConnection.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at System.Data.Entity.Internal.LazyInternalConnection.Dispose()
at System.Data.Entity.Internal.LazyInternalContext.DisposeContext()
at System.Data.Entity.Internal.InternalContext.Dispose()
at System.Data.Entity.DbContext.Dispose()
at GalleryServerPro.Data.SqlCe.SqlCeGalleryServerProProvider.MediaObject_Save(IGalleryObject mediaObject) in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Data.SqlCE\SqlCeGalleryServerProProvider.cs:line 828
at GalleryServerPro.Business.MediaObjectSaveBehavior.Save() in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Business\MediaObjectSaveBehavior.cs:line 82
at GalleryServerPro.Business.GalleryObject.Save() in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Business\GalleryObject.cs:line 959
at GalleryServerPro.Business.SynchronizationManager.UpdateExistingMediaObject(IAlbum album, IGalleryObject mediaObject) in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Business\SynchronizationManager.cs:line 707
at GalleryServerPro.Business.SynchronizationManager.SynchronizeMediaObjectFiles(DirectoryInfo directory, IAlbum album) in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Business\SynchronizationManager.cs:line 614
at GalleryServerPro.Business.SynchronizationManager.Synchronize(String synchId, IAlbum album, String userName) in C:\Dev\GSP\Dev-Main-2.5\Src\TIS.GSP.Business\SynchronizationManager.cs:line 207
at Gsp.Gallery.Synchronize(Int32 albumId, String synchId, Boolean isRecursive, Boolean rebuildThumbnails, Boolean rebuildOptimized, Boolean regenerateMetadata) in C:\Dev\GSP\Dev-Main-2.5\Src\Website\gs\services\Gallery.asmx.cs:line 549

The error was occurring within EF code, not the new ADO.NET code I just wrote. For some reason, combining EF and ADO.NET together was not working. I googled around but ultimately found nothing. I racked my brain for a couple hours trying to figure out some way to preserve this awesome new performance trick, but in the end I could not solve it.

I had to scrap the ADO.NET code. With a sniff and a tear in my eye I hit the delete key…

The solution that worked

I went back to the original InsertMetadataItems() function and studied it. What if, instead of calling SaveChanges() for each metadata insertion, I batched them up and only ran it once for each media object? I commented out the SaveChanges() line and the one immediately after it (since we no longer would have the newly assigned ID at that point) and ran the test again.

Success! The time it took to run the tests dropped by almost half! Here is the data from table B again, this time with an additional column for the improved version.

Table C - Sync existing 105 images, option ‘Re-import metadata’ selected (no thumbnail or optimized images created)

 

SQL CE
2.6.0

SQL CE
2.6.1

SQL Server

Duration

9.3 min.

4.8 min.

1.6 min.

% of time inserting records into gs_MediaObjectMetadata

83%

72%

4.4%

# of calls to SaveChanges()

2404

108

N/A

Interestingly, the SQL generated by EF still includes the unnecessary table query, which may account for much of the remaining performance difference with SQL Server. But reducing the number of calls to SaveChanges() still had a dramatic effect.

In order to preserve the original behavior of the function, I wasn’t done yet. I still needed to assign the newly created ID to the metadata objects. So I added a little plumbing code to assign all the IDs once they are known. Here is the final version of the function:

InsertMetadataItems261

Wrap up

This change really improves insertion scenarios in SQL CE, which are primarily synchronizations and file uploads to the gallery. Retrieval performance is already pretty good.

Even with this change and the expected improvements in .NET 4.5, SQL Server will remain the fastest data provider for Gallery Server Pro. It is highly optimized to use stored procedures, indexes, and carefully constructed ADO.NET.

Use SQL Server when you need the best performance and don’t mind it’s additional complexity and maintenance requirements. Use SQL CE when simplicity is paramount.