Reflections on Style for Shrink Wrap Web Sites

My main bread and butter is commercial software designed to be installed on customer sites (with the host of environmental variations that may occur). One aspect is build websites that may be easily branded by the customers with the use of CSS.

 

One of the first steps  is moving CSS to resource files so pages may be style in a culturally aware manner.  Sentence length can be doubled or halved in different languages so layouts may need to change.

 

Once that step is done – then there’s the issue of how to factor the page for branding. Often folks go for a cheap solution – allow only a few things to be branded; I prefer to support comprehensive branding.

 

Often the page delivered by a developer may look like this:

   1: <div style="display: table-row block; border-bottom: solid thin red;">

   2:       <div style="display: table-cell; whitespace: nowrap">

   3:         <font: Arial>

   4:             <b>

   5:                <asp:Label runat="server" ID="EmailAddressLabel" 

   6:                     AssociatedControlID="EmailAddress" />

   7:             </b>

   8:         </font>

   9:       </div>

  10:       <div style="display: table-cell; whitespace: nowrap">

  11:           <asp:TextBox runat="server" ID="EmailAddress" />

  12:       </div>

  13: </div>

The label content is filled from a resource file in the code behind.

 

Of course, the above is not really customizable by CSS – not a single class attribute to be seen!

 

So where do you go? You start normalizing the html using classes – but not a class for every item. You normalize the classes by exploiting items through naming a section, for example:

   1: <div class="username">

   2:     <h3>

   3:         <asp:Literal runat="Server" ID="RecoverUserNameHeader" />

   4:     </h3>

   5:     <bb:Literal runat="Server" ID="RecoverUserNameBody" />

   6:     <div class="row">

   7:         <div class="rowlabel">

   8:             <asp:Label runat="server" ID="EmailAddressLabel" 

   9:                 AssociatedControlID="EmailAddress" />

  10:         </div>

  11:         <div class="rowinput">

  12:             <asp:TextBox runat="server" ID="EmailAddress" />

  13:         </div>

  14:     </div>

  15: </div>

What you should go for as the end-HTML  is:

• No Style attributes in the page
• No obsolete or UI modifying tags (<b>,<i>,<font> etc) in the page
• As few classes as possible.

Then for verification of the refactor,  you need to  enumerate out how you can specify each piece of text in a css file. For the above example we have:

• .username
• .username H3
• .username .row .rowlabel
• .username .row .rowinput
• .username .row .rowinput LABEL
• .username .row .rowinput INPUT

So, via CSS the customer can customize the look of each element. You do not have to supply all of these items, you may just define a few items in your shipping package  like

• .row
• .rowlabel
• .username H3
• .rowinput

The red items are universal.  What is the result – well, the code in the page is actually much simpler (and easier to review) and yet your customer can customizes to a very fine level. The process of refactoring the pages is very much like database normalization. The outcome is a much nicer product code base.

Working Around The Yield Restriction In Try/Catch

The code below is a workaround for the restriction that you can’t yield from inside a try/catch statement that contains a catch. Note: you can yield from inside a try/catch statement that only has a finally clause (no catch). What I need to do was:

• Call Microsoft SQL Server and return a result set.
• Support IEnumerable so I could take advantage of SQL Servers forward only cursors and Linq.
• Catch any exceptions of type SQLException and clear the connection pool.

Here is the code I would like to have compile (THIS DOES NOT WORK):

static public IEnumerable<DbDataRecord> Execute(
    String connectionString,
    String statement)
{
    using (SqlConnection sqlConnection =
        new SqlConnection(connectionString))
    {
        try
        {
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand(statement, sqlConnection);

            using (SqlDataReader sqlDataReader =
                sqlCommand.ExecuteReader())
            {
                foreach (DbDataRecord dbDataRecord in
                    sqlDataReader.Cast<DbDataRecord>())
                {
                    yield return (dbDataRecord);
                }
            }
        }
        catch (SqlException)
        {
            SqlConnection.ClearPool(sqlConnection);
            throw;
        }
    }
}

