Foreign keys and deletion performance

Relational databases tend to be furnished with many foreign key constraints. These prevent some types of invalid data from being added to tables – you can think of them as an ASSERT if you’re more familiar with C# and the like. Normally, the performance impact is minimal, based on the following arrangement:

  1. The column being referenced by the foreign key will almost always be the primary key of its table.
  2. As such it will be indexed so that, when adding new records to the referencing table, it will be possible to check that the new values being added are valid relatively quickly, by looking for them in the referenced table via the index.

However, there is a potential performance pitfall when deleting from tables. We came across a situation where a very innocuous-looking piece of code was deleting a relatively small number of rows from a table (which contained over a million rows). This was taking hours to complete. The cause was a foreign key from one column in the table to the ID column (the primary key) of the same table.

When adding rows, this foreign key wouldn’t be a problem because the check would be made on values in the ID column, which is indexed. However, when deleting, the check would be made against values in the referencing column, which didn’t have an index. For example, if a row with an ID of 10 was being deleted, a check would have to be made that there were no other rows which had a value of 10 for referencing column.

Adding an index to the referencing column enabled the deletions to complete in seconds rather than hours.

Improve WordPress page speed without plugins

There are plenty of WordPress plugins out there that promise to improve your page speed in various ways, but the truth is you can do a great deal without resorting to them and just making a few simple but effective changes to your Apache configuration. In fact, this article applies equally to any Apache website, not just WordPress.

As we know, keeping our page load times nice and snappy is the key to happy visitors and one of the important factors in SEO. Whilst it’s something to take seriously, it’s not too hard to understand with some pointers. This post will take you though the easy wins that are often overlooked when setting up a new WordPress site:

We’ll be using Google Page Speed and Yahoo’s YSlow browser plugins as a way of measuring the improvements in our page speed and getting tips for where to focus our efforts.

Leverage Browser Caching

Browser caching is when the server tells your browser how long to cache copies of your site’s files; the HTML, CSS, JavaScript, images, etc. The server does this using “HTTP headers” it attaches to the files it sends to the browser, in this case specifically the cache-control header, which specifies the number of seconds the browser should keep hold of the file before it should ask for a new copy.

If you open up Firebug and switch to the “Net” tab, or the alternative if you’re using developer tools in another browser, you should see a list of all the resources your page is sending to the browser (you might need to refresh the page to get this).

Screenshot of the Firebug Net tab without browser caching

Without browser caching we can see that all the page's resources are being downloaded from the server

The “Status” column shows us that all our page’s resources are being delivered by the server – this is what the “200 OK” status means. We want to change this so that the browser gets the resources from its cache, which is far quicker.

To do this we will use the Apache mod_expires module, which will allow us to define how long the server will tell the browser to cache the various types of resource.

First you’ll need to endure that mod_expires is enabled on your server, so open up your Apache httpd.conf file and search for the line LoadModule expires_module modules/mod_expires.so it’s commented-out by default so uncomment it or add it in as necessary.

Next, add in the configuration. Just like for compression, above, this can be added to the http.conf or .htaccess files.

For our site, we use the following configuration:

# Content expiration headers
<IfModule mod_expires.c>
    ExpiresActive On

    ExpiresDefault "access"

    ExpiresByType text/html "access"
    ExpiresByType text/xml "access"
    ExpiresByType application/xml "access"
    ExpiresByType application/xhtml+xml "access"
    ExpiresByType text/css "access plus 1 month"
    ExpiresByType text/javascript "access plus 1 month"
    ExpiresByType text/x-javascript "access plus 1 month"
    ExpiresByType application/javascript "access plus 1 month"
    ExpiresByType application/x-javascript "access plus 1 month"
    ExpiresByType image/gif "access plus 1 month"
    ExpiresByType image/jpg "access plus 1 month"
    ExpiresByType image/jpeg "access plus 1 month"
    ExpiresByType image/png "access plus 1 month"
    ExpiresByType application/pdf "access plus 1 month"
    ExpiresByType application/x-shockwave-flash "access plus 1 month"
    ExpiresByType image/ico "access plus 1 year"
    ExpiresByType image/icon "access plus 1 year"
    ExpiresByType image/x-icon "access plus 1 year"

    # Need to register the icon mime type
    AddType image/vnd.microsoft.icon .ico
    ExpiresByType image/vnd.microsoft.icon "access plus 1 year"
