Making the ‘New Message Alert’ slicker with jQuery AJAX

 

On our web platform, when you get sent a ‘U2U’ message, whether it’s from a colleague, or notification of a phone message, you get a little flashing envelope icon in the toolbar. Clicking on this takes you to your inbox. All of this is pretty much as you’d expect. The count of unread messages is done at page load (well.. taken from memcached anyway) and the button is generated then.

The snag with this is, if you’re expecting a message it can turn you into a bit of a refresh-monkey, reloading the page until you see the unread messages icon.

So we have a page that looks like this:

and we need to make that U2U alert a bit more…………. ‘realtime’. Fortunately it’s pretty easy with a little bit of AJAX magic.

First of all we need to make the button have an identifiable <div> so all we do when rendering the button in the Page_Load is do this: <span class="u2ubutton"></span>

Next, we simply create an ASPX page that returns the inner HTML for the button, which obviously depends on the number of messages. To do that we simply have an ASPX that goes a little like this:

response.clearcontent 
response.cachecontrol = "no-cache" 
response.write MessageCount & "  U2Us" 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Obviously it’s a bit more complex than that as you only want to display the envelope icon if there’s an unread message, but I’ll spare you the boring part.

Once that’s done it’s a matter of polling that page. In time, I’ll convert it to “Long Polling” for higher efficiency and better response time, but for now, a simple javascript timer will suffice. To poll the button content page, and update the content we can use a single line of JQuery called from a timer:

        <script type="text/javascript">

            $(document).ready(function() {
                 GetNewU2Us()
            });


            function GetNewU2Us() {
                $(".u2ubutton").load("<%=request.applicationpath%>/returnu2ubuttoncontent.aspx")
                window.setTimeout(function() {
                    GetNewU2Us()
                }, 10000);
            }
                                                                   
    </script>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The initial page load calls GetNewU2Us which updates the div with the class ‘u2ubutton’ with the HTML that’s spat out by our button page. It then starts a timer off so the event will happen every 10 seconds.

So far so good then, our message alert now works nicely in the background and updates every 10 seconds.

If you’re not currently viewing that page though you might not see it. If you’ve used the Twitter web page recently you may have spotted a nice new addition. If more tweets come in, it updates the page title to ‘(3) Twitter / Home’ signifying there are 3 unread tweets. You can see that number in your browser tab, so even if you’re doing something else you can see at a glance you have new messages.

Replicating this with our solution is a doddle. In our  returnu2ubuttoncontent.aspx page we just insert a bit of Javascript after the button text:

       If MessageCount > 0 Then
            ButtonText += "<script language=""JavaScript"">" & vbCrLf
            ButtonText += "var leftchar = document.title.substring(0, 1)" & vbCrLf
            ButtonText += "if (leftchar == '(')" & vbCrLf
            ButtonText += "{" & vbCrLf
            ButtonText += "var oldtitle = document.title" & vbCrLf
            ButtonText += "var rhb = oldtitle.indexOf("") "")" & vbCrLf
            ButtonText += "oldtitle = oldtitle.substring(rhb + 1)" & vbCrLf
            ButtonText += "document.title = '(" & MessageCount & ") ' + oldtitle" & vbCrLf
            ButtonText += "}" & vbCrLf
            ButtonText += "else" & vbCrLf
            ButtonText += "{" & vbCrLf
            ButtonText += "document.title = '(" & MessageCount & ") ' + document.title" & vbCrLf
            ButtonText += "}" & vbCrLf
            ButtonText += "</script>" & vbCrLf
        Else
            ButtonText += "<script language=""JavaScript"">" & vbCrLf
            ButtonText += "var leftchar = document.title.substring(0, 1)" & vbCrLf
            ButtonText += "if (leftchar == '(')" & vbCrLf
            ButtonText += "{" & vbCrLf
            ButtonText += "var oldtitle = document.title" & vbCrLf
            ButtonText += "var rhb = oldtitle.indexOf("") "")" & vbCrLf
            ButtonText += "oldtitle = oldtitle.substring(rhb + 1)" & vbCrLf
            ButtonText += "document.title = oldtitle" & vbCrLf
            ButtonText += "}" & vbCrLf
            ButtonText += "</script>" & vbCrLf
        End If

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Apologies for not just pasting in the resultant javascript, but you can see what it does. If there’s an unread message, if inserts the top blob which strips a ‘(xx)’ from the title if there’s already one and then adds the new count. If the message count is 0 it just strips the (xx) if it exists. Not pretty but it works!