This code throws the compile error:

 

error CS1626: Cannot yield a value in the body of a try block with a catch clause

 

To solve this issue, I implemented another class that supported IDisposable. This allowed me to create the SqlConnection and SqlDataReader as class properties, which I could clean up in Dispose(). Once that was done, the code above was flattened to remove the using statements. This reduced the code to four major statements: the SqlConnection constructor, open, execute and the yield loop. I broke them done into three methods (putting the SqlConnection constructor in the class constructor), wrapping each with a try/catch. However, I still had an issue with yielding inside the try/catch. To solve this issue I called GetEnumerator() and enumerated the SqlDataReader using MoveNext() and Current() to and fetched the DbDataRecord inside the try/catch. Once I have DbDataRecord, I can exit the try/catch and yield. There is a very interesting for loop below that provides context to the yield statement, it is much easier to understand if you walk the code with the debugger.

 

The class follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Threading;
using System.Collections;

namespace BlogLinq
{
    internal class ExecutionContext : IDisposable
    {
        SqlConnection _sqlConnection = null;
        SqlDataReader _sqlDataReader = null;
        IEnumerator _enumerator = null;

        internal static IEnumerable<DbDataRecord> Execute(
            String connectionString,
            String statement)
        {
            using (ExecutionContext exectionContext =
                new ExecutionContext(connectionString))
            {
                exectionContext.Open();
                exectionContext.Execute(statement);

                foreach (DbDataRecord dbDataRecord in exectionContext.Read())
                    yield return dbDataRecord;
            }
        }

        protected ExecutionContext(String connectionString)
        {
            // Create The SqlConnection this doesn't require
            // Reestablished Connection on SqlException
            _sqlConnection = new SqlConnection(connectionString);
        }

        private void Open()
        {
            try
            {
                _sqlConnection.Open();
            }
            catch (SqlException)
            {
                SqlConnection.ClearPool(_sqlConnection);
                throw;
            }
        }

        private void Execute(String statement)
        {
            try
            {
                // Create The Command 
                SqlCommand sqlCommand = new SqlCommand(statement, _sqlConnection);

                // Execute the Command And Get The Reader
                _sqlDataReader = sqlCommand.ExecuteReader();

                // From the Sql Data Reader Get The Enumerator
                _enumerator = _sqlDataReader.GetEnumerator();

            }
            catch (SqlException)
            {
                SqlConnection.ClearPool(_sqlConnection);
                throw;
            }
        }

        private IEnumerable<DbDataRecord> Read()
        {
            // The loop to give yield return  context.
            for (; ; )
            {
                // Always Initalize the output as null this
                // will happen for every fetch since it is instead
                // the yield return loop
                DbDataRecord output = null;

                try
                {
                    // Get the Next Row From the Enumerator
                    if (_enumerator.MoveNext())
                        output = (DbDataRecord)_enumerator.Current;
                }
                catch (SqlException)
                {
                    SqlConnection.ClearPool(_sqlConnection);
                    throw;
                }

                // Now that we are outside The try/catch, determine
                // if we are yielding or their are no more rows.
                if (output != null)
                    yield return output;
                else
                    yield break;
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (_sqlDataReader != null)
                _sqlDataReader.Dispose();

            if (_sqlConnection != null)
                _sqlConnection.Dispose();
        }

        #endregion
    }
}

 

To call this class the statement looks like this:

foreach (DbDataRecord dbDataRecord in ExecutionContext.Execute(
    ConfigurationManager.ConnectionStrings["ColorDatabase"].ConnectionString,
    "SELECT ColorName, CompanyId FROM Colors"))
{
    Console.WriteLine(dbDataRecord["ColorName"]);
}

{6230289B-5BEE-409e-932A-2F01FA407A92}

A mystery: AnteMeridiem and PostMeridiem

I was working with resource files (RESX) and been getting these obtuse messages.

 

• Warning    4    The resource name 'AnteMeridiem ' is not a valid identifier.    \ContentPage.be.Resx
• Warning    4    The resource name 'PostMeridiem ' is not a valid identifier.    \ContentPage.be.Resx  

Both are proper latin (for AM and PM) – and it’s a mystery why they are deemed to be invalid. To add more mystery to it – it only objects is some cultureinfo (languages).

Is this an easter egg????

A new code review item: Initializers in Alphabetical Order

I’ve been using MZTools Visual Studio add-in for a few years. The original reason I acquired it was because of it’s sorting of code ability:

• Sorting a collection of lines
• Sorting the elements in a class by type and then alphabetical.

I had gotten frustrated by manual re-organizing  of code and fighting the random dropping of code where it fell into the file. I love the use of initializers – it keeps code cleaner and easier to read (visually chunking), but with rapid evolution of code, we can often end up with something like:

   1: deposit.CreditCard.AccountHolder = new AccountHolderDetail()

   2: {

   3:     AddressLine1 = GetString(CreditCardAddressLine1),

   4:     AddressLine2 = GetString(CreditCardAddressLine2),

   5:     Company = GetString(CreditCardCompany),

   6:     FirstName = GetString(CreditCardFirstName),                   

   7:     LastName = GetString(CreditCardLastName),                        

   8:     PostalCode = GetString(CreditCardPostalCode),

   9:     AddressLine3 = GetString(CreditCardAddressLine3),

  10:     City = GetString(CreditCardCity),

  11:     NumericCountryIsoCode = UnneededCallForIso(GetGuid(CreditCardCountryId)),

  12:     MiddleName = GetString(CreditCardMiddleName),

  13:     StateProvince = GetString(CreditCardStateProvinceId)

  14: };

If you are cross-checking two chunks of code, the random sequence becomes a pain and thus decreases code quality.  With two clicks the code is transformed into alphabetical order and the code is a lot easier to review or cross-checking.

 

So properly reviewed code would be:

   1: deposit.CreditCard.AccountHolder = new AccountHolderDetail()

   2: {

   3:     AddressLine1 = GetString(CreditCardAddressLine1),

   4:     AddressLine2 = GetString(CreditCardAddressLine2),

   5:     AddressLine3 = GetString(CreditCardAddressLine3),

   6:     City = GetString(CreditCardCity),

   7:     Company = GetString(CreditCardCompany),

   8:     FirstName = GetString(CreditCardFirstName),                   

   9:     LastName = GetString(CreditCardLastName),                    

  10:     MiddleName = GetString(CreditCardMiddleName),

  11:     NumericCountryIsoCode = UnneededCallForIso(GetGuid(CreditCardCountryId)),

  12:     PostalCode = GetString(CreditCardPostalCode),

  13:     StateProvince = GetString(CreditCardStateProvinceId)

  14: };

I would advocate that this be made a part of your regular code review standards, that is:

• Initializers must be used when appropriate (most of the add-in tools will suggest/prompt you to do so and automatically refactor for you).
• Initializers are in alphabetical order

The New SQL Azure DBA

Compared to SQL Server here is the list of things that you can be completely ignorant about and still be a SQL Azure DBA:

• Hard Drives: You don’t have to purchase hard drives, hot swap them, care about hard drive speed, the number of drives your server will hold, the speed of SANs or motherboard bus, fiber connection, hard drive size. No more SATA vs. SCSI debate. Don’t care about latest solid state drive news. Everything you know about Hard Drives and SQL Server – you don’t need it.
• RAID: How to Implement RAID, choose a RAID type, divide physical drives on a RAID – don’t care. RAID is for hornets now.
• Backup and Restore: You need to know nothing about Back up SQL Server data files, transaction logs, or how the database model effects your backup. You don’t need a backup plan/strategy – nor answer questions the about risk factor for tsunami in Idaho. No tapes, tape swapping, or tape mailing to three locations including a hollowed-out mountain in the Ozarks.
• Replication: You never have to do any type of replication, or even care about the replication. Merge, Snapshot, Transaction Log Shipping, or having a read-only secondary database with a 60 second failover – don’t care.
• SQL Server I/O: You don’t have to worry about physical disk reads/writes or paging, disk access wait time, managing file groups across tables, sizing database file (.mdfs), shrinking your database (you shouldn’t do this on SQL Server anyways), transaction log growth, index fragmentation, knowing anything about the tempdb or how to optimize it, data files (.mdf, .ldf, .ndf), data Compression, or determine and adjust fill factor. None of this is required for a SQL Azure DBA.
• Hardware: You do not have to buy or make a budget for hardware when using SQL Azure. You don’t have to rack hardware, figuring out rail kits, care about cooling, find the right rack screw, creating name labels for servers using the label maker with dead batteries, sucking down water because the data center air conditioning is drying you like beef jerky. No more racks, wires, or KVMs, or redundant power worries.
• Rebooting: There is no driving cross-town for rebooting , 2:00 AM reboot, 3:30 AM rebooting, sleeping in the data center. No f@#!ing Safe Mode.
• Installing Software: No more installing Windows and the 103 critical updates. No more installing SQL Server or careing about how it is installed. No more midnight installations of Windows upgrades and SQL Server patches. Say with me now: “I don’t care how SQL Azure is installed “– first step of a twelve step program for recovering DBAs.
• Licensing: You do not need to understand SQL Server license types. Nor do you care how much RAM SQL Server 2008 standard edition supports in 32-bit mode versus 64-bit mode
• Database Integrity: DBCC anything. Weird huh – how does the database lose integrity? You don’t have to worry about it with SQL Azure.
• Performance Monitor: You will never have to open it with SQL Azure.
• Dynamic Management Views: sys.dm_db_index_physical_stats and sys.dm_io_virtual_file_stats are a couple that you don’t need to know.

Read the first five again (Hard Dives, RAID, Backup, Replication, SQL Server I/O), think of the books you have read and discussions you have had about these topics for SQL Server. Now look behind you; see that young SQL Azure DBA – why isn’t his bookbag weighting him to the ground?

 

{6230289B-5BEE-409e-932A-2F01FA407A92}

USB hard drives going the way of 8” and 5.25” floppies….

I am a convert to eSATA drives, external SATA docking bays where you can just drop in a raw SATA drive.  On my new machines, the port is already there. On my older machines with SATA on the motherboard, it’s just a matter of buying something like Vantec NexStar NST-D100SU 2.5-Inch/3.5-Inch SATA to USB 2.0 and eSATA Hard Drive Dock which comes with a card tab with eSata and the cable to connect it to the motherboard sata connectors.

For older machines, you need to buy an eSATA card, or Sata card with eSata Connector. There are also eSata cards for laptops (but not for Netbooks, since the only ports seem to be USB ports on most netbooks.  You want to make sure that the eSATA is SATA 2… why?  Bus speed…

BUS	Maximum speed in MB/Sec
USB 1	1.5
USB 2	60
SATA 1	150
SATA 2	300
SCSI-320	320

 

So you can see why I am a convert: 5x the speed of a USB drive. In fact if you have a BlueRay burner which uses SATA, you could just move it  from machine to machine as needed.

 

Followup

I got some direct email asking about USB3. USB3 cards and drives are not generally available – even if they are:

• The cost of a USB3 external drive will always be more expensive than a bare SATA drive (after all the USB3 drive would have a SATA drive, enclosure and electronics costs).
• The performance of a USB3 drive can never be better than an eSATA drive coming off a contemporary motherboard or card (after all there’s an internal SATA in the USB3 drive!)
• SATA 600 is pending – and available on boards such as ASUS P6X58D-E, with the first SATA-600 drives announced at the end of 2009.  So the first through the gate for easy and cheap availability will be eSATA 600, far before SATA 600 are shipped with USB3 enclosures (at premium prices)
• WD Caviar Black 1 TB Internal hard drive - 600 MBps - 7200 rpm, runs at $103.00
• Buffalo DriveStation USB 3.0 1 TB External hard drive - 5.0 Gbps (SuperSpeed USB) runat at $128 (no info on the drive speed, it may just be SATA 300)…

IMHO – eSATA is still the way to go for both serious gamers and developers.

Mirror, Mirror on the drive, who is the smartest of them all?

At today’s Bellingham DotNet meeting we got into a discussion of software mirroring (built into Server 2003, 2008, Vista and Windows 7).  The consensus was that every desktop system should have all of the drives mirrored (by OS mirroring or hardware mirroring). An older machine may have 3 smaller SATA/IDE drives and the cost to mirror is $100 or less. Just buy a big drive (1-2TB) and drop it into the machine.

 

You can then mirror all of the existing drives to this one big drive (you do not need 1 physical drive to 1 physical drive), you can use a dynamic drive to mirror each drive on one drive. The drives do not have to be the same – you can mirror from an IDE to a SATA drive.

 

The second item discussed was buying a spare laptop/netbook hard drive, drop it into a USB holder and periodically at night mirror the internal drive to the USB external drive. If the internal hard drive fails, it’s just minutes to put the former-USB drive into the machine and you are up with a reasonably current images (and without having to do a restore or re-install).

 

It also means that some older dead laptop may give up their drives for mirroring (or for backups).

 

My favorite external drive USB kit is shown below:

Vantec CB-ISATAU2 SATA/IDE to USB 2.0 Adapter Supports 2.5-Inch, 3.5-Inch, 5.25-Inch Hard Disk Drives

Just plug in whatever you have for a drive.. 5.25” Floppy, 3.5” Floppy, CDROM, DVDROM, IDE, SATA.

 

No nice case (or heat problems), the true geek industrial look!

Using Google to translate resource files – code example

Often there is a need to create resource files in foreign language for code-testing purposes. The typical list of languages that I like to test are:

• Hebrew
• Arabic
• Simplified Chinese
• Spanish
• French
• German – text is often 2x longer then English (what you get with preciseness in expression)
• Hindi

If the code/css works for all of the above then you are likely safe for other languages. There is two pieces of code and one manual process (cut and paste – Google usually makes it hard to automate the capture of the translation).

 

Converting the Resx to a Html page

We put the Resx up – almost as is, just ditch the comments and place the items in <html> and <body> tags.

private void WriteHtml(FileInfo infile, FileInfo outHtml)
{
    XmlDocument sourceResx = new XmlDocument();
    sourceResx.Load(infile.FullName);
    XmlDocument xHtml = new XmlDocument();
    xHtml.LoadXml("<html><body/></html>");
    XmlNode body = xHtml.SelectSingleNode("//body");
    XmlNodeList list = sourceResx.SelectNodes("//data[@name]/comment");
    // remove comments
    for (int i = list.Count - 1; i >= 0;i-- )
        list[i].ParentNode.RemoveChild(list[i]);
    list = sourceResx.SelectNodes("//data[@name]");
    foreach (XmlNode node in list)
    {
        body.AppendChild(xHtml.ImportNode(node, true));
    }
    xHtml.Save(outHtml.FullName);
}

Getting the Translations

Next, we copy this to a website. I copied an example to one of my sites,  http://reddwarfdogs.com/ContentPage.html if you want to see what the output looks like.

 

Next, go to http://translate.google.com  and enter the URL and then pick the desired translation. Once the translation is presented I usually view source and then copy and paste it to a file with the cultureinfo as the name and .htm as the extension (this is assumed to happen in the next code sample). So we would have items like

• he-IL.htm
• ar.htm
• es.htm

Creating the translated Resx files from the .htm files

We can now return to the world of code processing.

• We use the CodePlex, HtmlAgilityPack library to fix the html into valid Xml so that processing is a lot easier, but before we do that we:
• Add in a meta tag to identify the file as UTF-8 (if you forget to do this, you may get a lot of ???????? appearing instead).
• Once we have valid Xml, we eliminate the original phrase that is put in the html from Google.
• We then load a copy of the original Resx file and walk it, replacing the <value> with the one from the translation.
• Just save to an appropriately named file.

The code:

void CreateTranslatedResx(FileInfo sourceFile)
{
    XmlDocument dom = new XmlDocument();
    dom.Load(sourceFile.FullName);
    string baseName = sourceFile.FullName.Substring(0, sourceFile.FullName.IndexOf("."));
    DirectoryInfo source = new DirectoryInfo(Environment.CurrentDirectory);
    FileInfo[] files = source.GetFiles("*.htm");
    foreach (FileInfo fi in files)
        if(fi.Extension==".htm")
    {
            //Update it with the encoding if not roman letters.
        string txt = File.ReadAllText(fi.FullName);
        if (!txt.Contains("utf-8"))
        {

            File.WriteAllText(fi.FullName, txt.Replace("<html>", "<html><meta http-equiv='Content-Type' content='text/html; charset=utf-8'>"));
        }
            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();                
        doc.LoadHtml(File.ReadAllText(fi.FullName));
        doc.OptionOutputAsXml = true;                  
        doc.Save("temp.xml");                           
        string culture=fi.Name.Substring(0,fi.Name.IndexOf("."));
        XmlDocument htmDom = new XmlDocument();
        string xml = File.ReadAllText("temp.xml");
        htmDom.LoadXml(xml);
        XmlNodeList list = htmDom.SelectNodes("//span[@class='google-src-text']");
        for (int i = list.Count - 1; i >= 0; i--)
            list[i].ParentNode.RemoveChild(list[i]);
        XmlNodeList toMoveList = htmDom.SelectNodes("//data[@name]");
        foreach (XmlNode node in toMoveList)
        {
            XmlNode oldNode=dom.SelectSingleNode(
                string.Format("//data[@name='{0}']",node.Attributes["name"].Value));
            oldNode.SelectSingleNode("value").InnerText = node.SelectSingleNode("value").InnerText.Replace("?",string.Empty);
        }
        dom.Save(String.Format("{0}.{1}.Resx", baseName, culture));
    }            
}

Conclusion

That’s it!  The main things that can go wrong are:

• Not saving with the correct CultureInfo name (What is the code for Welsh and Yiddish?)
• Not saving the HTML from Google as UTF-8

Again, this is done only for testing purposes, read Googles terms of use etc if the files are to be shipped with the application or exposed on the real internet.

Video: Starting an Windows ESB Instance on Amazon EC2

How to start a Windows ESB instance on Amazon EC2 using the Amazon Management Console is tricky.  Every time I do it  I find myself struggling to find just the right steps, so I made a screen capture of the process.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Cleaning Up Stored Procedures

Sometime ago, in reducing the scope of my community web site, I deleted a bunch of SQL Server tables no longer in use with names like Classifieds, Banners, Posts, and Weather. However, I didn’t clean up the stored procedures that called these tables. I have made a decision recently to move to SQL Azure and want to reduce the amount of “dead” code I am uploading into the cloud.

 

I embarked on the journey of figuring out which stored procedures called tables that no longer exist. This is next to impossible to determine because SQL Server does a poor job of tracking the dependencies of stored procedures. In fairness, SQL Server doesn’t really need to keep track of the dependencies – all it needs to do is to be able to run. The running of the stored procedure generates a runtime error if the table does not exist. It is the DBA that needs to track the validity of the stored procedure in the scope of the project.

Example

If you run the following on your favorite testing database in SQL Server 2005/2008 what do you expect to happen?

 

Query #1

CREATE PROC spTest
AS
SELECT *
FROM asdfasdfasdfasdfasdfasdfasdf

If you said run-time error because the table does not exist, you are wrong. This command executes correctly, stored procedures can be created that reference nonexistent tables.

Tracking My Test

The next thing I wanted to do was look at the stored procedure dependency listing in the sys.sql_dependencies. To do this, I need to get the object id of the stored procedure that I just created:

 

Query #2

SELECT *
FROM sys.objects
WHERE name = 'spTest'

 

This gave me the object id of 893714732, which I could use to determine the dependencies with this query:

 

Query #3

SELECT *
FROM sys.sql_dependencies
WHERE object_id = 893714732

 

This returned me no rows – which mean that SQL Server had tracked no dependencies on the stored procedure – i.e. no tables. Now I was on to something, I could determine all objects that had no rows in the sys.sql_dependencies catalog view and output them. This would significantly reduce the amount of time I need to review the 900 stored procedure by hand.

 

I came up with this query that returns all the objects which are stored procedures and have no rows in the sys.sql_dependencies catalog view.

 

Query #4

SELECT *
FROM sys.objects
WHERE object_id NOT IN (
SELECT object_id
FROM sys.sql_dependencies ) AND type = 'P'
ORDER BY name

 

It returned a large number of stored procedures that referenced tables that did exist. These procedures I wanted to keep (and didn’t want returned in the results), so these query results were false negatives that I was not expecting. That meant that SQL Server was not keeping track of the dependencies in sys.sql_dependencies as I expected. In fact, for some of my stored procedures it did not know about the dependencies at all – I confirmed this by running query 2 and 3.

The Solution

I found that if I dropped and recreated the stored procedures or altered the stored procedures without changing code, the sys.sql_dependencies catalog view updated with the correct dependencies. In testing, it appears sys.sql_dependencies catalog view is only updated when the object referenced in object_id is updated, not when the dependent objects are updated (or removed).

To remove all the false negatives, I need to drop and create all 900 stored procedures in my database. Fortunately, SQL Server Management Studio has an easy way to do this:

1. Right Click on the Database node in SQL Server Management Studio
2. Choose Tasks | Generate Scripts…
3. Choose All the stored procedures
4. In the advanced settings choose to DROP and CREATE the stored procedures. You have to drop them since they already exist.
5. Generate the script to the query window.

Executing the generated script will take the stored procedure offline for a very small amount of time that might affect a production server – do this during downtime or on a staging database. However, once you have executed this Query #4 will return with much better results.

Hand Checking

This is not a fully automated process, because you cannot use the results of Query #4 without checking each query visually. It just reduces the number of queries that you have to check by hand.

 

Some hidden gotchas:

• Queries that don’t reference objects (like string manipulation queries).
• Queries that use the EVAL function to reference dependencies. Sometimes developers will concatenate Transact-SQL as strings inside the stored procedure and then call the EVAL. The Transact-SQL that references the table is not compiled until runtime and will not have any dependencies in the sys.sql_dependencies catalog view.

Notes

I did try using the SQL Dependency Tracker by Redgate software, it did not tell me about stored procedures where the table referenced had been deleted.

 

I am using sys.sql_dependencies catalog view because I use SQL Server 2005. This has been depreciated for sys.sql_expression_dependencies catalog view introduced in SQL Server 2008. I did not test with the sys.sql_expression_dependencies catalog view.

 

There are other solutions out there, however they require running every stored procedure with a test harness – I was unwilling to do this, since some of my procedures take variables, and some implement data deletes.

Summary

I spent a couple of days figuring this out and could find no better solution. However, I am open for any thoughts or better ideas – post them in the comments.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}

An easy fix for the Xhtml compliance <span> problem with AspNet Controls

As I posted earlier, many AspNet controls makes it impossible to generate valid XHTML if normally used.  I had been resolving this in a non-elegant way in the past. Today, this method broke down in an odd way but it lead me to realize that there is a very simple way to fix this, override the TagKey property.

 

For example, asp:Panel renders with a <span> which prevents a lot of items from being placed on it if the page is be to Xhtml Compliant.

 

Well the solution is simple by creating a new version with just one little change as shown in the code below

namespace Lassesen.Web.UI.WebControls
{
    using System;
    using System.Web.UI;