</IfModule>

Let’s break this down:

  • ExpiresActive On switches on cache-control for all our resources.
  • ExpiresDefault "access" sets the default cache expiry to be instant – as soon as the content is accessed.
  • The following lines are all similar to ExpiresByType text/css "access plus 1 month" where we say what type of resource we are talking about (in this case CSS files) and how long they should be cached (in this case 1 month after they are accessed).
  • Note that the HTML and XML types are all set to “access”, this means they won’t be cached by the browser. We chose to do this so the user is guaranteed to see the very latest content, but what you choose will depend on your site.
  • The final couple of lines add in a non-standard type of image/vnd.microsoft.icon, which is used by favicons, and then adds an expiry for that type.

After making this change, restart your server and take another look at your page. The first time you look at it, it will still request all the resources from the server (as it hasn’t been told to cache them yet) but when you look at the headers of one of your files you’ll see the new cache-control header is in there.

Screenshot showing the cache-control header

Note the "Cache-Control" header is now present

The next time you refresh your page, your browser will know to get these resources from its cache rather than request them from the server, so your new page load should look more like the following:

Screenshot of the Firebug Net tab with browser caching

With browser caching enabled we can see that all the page's resources are being fetched from the browser's cache

Much better! Now all our content is coming straight from the browser cache (denoted by the “304 Not Modified” statuses) and you can see the loading speeds are all significantly faster.

Enable Compression

When the server sends information to the browser it can be configured to compress the data in order to make the loading speeds quicker. To enable compression in Apache, first you will need to check that mod_deflate and mod_headers are enabled on your server, so open up your Apache httpd.conf file and search for the following lines, they are commented-out by default so uncomment them or add them in as necessary:

  • LoadModule deflate_module modules/mod_deflate.so
  • LoadModule headers_module modules/mod_headers.so

Next, add in the configuration. This can be added to any of the following locations:

  • The base-level of the httpd.conf file so that it applies to all your server’s sites.
  • Into one of the <location> sections so that it applies to just that location’s files.
  • Into a .htaccess file so that it applies to just the files governed by that file.

For our site, we use the following configuration:

# Compress text, html, javascript, css, xml
<IfModule mod_deflate.c>
    # The list of mime types we want to compress
    AddOutputFilterByType DEFLATE text/plain
    AddOutputFilterByType DEFLATE text/html
    AddOutputFilterByType DEFLATE text/xml
    AddOutputFilterByType DEFLATE text/css
    AddOutputFilterByType DEFLATE application/xml
    AddOutputFilterByType DEFLATE application/xhtml+xml
    AddOutputFilterByType DEFLATE image/svg+xml
    AddOutputFilterByType DEFLATE application/rss+xml
    AddOutputFilterByType DEFLATE application/atom_xml
    AddOutputFilterByType DEFLATE application/javascript
    AddOutputFilterByType DEFLATE application/x-javascript
    AddOutputFilterByType DEFLATE text/javascript
    AddOutputFilterByType DEFLATE text/x-javascript

    DeflateCompressionLevel 9

    # Netscape 4.x has some problems...
    BrowserMatch ^Mozilla/4 gzip-only-text/html

    # Netscape 4.06-4.08 have some more problems
    BrowserMatch ^Mozilla/4\.0[678] no-gzip

    # MSIE masquerades as Netscape, but it is fine
    # BrowserMatch \bMSIE !no-gzip !gzip-only-text/html

    <IfModule mod_headers.c>
        # Make sure proxies don't deliver the wrong content
        Header append Vary User-Agent env=!dont-vary
    </IfModule>