So after that bit of work, we’re left with:

Obviously the background polling and the nice magic updating aren’t apparent in a screenshot, but the end result is super slick…!!

Reducing operator stress – Answer Phrase

 

Our call agents have a pretty hard job. They answer around 300 calls per shift, and probably 50% of those calls are for unique clients. It’s not the same as say a call centre for Barclays where you answer the phone in the same way every time. Most of the time, they’ll have to answer the phone with a different greeting every time.

95% of our clients have a standard greeting of ‘Good [timeofday][company], how can I help you?’ so the operators have that down pretty well. The only snag is, since the client can change the greeting themselves, our operators are forced to read to whole answer phrase each time as they don’t know if it’s the standard greeting or not.

A few lines of Regex.Replace code and a bit of CSS styling, and we have a nice little mod. Instead of the whole greeting being in bold red text, if it’s the standard “Good morning [companyname], how can I help you?” then we lowlight (yes that’s a word!) the ‘standard’ parts.

Example:

Look how much easier that is on the eye and how much quicker your brain can do the mental ‘replace’.

This should hopefully reduce the mental weight on the operator in that first half second of the call, allowing them to give a more natural greeting.

Getting Extension Number from Local Machine

 

For proper interfacing between computer and telephone, the computer system and the telephone system need to know which operator is on which extension.

On each machine we currently keep the extension in c:extension.cfg. The ‘CTI’ program that forces the webpages to ‘pop’ uses this to know which extension it’s on and which extension to listen for on the main feed from the switchboard. It knows that if line 4000 gets answered on it’s workstation it needs to load http://ourserver/screenloader.aspx?line=4000

All nice and simple. However, with our new switch we need a bit more.

The telephone system needs to know which user is on which extension so that when the user clicks on ‘Dial 01234-567890’ the switchboard will know which extension to make that call from.

The computer system needs to know for stats and when the telephone system says "Ext 123 has picked up line 4000" the computer knows to pop the right screen on the right extension.

So our web based system needs to know which user is sat at which physical machine. There are a couple of simple ways we could do this:

  1. Use the IP address to work out which extension is being used.
  2. Make the user type it in after they login.
  3. Make the CTI program the main login and have it pass the extension number over as part of the login data.
  4. Something else.

Hmmm. Option 1 is all fine until you have a roaming user. Option 2 would be a disaster. Option 3 would work, so long as they remembered to login with the CTI program and not via the normal webpage.

So option 4 it is… get the data from c:extension.cfg into the database by using the normal webpage from IE/FF/Chrome. Right… should be easy….

Plain old Javascript was out as reading from the hard drive is a no no. ActiveX would do it, but IE only. There needed to be another way.

Fortunately there was. Our CTI program. That runs on every machine and knows the extension number. We just need to communicate with it. At first, I thought a quick and dirty bit of AJAX would suffice and I can make a call to http://localhost:1234/ where we’d have our CTI client listening on port 1234 waiting to respond. Sadly, browsers don’t like doing cross domain calls, and as far as it’s concerned, localhost is a different domain. Something more cunning was needed.