    [ToolboxData("<{0}:Panel runat=\"server\" />")]
    public class Panel : System.Web.UI.WebControls.Panel
    {
        /// <summary>
        /// Enhanced(Corrected) Panel that renders in a Div and not a Span
        /// </summary>

        public Panel() { }
        /// <summary>
        /// Gets the <see cref="T:System.Web.UI.HtmlTextWriterTag"/> value that corresponds to this Web server control.
        /// We change the outer html tag from Span to Div for Xhtml Compliance
        /// </summary>
        /// <value></value>
        /// <returns>
        /// One of the <see cref="T:System.Web.UI.HtmlTextWriterTag"/> enumeration values.
        /// </returns>
        protected override HtmlTextWriterTag TagKey
        {
            get
            {
                return HtmlTextWriterTag.Div;
            }
        }
    }
}

 

This same trick may be done with other controls. Just add this to web config and you are in business.

 

<add tagPrefix="xhtml" namespace="Lassesen.Web.UI.WebControls" assembly="Lassesen.Web" />

 

As I said, this is a nice elegant solution (IMHO).

Improving Page Speed for Google Rankings

Google announced that it will consider the speed of the web site as part of its search engine ranking (Using site speed in web search ranking) – something I suspect that it has been doing for some time. We have known for a while that page speed is very important to how the user perceives the quality of the web site. You can significantly lower your bounce rate by improving page speed – because users will look at more of your site if the first page they request returns quickly. Now, it is just not user experience that suffers from a slow site; it is site ranking on Google – which means with this announcement slow sites attract fewer viewers because your site will appear father down the search results.

“Now slow performing code and slow database queries are a marketing issue.”

For on of my major web site serving Google AdWords slowness means lower revenue.

Page Caching

On the web site serving AdWords I had some slow leaf pages (outlying lower pages with the majority of the content details), the major entrance pages I had improved a while back using page caching. There are thousands of leaf pages – which meant I couldn’t cache them all because they would be forced from the cache for lack of room. I could see this in Google Webmaster tools, some days I had fast requests and some days slow results – depending if Google’s crawler traversed a cache page.

 

SQL Server

I knew that my issue was in the response of my SQL Server queries that fetched content to generate my dynamic ASP.NET pages. I set about optimizing my queries by using the procedure cache to figure out which queries where running the slowest

 

Every query that SQL Server executes is saved in the SQL Server query plan cache, regardless of whether that query is ad-hoc, inline parameterized queries in C# code, stored procedures, or just executed from the SQL Server Management Studio. Along with the query information is how long it took to execute, the number of executions and other statistics about the query.

 

This is the query that I use to get the top ten slowest queries. Slow is defined as slowest average execution time across all executions since SQL Server started. I found the query below on the Internet, however optimized it so it runs faster:

SELECT total_physical_reads, total_elapsed_time, execution_count, [text], total_elapsed_time /execution_count
FROM    (SELECT  TOP 10 *
 FROM    sys.dm_exec_query_stats
 WHERE execution_count > 100
 ORDER BY total_elapsed_time /execution_count DESC) AS qs
    CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st    
ORDER BY total_elapsed_time /execution_count DESC

 

I tuned the query so it will only return queries that have run more than 100 times. This excludes custom queries (like this one) from the SQL Management Studio.

 

 

The total_elasped_time column is the total amount of time spent running this query across all executions since SQL Server started last.

 

The execution_count column is the number of times the query has executed on SQL Server since it started last.

 

Dividing total_elasped_time by execution_count gives me the average time the query took to execute -- I want to go after those queries where this number is high.

 

The text column contains the query text. It is up to the reader to figure out if this is in a stored procedure, inline C# code, or adhoc.

Summary

On my site I tracked down the offending queries and over two days improved my site speed tremendously by tuning the queries. I rewrote some of them and added some covered indexes. That top spot on Google is so important for great web traffic – getting there would make my tuning worth the effort.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}