|
|
Normal Polling
Let’s start with normal polling. The browser simply runs some Javascript on a timer that repeatedly checks for new data on the server. The problem with this is there a trade off between latency and bandwidth. If you were to use a timer that ran every minute your server load would be minimal…. but there’d also be up to a minute before the user saw the changed data. You could drop it to a very short interval but you’d have a LOT of requests to your site.
The server setup is unchanged from a normal web server setup:
Long Polling
If we don’t want the bandwidth/latency trade off there is another way. You can use the timeout function of most AJAX libraries (I use jQuery) to perform ‘long polling’. Instead of asking the conversation between the browser and the server going like this:
“Anything new…………………..? Anything new…………………..? Anything new…………………..? Anything new…………………..?Anything new…………………..? Anything new…………………..?Anything new…………………..? Anything new…………………..? Anything new…………………..?”
It goes more like this:
“Tell me if anything new comes along in the next 20 seconds ………………………………………… ………………………………………………………………….
Nothing? OK, let’s try again…
Tell me if anything new comes along in the next 20 seconds ………………………………………… …………………………………………………………………."
a much more efficient use of bandwidth, but here’s the double bubble bonus. So long as the server it’s asking returns the new data and closes the connection there’s actually less latency. It doesn’t matter when the new data arrives, but with standard polling you have to wait until the next poll.
In flowchart form, long polling is super simple:
So we’re all sorted right? Not quite.
The problem with long polling using a regular web server is, it’s not very efficient. You end up with a LOT of open connections, and other than having IIS sit there spinning on each ‘poll’ page waiting for new data to come in, there’s not really a nice notification structure either. Apache is even worse on this front as it really dislikes connections being held open. Another minor snag is that you don’t want to query the original hostname for the data. Most browsers only allow you 2 connections per site, so if you tie up one on the polling there’s only one left to actually fetch data.
So, the answer is a dedicated polling server.
These things exist in the *nix world, most notably CometD, but it’s a lot to learn just to do something simple.
After 10 minutes of pontificating, I decided to do the obvious. Make my own! Project Totem is born. ( because a Totem is a ‘long pole’ and also as a nod to my friend Sam who runs Totem Development )
In essence it’s a very simple Windows Sockets application that just pushes Javascript back to the browser. The browser then executes that script and gets the data from the original web server.
The server generates a GUID that’s sent to each page in the polling javascipt. The server also tells Totem that it’s served that GUID, and that page needs to know about changes to data sets A, B and C.
The browser then polls Totem using the GUID, and if there’s nothing new the request will just time out after 20 seconds. It then polls again, and repeats polling using a 20 second timeout. The very millisecond that Totem gets a notification from the webtier that say data set A has changed, it returns the appropriate Javascript back to the browser and shuts the connection. The browser then does whatever you want to go get the data etc.
I’ll explain more about how I’m tracking keys/scripts and GUIDs etc in part 2 
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"
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>
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
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…!!
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.
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:
- Use the IP address to work out which extension is being used.
- Make the user type it in after they login.
- Make the CTI program the main login and have it pass the extension number over as part of the login data.
- 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
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!!
(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
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
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
| 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 ?
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)
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.
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!
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'
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)
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!!
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.
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.
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.
|
|