JQuery is our saviour here. More exactly, jQuery.getJSON is our saviour. We need to use JSONP which allows us to do cross site queries like you’d do into Flickr etc. All we need to do is to write a little server into the CTI app that returns some JSON data. Well, not quite. It’s JSONP. The difference is, the calling application, in this case JQuery passes a parameter that it expects as a call back.  You ask it to get you http://server/getextension.scc?user=abc&jsoncallback=?     and it replaces that 2nd ‘?’ with a random name. All you then need to do is use that as the method name for your JSON data.

 

        Dim RequestParts As String() = Split(httprequest, "?")
        Dim pagename As String = RequestParts(0)
        Dim params As New SortedList

        If RequestParts.Count > 1 Then
            Dim tmp_params() As String = Split(RequestParts(1), "&")
            For Each tmp_param In tmp_params
                If tmp_param.Contains("=") Then
                    Dim components() As String = Split(tmp_param, "=")
                    params.Add(components(0), components(1))
                End If
            Next
        End If



        Select pagename.ToLower
            Case "maintainlogin.scc"
                ComposeResponse = params("jsoncallback") & "({"
                ComposeResponse += """extension"":""" & extension & ""","
                ComposeResponse += """switchboard"":""JAM"""
                ComposeResponse += "})"
        End Select

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

So we call http://server:1234/getextension.scc?user=abc&datacallback=? which JSON changes to (for example) http://server/getextension.scc?user=abc&datacallback=JSONP123456789

Our ‘server’ app then responds with valid JSONP response of  JSONP1234567890({“extension”:”2345”,”switchboard”:”JAM”})

We then need to send that data back to the main webserver so it can be written into the database. Fortunately, this isn’t cross domain and is a piece of cake with a JQuery ‘load’ event. ‘Load’ just gives you the HTML from a page you request and inserts it wherever you like into the DOM. I created a page called ‘extlogin.aspx’ that you pass the extension and the usercode to. It returns some HTML to notify the user of success.

All in all, on the main webpage that is sent to the client we simply need to include the JQuery library and do this in the body:

    <div id="localcontent">
    </div>

    <script type="text/javascript">
  $(document).ready(function(){
    $.getJSON("http://127.0.0.1:1234/maintainlogin.scc?usercode=<%=UserCode %>&jsoncallback=?",
        function(data){
               $("#localcontent").load("extlogin.aspx?extension=" + data.extension + "&usercode=<%=UserCode %>");            
          });
        });
    </script>
 

That calls our local server on port 1234, parses the result and pushes it back to the main webserver. Easy!

Here’s a screenshot of it in action:

and ‘proof’ of it being written into the DB on the main server…

 

So from local text file, to DB on webserver in a few easy steps with the help of JQuery, JSON and some imagination. Easy!!

Partitioning Data in SQL

(Disclaimer: Yes, I know there are ways to ‘let SQL do this for you’ but they’re bloody complicated, and I have a really nice, even hash key to use so it’s simple!)

(Disclaimer 2: This is a pretty long post.)

So, after a few years of usage gathering data, SQL tables can become pretty large. Especially if they’re wide tables. Consequently you also end up with a big database as well. What we want to do is to partition my data out into lots of smaller tables, across multiple databases.  For example, a message is stored in a ‘messages’ table with the company code the message is for in the row. This makes an excellent partition as it’s very unlikely you’ll be wanting to do a query on messages for different companies. Similarly, you might partition user preferences by user.

You could simply partition everything by using the first letter of the name. Things for ‘Acme’ go into ‘MessagesA’ etc. However, the natural distribution of letters will mean some tables are vastly bigger than others.   In my data I have hashcodes for everything which look like this: “F6QHC5TYB”. Think of them as mini GUID’s. Since these are pretty even, having an algorithm that decides where the data goes based on the hashcode should work fine.

Splitting across different tables is only half of the story though. You really want to be able to put those different tables into a few different databases, for example, with 4 tables split across 2 databases:

First Letter of Hash Table Database
     
A Messages1 Database1
B Messages2 Database2
C Messages3 Database1
D Messages4 Database2
E Messages1 Database1
F Messages2 Database2

So Messages1 & Messages3 live in Database1, Messages 2 and 4 live in Database2. All we need to do is to have something that generates a suffix for the table and the database based on the hashcode. You could then simply do:

“SELECT * From ” & GetDatabase(Hashcode) & “..” & GetTable(Hashcode) & ” Where blah blah blah”

Lets knock up a couple of functions then…..

 

For the Database

    Public Shared Function PartitioningGetDatabase(ByVal HashCode As String, ByVal DataType As PartitioningDataType) As String
        Dim DatabaseName As String = ""
        Dim DataBaseSuffix As String = ""
        Dim SuffixRange As Integer = 2


        Select Case DataType
            Case PartitioningDataType.Message
                DatabaseName = "Database"
        End Select


        Dim _HashNumber As Integer = Asc(Left(HashCode, 1))
        Dim _HashOutNumber As Integer = 0

        If _HashNumber > 47 And _HashNumber < 58 Then _HashOutNumber = _HashNumber - 48
        If _HashNumber > 64 And _HashNumber < 91 Then  _HashOutNumber = _HashNumber - 55

        Dim _DataBaseNumber = _HashOutNumber Mod SuffixRange

        DataBaseSuffix = Chr(49 + _DataBaseNumber)

        Return DatabaseName & DataBaseSuffix
    End Function

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

For the Table

    Public Shared Function PartitioningGetTable(ByVal HashCode As String, ByVal DataType As PartitioningDataType) As String
        Dim TableSuffix As String = ""
        Dim SuffixRange As Integer = 8


        Select Case DataType
            'Here was can set different ranges based on different data types
            Case PartitioningDataType.Message
                SuffixRange = 4
        End Select


        Dim _HashNumber As Integer = Asc(Left(HashCode, 1))
        Dim _HashOutNumber As Integer = 0

        If _HashNumber > 47 And _HashNumber < 58 Then _HashOutNumber = _HashNumber - 48
        If _HashNumber > 64 And _HashNumber < 91 Then _HashOutNumber = _HashNumber - 55


        Dim _TableNumber = _HashOutNumber Mod SuffixRange

        If _TableNumber < 9 Then
            TableSuffix = Chr(49 + _TableNumber)
        Else
            TableSuffix = Chr(56 + _TableNumber)
        End If

        Return TableSuffix
    End Function

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The ‘suffixrange’ is what determines how big the spread is for the tables and the databases and is variable dependant on the type of datas. This way you can easily have it set to spread very heavily used data across more tables/db’s than less heavy data.

So let’s have a look at some data output using a simple test rig like this:

        Dim Chars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

        For i As Integer = 1 To Len(Chars)
            Dim ThisChar As String = Mid(Chars, i, 1)
            Dim ThisTable As String = PartitioningGetTable(ThisChar, PartitioningDataType.Message)
            Dim ThisDatabase As String = PartitioningGetDatabase(ThisChar, PartitioningDataType.Message)
            System.Diagnostics.Debug.Print(ThisChar & "," & ThisDatabase & ",Messages" & ThisTable)
        Next i
 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

1st Char of Hash Database Table
A DataShard2 Messages3
B DataShard1 Messages4
C DataShard2 Messages5
D DataShard1 Messages6
E DataShard2 Messages7
F DataShard1 Messages8
G DataShard2 Messages1
H DataShard1 Messages2
I DataShard2 Messages3
J DataShard1 Messages4
K DataShard2 Messages5
L DataShard1 Messages6
M DataShard2 Messages7
N DataShard1 Messages8
O DataShard2 Messages1
P DataShard1 Messages2
Q DataShard2 Messages3
R DataShard1 Messages4
S DataShard2 Messages5
T DataShard1 Messages6
U DataShard2 Messages7
V DataShard1 Messages8
W DataShard2 Messages1
X DataShard1 Messages2
Y DataShard2 Messages3
Z DataShard1 Messages4
0 DataShard2 Messages1
1 DataShard1 Messages2
2 DataShard2 Messages3
3 DataShard1 Messages4
4 DataShard2 Messages5
5 DataShard1 Messages6
6 DataShard2 Messages7
7 DataShard1 Messages8
8 DataShard2 Messages1
9 DataShard1 Messages2

 

All looking good ? OK, let’s change the suffixrange to spread it across 3 databases instead of 2…

 

8 tables, 2 DBs

8 tables, 3 DBs

1st Char of Hash Database Table
A DataShard1 Messages3
B DataShard2 Messages4
C DataShard1 Messages5
D DataShard2 Messages6
E DataShard1 Messages7
F DataShard2 Messages8
G DataShard1 Messages1
H DataShard2 Messages2
I DataShard1 Messages3
J DataShard2 Messages4
K DataShard1 Messages5
L DataShard2 Messages6
M DataShard1 Messages7
N DataShard2 Messages8
O DataShard1 Messages1
P DataShard2 Messages2
Q DataShard1 Messages3
R DataShard2 Messages4
S DataShard1 Messages5
T DataShard2 Messages6
U DataShard1 Messages7
V DataShard2 Messages8
W DataShard1 Messages1
X DataShard2 Messages2
Y DataShard1 Messages3
Z DataShard2 Messages4
0 DataShard1 Messages1
1 DataShard2 Messages2
2 DataShard1 Messages3
3 DataShard2 Messages4
4 DataShard1 Messages5
5 DataShard2 Messages6
6 DataShard1 Messages7
7 DataShard2 Messages8
8 DataShard1 Messages1
9 DataShard2 Messages2
1st Char Database Table
A DataShard2 Messages3
B DataShard3 Messages4
C DataShard1 Messages5
D DataShard2 Messages6
E DataShard3 Messages7
F DataShard1 Messages8
G DataShard2 Messages1
H DataShard3 Messages2
I DataShard1 Messages3
J DataShard2 Messages4
K DataShard3 Messages5
L DataShard1 Messages6
M DataShard2 Messages7
N DataShard3 Messages8
O DataShard1 Messages1
P DataShard2 Messages2
Q DataShard3 Messages3
R DataShard1 Messages4
S DataShard2 Messages5
T DataShard3 Messages6
U DataShard1 Messages7
V DataShard2 Messages8
W DataShard3 Messages1
X DataShard1 Messages2
Y DataShard2 Messages3
Z DataShard3 Messages4
0 DataShard1 Messages1
1 DataShard2 Messages2
2 DataShard3 Messages3
3 DataShard1 Messages4
4 DataShard2 Messages5
5 DataShard3 Messages6
6 DataShard1 Messages7
7 DataShard2 Messages8
8 DataShard3 Messages1
9 DataShard1 Messages2

Whoops!!! What happened there? The table Messages3 is now in 3 different databases. That’s not good. Whilst it’d be ‘workable’ it’s messy. If you ever want to move the data around again it’s going to be very hard work. So what went wrong? Well… look at our code that calls the functions again…..

Dim ThisTable As String = PartitioningGetTable(ThisChar, PartitioningDataType.Message)
Dim ThisDatabase As String = PartitioningGetDatabase(ThisChar, PartitioningDataType.Message)

 

Can you see the problem  ?

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The table and the database are determined on the hash of the row we’re storing. Sounds correct yes? If you ran 8 tables on 4 DB’s it’d look fine against the original data, but since we’re using a non divisible numbers, the suffixes go out of sync. You could just assume that you’d be sensible and move from say 2 DB’s to 4, or we make a teeny tiny code change……..

Dim ThisTable As String = PartitioningGetTable(ThisChar, PartitioningDataType.Message)
Dim ThisDatabase As String = PartitioningGetDatabase(ThisTable, PartitioningDataType.Message)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Not a very complicated change is it, but it should be a fundamental…. The hashcode must just determine which table to use, and the table suffix should always determine the DB. That way you always always have the same tables in the same DB’s.

Hope that helps someone out before they make a massively silly mistake!

I’ll post up real life distribution data based on my keys at a later date.

Every guy needs a picture of a rocketship on his wall.

 

Quite timely this, what with the Shuttle Launch the other day. I bought a picture when visiting Kennedy Space Centre last year, but only just got round to putting it up.

 

 

It’s been there for a week now and it’s doing a great job of inspiring me. Every time I catch a glimpse of it, I’m reminded that:

  • With enough time, money and will power you can do pretty much anything within the boundaries of physics.
  • The Shuttle is huge and complicated, but you can break it down into lots of small easy problems. When designing the launch system, they didn’t have to think about how to get the loading bay doors to open.
  • Even something as insanely powerful as a rocket takes time to build momentum. On the left is T+1, then T+2, T+4, T+6, T+8 and T+9. It moves more from T+8- to T+9 than it does in the first 6 seconds. Nearly every project works in the same way.

What do you see every day that inspires you? If there’s nothing, then maybe there ought to be!

Who needs values these days?

 

Whilst doing some SQL optimization on something else in my system I spotted something odd whizz up SQL Profiler. It was a simple query to get the last 8 U2U’s for a user. However, some queries were doing 75,000 reads and taking near enough a second. What the hell ?

I copied and pasted the query and ran it as straight TSQL (converting it from the parameterised query). Odd…. only 130 reads?!

Running the original parameterized version produced the high read count and a complex query plan, and this was repeatable time and time again. So why was using a query window so much faster that using code???

Stupid code, thats why. I was creating the query like this using AddWithValue

    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.AddWithValue("@LoggedInUser", PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode))
 

But the resultant SQL call contained this:

‘@LoggedInUser nvarchar(9)’,@LoggedInUser=N’ABCDEFGHI’

When the code was passing the usercode (9 character alphanumeric) it was passing it as an nvarchar rather than just a char. What if we change the parameterized version from:

 

exec sp_executesql N'SELECT top 8 UserMessages.*, PearlUsers..Users.UserName as UserFromName, PearlUsers..Users.FullName as UserFromNameFull FROM UserMessages LEFT JOIN PearlUsers..Users ON 
UserMessages.UserFrom=PearlUsers..Users.UserCode Where MessageType = 0 and UserTo = @LoggedInUser order by datesent desc',N'@LoggedInUser nvarchar(9)',@LoggedInUser=N'ABCDEFGHI'

To:

exec sp_executesql N'SELECT top 8 UserMessages.*, PearlUsers..Users.UserName as UserFromName, PearlUsers..Users.FullName as UserFromNameFull FROM UserMessages LEFT JOIN PearlUsers..Users ON 
UserMessages.UserFrom=PearlUsers..Users.UserCode Where MessageType = 0 and UserTo = @LoggedInUser order by datesent desc',N'@LoggedInUser char(9)',@LoggedInUser='ABCDEFGHI'

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Original query used 13436 reads and took 703ms.

Version using ‘char’ used 73 reads and took 171ms!!

Looking at the query plans for both you can see vastly different patterns:

 

 

The first query was ignoring the index as the index was built on a char and not an nvarchar!!

So quick change of the code from:

    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.AddWithValue("@LoggedInUser", PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode))

To

  Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(SQL, con)
        cmd.Parameters.Add("@LoggedInUser", Data.SqlDbType.Char).Value = PearlUserManagement.GetInfoAboutCurrentUser(PearlConstants.UserInfoItems.UserCode)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

and we’ve reduced disk reads by a factor of 180, and dropped the query time to just a quarter.

So, AddWithValue is great for not having to think about things, but it can cause some REALLY REALLY poor query plans in SQL!!

Shared file storage on a web cluster.

 

Part of my web app allows users to upload their own avatars as a GIF/PNG/JPG. It then resizes the pic automatically, saves out the file (and a smaller thumbnail) into ‘/static/avatars/’ and updates their Avatar_Filename field in the user table.  All I had to do was to grant the user account that IIS runs under the appropriate write permissions for that dir. Simple enough, and it’s worked for ages.

So now we want to use a web cluster we have a problem. If you’re on Server A and you add an avatar, anyone on Server B won’t see it. We need to have shared file storage.

“I know, I’ll just create another website on an IIS server and that can be ‘static.mydomain.com’ and I’ll just save the files to that across the network, and then reference the different hostname… easy, and I’ll be done in time for lunch”.

HAH! Not so. You can’t save files across to a different machine with IIS. IIS has no network access. Well.. you *can* bodge it, but you have to change the user account that IIS runs on to a domain account, then grant it write access. Not only did that sound dangerous security wise, it also sounded a bit……….. shaky. What if I rebuild the server and forget that bit?

A bit of brain storming later and I had a solution. A solution so cunning, that it needed a super accurate diagram to even think of it:

 

So, take the file the user uploads on the web host, resize it, shred it into a byte array and whack it into a SQL blob on the main server. Then call a webservice on the static file server that says “I have a blob in SQL waiting for you with the file name of XYZ, make a file from it!”

The webservice then grabs the blob from SQL using the filename passed to it in the service call, gets the bytearray, saves it with an IO stream and returns True.

Yes, I could pass the byte array directly to the webservice but it’s not worth it.

Distributed Caching Showdown – Memcached vs Velocity

In the red corner is Memcached (http://www.danga.com/memcached/) and the BeITMemcached .NET library (http://code.google.com/p/beitmemcached/ weighing in at £0 and all the way from geeky Unix-land.

In the blue corner is Velocity (http://msdn.microsoft.com/en-us/data/cc655792.aspx) also weighing in at £0 and from Redmond.

Distributed caching is a simple system… you have 1 or more machines which you use as a memory store, normally with key/value pairs. It’s not really complicated, so how do these two differ?

Installation

After 2 hours of messing around with Ubuntu and trying to get memcached to install I gave up and plumped for the seriously simple MemcachedManager (http://allegiance.chi-town.com/MemCacheDManager.aspx). You just tell it which Windows servers you want to use and it remotely installs the service for you. Can’t be any easier.

Velocity was about as difficult. Just install PowerShell V1.0 on the machine first, then run the Velocity installer and you’re
pretty much done. You need to run a few scripts (included in help file) to create a cache but it takes under 2 minutes.

Features

No question here, Velocity has it licked. Memcached offers you, errr, ‘Put’ and ‘Get’ pretty much. Velocity gives you such lovelies as:

  • Cache Invalidation (things in SQL changing can expire the cache)
  • Cache Groups (so you can specify different policies for different types of data)
  • High Availability (you can use 3 or more servers to 100% ensure your data stays up)
  • Local Cache (for even more performance for data that can be stale)
  • Ability to use it to store Session data
  • 64bit version so no real limit on memory
  • …and a whole bundle more.

Performance

After installing Memcached and Velocity on the same pair of servers, I wrote a small app to compare the performance. It simply
writes/reads 1000 small strings, 1000 largeish XML strings and 1000 Integers to and from the cache. The results are as follows:

MemCached

Velocity

Velocity (With local-cache turned on)

Clearly memcached is faster than Velocity (unless you count the local cache option which is cheating!!!). Velocity seems less fussed about the long XML strings than memcached (5x slower on memcached but only 2x slower on Velocity to read them back!) but that could be the client library.

Working With Them

Both support a PUT/GET model that’s pretty identical:

Velocity:

Dim CacheFactory1 As DataCacheFactory = New DataCacheFactory() Dim myCache1 As DataCache = CacheFactory1.GetCache("test")

myCache1.Put("Author", "Brian")
Dim name As String = myCache1.Get("Author")

Memcached:

Dim objcache As BeIT.MemCached.MemcachedClient
objcache = (BeIT.MemCached.MemcachedClient.GetInstance("production"))

objcache.Set("Author", "Brian")
Dim name As String = objcache.Get("Author")

In use there’s very little in it, however there was something about Velocity I just couldn’t put my finger on. Pulling out the
network lead confirmed my hunch:

DOH! Something designed to give us scalibility, resilience etc fails spectacularly if it can’t find the hosts. The whole idea behind these things is that you can use spare memory on spare machines. Machines that may go down once in a while (or reboot for updates for example). Memcached fails much more gracefully and just returns ‘nothing’ after a short delay which is what you’d expect. I’m sure Velocity could be coded around, but for me, for now… using ‘spare’ machines, Memcached seems to be the way to go. I’ll code it behind a ‘layer’ so I can switch to Velocity (or something else) if we ever need something bigger than memcached.

Tweaking the Message Review Page

When our operators save a client message, it then takes them to a review screen (which I’ll cover in more detail about the neat things it does when I’ve got time). It tells them if they’ve made a spelling mistake, or if they’ve omitted a field etc. It also does things like proper casing etc.


Previously it looked like this:



However, even when presented with the review, occasionally they either instinctively click on ‘Send Original’ or they miss a spelling suggestion. Not very often, but when it’s busy in the call centre it does happen. So let’s see what could be the problem:



 


Right, so a few quick changes later and we end up with something much cleaner and steers the operator towards the modified version:


 


 



The styles of the corrections are now the same (mis-spelt words being a bit stronger). More visual weight to the ‘Send/Edit Modified’ links. The ‘Original’ links are now hidden for the first 2 seconds aswell.


 



 


All in all, some very simple tweaks that took 20 minutes to do. I can measure the percentage of messages that get sent without being corrected, so hopefully after a few weeks we’ll have a noticeable difference.

It’s not about the tools or the language! (Yeah right)

Pretending it’s all about *doing* stuff seems the be the trendy viewpoint for some developers these days with their war crys of  “It doesn’t matter what language you write in, just get writing” and “You can use anything to write code, even notepad” etc.


The people most vocal about this seem to be the Ruby On Rails guys with their “Just start coding” ethos, though in part that may be them picking up on DHH‘s slightly militant world viewpoint.


Great, I agree 100%. But do you know what? Ruby On Rails is too fiddly. Maybe it’s purely because I’m on Windows, but the promise of it being ‘Low on dependencies and prides itself on shipping with most (sic) everything in the box’ is a bit of… well… a lie. You got to download Ruby. Then Gems. Then use Gems to download Rails…..and a DB. Then you’re going to need an editor……


If the RoR guys want a bigger take up they need to lower the barrier to entry a long long way.


I did eventually manage to get it running by using a combination of the recommended downloads, a lot of luck, and the InstantRails download (which hilariously didn’t work first time and was missing something though I can’t remember what). I wish I’d documented the comedy install process, but I must have typed a good 20 commands into the command line, edited a fair few config files and spent about an hour on Google tracing error messages. By the time I’d got the ‘hello world’ site running I no longer had the enthusiasm to start learning it.


In contrast, I put Visual Web Developer on my laptop (which is a clean machine other than the huge amount of Ruby junk now on it). The install took 2 minutes, worked first time and needed nothing else.


So no, it’s not about the tools. It’s not about the language either. But I’ll take something that works first time over something that doesn’t…….