</IfModule>

Let’s break this down:

  • There are a number of lines similar to AddOutputFilterByType DEFLATE text/html – these lines tell the server to compress specific types of file, in this example, HTML files. Here we list all the types of file we want to compress and anything we leave out will be sent normally. Note that images and PDFs are not included, it’s only text-based files.
  • DeflateCompressionLevel 9 sets the compression level to its highest. This will take more processing power so adjust this according to your needs.
  • There are a some lines that begin with BrowserMatch, these take care of compatability with some older browsers.
  • Finally there is a section that ensures proxies work well with our compression.

Now that’s all set up Apache will compress all the content it can and, using Firebug, you’ll see both the size and load time of all the text-based resources drop nicely.

Disable ETags

A common complaint of YSlow is that “There are x components with misconfigured ETags” – because we have taken control of our browser caching, we don’t really need ETags any more (they are just another way for your browser to control its cache) so the simplest solution is to turn them off.

To do this you’ll need to have mod_headers enabled (as for enabling compression, above) and add the following to your httpd.conf or .htaccess file:

# Turn off ETags as we're manually managing our caching
<IfModule mod_headers.c>
    Header unset Etag
    FileETag none
</IfModule>

This removes the Etag HTTP header and YSlow will stop complaining and give you an A-grade for ETags.

TortoiseSVN integrated into Windows 7 Libraries

TortoiseSVN 1.7 added some Windows integration goodness in the form of integration with Windows 7′s “Libraries” feature.

Windows 7 Libraries are a way of giving a name to a group of related folders. You can then search the library, which will look through all the different folders you added to it. The important thing to remember is that all the folders you add to a library will remain in the same physical location. There are a few special, default libraries in the form of your Documents, Music, Pictures and Videos libraries.

If you have TortoiseSVN 1.7+ installed then there will be a new Subversion library in your Windows Explorer. If you add an SVN-controlled folder to this library and browse to it from the library you will see buttons on the Explorer toolbar for common SVN operations like commit, update, check for modifications, show log, repo-browser and checkout.

Screenshot showing a Windows 7 Subversion Library

An example Windows 7 Subversion Library, with Windows Explorer integration.

Android development – NxtGenUG with Ross Scott

Reynold, James, Steve and Mike have been to the NxtGenUG meeting on Android development. Ross Scott gave an orientation exercise for developers thinking of targeting Android with some live coding and sample projects. The slides are on his blog. Ross’s main message was:

  • If you can build your app with HTML5, CSS3 and JS then do so.
  • If you need the above plus access to basic phone features, use a library called PhoneGap. PhoneGap is a platform that allows you to build native applications using HTML5 whilst providing access to common functionality on most mobile devices.

If those aren’t enough, then you are left with native Android programming. Your options are:

  • Java/Eclipse.
  • MonoDroid.

MonoDroid, or Mono for Android, gives you .NET on the Android. Ross has experience of using this. He described the early release as “awful” but recent releases as “pretty good”.

In discussion, one audience member mentioned KendoUI. This is a library of polished web and mobile UI widgets. Part of their advantage is they are responsive to the mobile device they are being rendered on, so your app can switch its appearance to appear native on iOS, Android and others.

Display SQL messages instantly, mid-execution

Sometimes when you are running a long running script or stored procedure it’s nice to know whereabouts it has got to, often we put in print statements for this purpose. The problem is that half the time you won’t see the printed statement until SQL Server decides it can’t buffer anymore and shoves it back to the client (e.g. Management Studio) which may not be until it has finished in some cases.

Instead of using print in these situations the following syntax can be used:

raiserror ('This is my message, show it now!', 0, 1) with nowait

This will have the same effect as if you had said:

print 'This is my message, show it now!'

Except it will send it to the client immediately.

Also note that sys.dm_exec_requests may also be used to find out exactly what statement is being executed at any particular instant and from within which stored procedure, UDF, etc.