Donner’s Daily Dose of Drama

Sometimes we need to reduce the granularity of date ranges by combining serveral contiguous rows into one. While this is a common classic SQL problem, I was unable to find an elegant solution that also performs well, and came up with my own. This article explains the problem and outlines the solution using a Common Table Expression (CTE) with recursion in SQL Server 2005.

In an earlier post I presented the network throughput results of my tests with the Synology RS-407 NAS and IPerf in various configurations. I had a Windows XP x64 client in the mix with a bad network driver that messed up my test results. During these earlier tests I contacted Synology support about the odd results that I was getting, and the technician suggested that I repeat my tests with a cross-over cable, thus eliminating the influence of a switch.

Since then I got rid of the 64-bit XP installation and reverted back to the 32 bit OS. I was hesitant for a long time about making or purchasing a cross-over cable, because it is so unlikely that I will ever use it again. I got one anyway. I repeated the iPerf tests with various client and server configurations, both with the cross-over cable and my HP switch.

I did not create lab conditions, which means that the tests were done with normal network traffic. For instance, the Synology Surveillance Server running on the NAS was recording a video stream while I ran iPerf. The results should therefore be regarded as somewhat lower than under ideal conditions. The Thinkpad laptop ran on battery during the tests, which may explain the meager results

This time, iPerf produced more meaningful and repeatable results. The take-aways for me are that

• I do get transfer rates of over 500 MBit/sec, but only when jumbo frames are turned on. In fact, throughput with MTU=9000 is generally twice the rate of when jumbo frames are turned off (MTU=1500). Jumbo frames are now turned on for good on all stations that support it.
• My HP switch performs well and it is not a bottleneck. Obviously, when I tested with the cross-over cable there was no traffic other than the Windows remote desktop or an SSH terminal session. With the switch, my network was running with standard traffic. It is possible that when accounted for the video stream from the security camera, the results would have been 5-10% higher, meaning that throughput with the switch would be at least as fast as with the cross-over cable, if not faster.

The results are below. Detailed results are here.

Why do I find all the examples of how to use recursion in SQL Server 2005 too complicated (including the one in the Microsoft help)?

Here is mine that does the bare minimum.

I wanted to know exactly how much throughput I get from my new switch (HP ProcCurve 1400-24G) and found a network performance test tool that is available on many platforms - iPerf. I installed it on my Pc (Asus P5NSLI MB with dual-core Intel Pentium 4 3.4 GHz, a Raid 0 configuration, and a Marvell on-board Ethernet controller), a Synology Rackstation RS-407 NAS (ARM CPUrunning Linux, Raid 5), and a Sun Fire V20z rack server with two 2.4GHz Athlon processors running Windows Server 2003 x64 (2 Broadcom NetXtreme Gigabit Ethernet controllers, single SCSI disk).

I purchased the HP switch because it supports Jumbo frames. I tested the transfer rate between all three stations in both directions with three different MTU settings. All NICs support Jumbo frames and the value can be configured in the NIC driver settings (Windows) or in the Synology administration UI.

MTU=1500

1500 is the default (non-jumbo). I limited the MTU size for all three network cards to 1500 and ran iPerf in all 6 constellations.

iperf -c rackstation -P 1 -i 10 -p 4000
    -l 8K -f k -t 10 -d -r -L 4001

The results did not exactly blow me away. I have a mix of Cat-5 and Cat-5e cabling in the house and was concerned that I would experience bandwidth limitations. Since the servers in the test were directly connected to the switch with newly purchased Cat-6 patch cables, I expected a significantly higher transfer rate between the servers than between the workstation and either of the servers. The workstation was connected via 15 feet of Cat-5 cable, a Cat-5 wall jack, and a Cat-6 patch cable.

iPerf reported transfer rates between 150 and 250 kbps, not exactly Gigabit-like. So I turned up the MTU and hoped for the best.

MTU=4000

The next MTU size that is supported by all three network card drivers was 4000. I expected transfer rates to go up consistently.

iPerf now reported between 100 and 400 kbps, except between the workstation and the Sun server - there the performance descreased to a fraction of what it should be.

MTU=9000

9000 is the largest supported frame size. I set the MTU to 9000 on all sides and ran the same iPerf tests again.

Now iPerf reports over 500 kbps download speed from the Synology NAS, which would be nice if traffic between the Sun server and the workstation wasn’t so unrealistically slow. In addition, there now are oddities between the Sun and the Synology as well. I was able to consistenly reproduce these results.

I cannot say yet why I am observing these things. I opened a support case with HP networking and I am still waiting for an answer there. I did do some basic file transfer tests with a tool, by simply copying a large 1 GB video file between the participants, and while I did not measure such low transfer rates as iPerf reported, it definitely appears that the upload speed is consistently going down when Jumbo frames are enabled.

250,000 Mbps (bits) is a little over 30 MBps (bytes). Copying a 1GB file across the network takes about 30 seconds with MTU=1500. It does not get any faster than that, but downloads become substantially slower, e.g. > 2 minutes per GB when I set MTU=9000. The SMC Gigabit switch that I replaced and that did not have Jumbo frame support had the exact same performance. So, maybe the HP switch does not really support Jumbo frames? I am still waiting for a definitive answer from support, but if you have a Giga switch that performs better with Jumbo frames I’d like to hear about it.

Needless to say, I turned Jumbo frames off again and I am running with the basic MTU of 1500. While the iPerf numbers are not impressive the subjective performance gain from upgrading to a Gigabit switch was much more dramatic. It almost seems that iPerf does not function correctly with larger MTU sizes, too.

When you have query that does complex calculations, maybe even has function calls in the select list

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
from
       table_T

and then you need to return the result of such a calculation, but also manipulate it further and return the result of this operation as well,

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       result_A/result_B as result_ratio
from
       table_T

the result is the following dilemma:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'result_A'.

It is not allowed. Invoking the function twice in the query

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       costly_function_A()/costly_function_B() as result_ratio
from
       table_T

would work, but is not an option because it nearly doubles the execution time. So what does SQL Server offer in terms of query-writing tricks that let us get around this issue?

There are 2 choices - neither is particularly elegant.

First, we can put the costly stuff into a derived table where it gets executed only once, and do the secondary calculation in an outer query:

select
       costly_T.result_A,
       costly_T.result_B
       costly_T.result_A/costly_T.result_B as result_ratio
from
(
       select
              costly_function_A() as result_A,
              costly_function_B() as result_B
       from
              table_T
)
as costly_T

Or we can use a view. If you do not already use views, don’t introduce one, but go with the derived table. If you do have views, you may already have the necessary layer of abstraction that is needed here.

I installed the Synology Rackstation last night and so far I like it a lot.
.
I installed four Seagate Barracuda ST3500630AS (500GB each) and, let me tell you, this configuration is anything but quiet. I have it sitting in a 42U rack cabinet in my basement, which is now filled with the airplane-like hum of four harddisk drives and nine fans.
Second, it is slow. The Gigabit network connection does not help if the unit’s processor is hopelessly overwhelmed - which it is even when I am the only user. I am in the process of copying content from a server with a 100MBit network connection, while the NAS is on a Gigabit connection. At the same time I am configuring Joomla, which I just installed on the Synology, and every page request takes long enough to return that I have time to write this report. Response time on any web page, photo station, Joomla, or administration page, is somewhere between 3 and 5 seconds, even when the shares are idle. It is possible that the server is still busy indexing my content, so I will give it a few days and test performance again.

< ... a few hours later ...>
So I just updated the firmware to enable SSH access to the NAS, which worked beautifully, and was able to connect right away. Luckily, top is available and I noticed that a convert process is running and routinely taking up to 100% CPU. I assume that it is still creating thumbnails for the 10’s of thousands of pictures that I uploaded earlier today. This would account for the sluggish performance.

< ... the next day ...>
I was able to install the Bootstrap and get iperf installed on the Rackstation. With both my workstation and the Rackstation connected to the new Gigabit switch, I had somewhat higher expectations regarding transfer rates than what I am actually measuring:

------------------------------------------------------------
Client connecting to 10.10.1.176, TCP port 4000
TCP window size: 102 KByte (default)
------------------------------------------------------------
[ 7] local 10.10.1.109 port 3044 connected with 10.10.1.176 port 4000
[ 6] 0.0-10.0 sec 118 MBytes 98.9 Mbits/sec
[ 6] MSS size 4034 bytes (MTU 4074 bytes, unknown interface)
[ 7] 0.0-10.0 sec 303 MBytes 255 Mbits/sec
[ 7] MSS size 4022 bytes (MTU 4062 bytes, unknown interface)


After a little bit of Googling I set the MTU size of the network card on the workstation to 9000. The results are better:

------------------------------------------------------------
Client connecting to 10.10.1.176, TCP port 4000
TCP window size: 109 KByte (default)
------------------------------------------------------------
[ 7] local 10.10.1.109 port 3441 connected with 10.10.1.176 port 4000
[ 7] 0.0-10.0 sec 575 MBytes 482 Mbits/sec
[ 7] MSS size 8948 bytes (MTU 8988 bytes, unknown interface)
[ 6] 0.0-10.6 sec 712 KBytes 549 Kbits/sec
[ 6] MSS size 8960 bytes (MTU 9000 bytes, unknown interface)


After making sure that the patch cables on both ends are Cat 5e (is Cat 5 Enhanced the same as Cat 5e?), the numbers went up a little still, but since the cable in the wall is Cat 5 and will not be replaced unless absolutely necessary … I suppose this is as good as Gigabit Ethernet gets. I ordered Cat 6 patch cables anyways, since there will be more Gigabit traffic in the house soon. We’ll see if it makes a difference.


eth0 Link encap:Ethernet HWaddr 00:11:32:01:86:86
inet addr:10.10.1.109 Bcast:10.10.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:9004 Metric:1
RX packets:63186085 errors:0 dropped:0 overruns:0 frame:0
TX packets:37736653 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:512
RX bytes:2705338793 (2.5 GiB) TX bytes:3908546493 (3.6 GiB)
Interrupt:21


My switch is the SMC GS16 (no jumbo frame support, or I am sure SMC would mention it on their web site). Why I get a 500% improvement in throughput by enabling Jumbo frames on the client is beyond me. I was hoping that by plugging my laptop directly into the switch, with the RS407 plugged directly into the switch, I would get better transfer rates again, but no. It appears that the Intel PRO 1000 network controller in my Lenovo T60p laptop does not support Jumbo frames, at least there is no way of enabling them in the most recent drivers. The desktop is an Asus P5NSLI and enabling Jumbo frames on the onboard Marvell Yukon PCI Gigabit Ethernet Controller really seems to make all the difference.

The HTTP performance is still poor. The image thumbnails are apparently all created, since the machine is mostly idling along (why it does not go into hibernation is yet another question to answer), but it still responds slower to page requests than most external sites that I go to. Apache uses almost 100% of CPU time when it puts the HTTP response together - and these are light-weight pages.

Overall, I still like the Synology. It is comparatively expensive - the Acer Aspire Easystore withg 2 TB (the device is not marketed in the US) is available for almost half of what I paid. Still, a read transfer rate of almost 30MB/s over the network is pretty cool. Copying an 800MB VCD image from the NAS to the local disk takes less than 30s. I am looking forward to filling up all this space now.

< ... after one week ...>

I just noticed that the iPerf results above were Kilobits, not Megabits. Now it starts to make sense. Setting the MTU to 9 kB does not work after all if the switch does not support it. Or whatever else causes the slowdown. Since I did not really notice a problem with transfers, I am not so sure how meaningful these results are anyways. I set the MTU back to 1.5 kB. The Cat 6 patch cables arrived, btw, and I did not notice a difference.

Useful resources:

• Instructions for how to install the Bootstrap
• ARM/Marvell Bootstrap (required for ipkg and pretty much any customization work)
• More about my experiments with the Rackstation’s network performance

< ... update ...>
Synology released new firmware for the Rackstation a few months back. I have been using it for some time. The new Web interface really makes the product much more usable. There is now a Surveillance station that captures camera streams and records to disk. Unfortunately, it does not work with my camera. Still, if you don’t have it, I highly recommend getting the update.

The Globe reports that the MBTA is making Wifi mobile internet access available on one of its commuter rail lines. The Boston commuter rail infrastructure is so derelict - it is stunning that Wifi was a priority. Here are the top 3 things that should have been addressed instead:

1. Speed up slower trains
   Some trains are much faster than others, apparently because of traffic congestion on the lines. It should be possible to schedule trains better. If trains were faster, there wouldn’t be time to use any Wifi service anyways.
2. Display the train’s destination
   Currently, the conductor is the only person who knows where a train is going, and not all of them are sharing this information freely. People routinely got on the wrong trains. This is such an easy fix.
3. Make sure that trains are on time
   Currently, I can rely on my train being late, and it is planned into my daily schedule.

www.austria-boston.org, an ASP 1.0 site, was hosted by a company called Burlee starting about 6 years ago. Burlee was later acquired by Interland, and ended up as a part of Web.com. The hosting fees were never adjusted and ended up being disproportionally high, so I started looking around for an alternative to realize some savings for the Austrian club.
Since I had already been a Godaddy domain customer for a number of years and their Windows hosting plans were priced at what seemed a reasonable level, I decided to try GoDaddy hosting. I went with their 2-year Economy plan at $3.19/month. I described some implementation details of the site during the sales call and was assured that the migration would be possible without any functional limitations in the new environment.
The site has now been running at GoDaddy for several weeks and it is time to summarize the experience.

My new phone is the T-Mobile Shadow. I have been using it for a week now and have some initial experiences to share.
First of all, the phone scores on all fronts, compared to its predecessor in my pocket, the SDA. Windows Mobile 6 has some of the biggest pain points eliminated, the phone feels much more responsive, it is faster, the camera is fun to work with, and the phone looks really slick.
On the downside, there are 2 things to note. The most annoying “feature” for me is that it does not have a standard USB connector and that an external adapter cable is required to either connect a USB cable or the charger. The Shadow ships with only one of these adapters, and it is only a matter of time until I lose it. I need to carry the adapter cable with me, because the phone is plugged into my laptop at work and gets recharged at home over night (my battery typically lasts less than 24 hours).
The headset also shares the single connector that is used for the adapter cable, which means that no other (wired) headset can be used with the phone.
The other (preliminary) annoyance is that my attempts to application-unlock the phone were not successful. I follwed instructions on the web for making changes to certain registry settings, but the changes were not accepted.

11/25/2007: I contacted T-Mobile support and asked where I could purchase another one of these ExtUSB adapters, since it is only a matter of time when I will lose mine. The response came back the next day and it was clear that the question was not understood. However, I finally tried to plug the charger’s regular USB plug into the ExtUSB connector and - heureka - they are compatible. I do not have to carry the adapter with me at all.
So far I managed to Application-unlock the phone using the SDA Unlocker, and I installed Core TCPMP, a much better media player than the Windows one. I am still waiting for a solution to SIM-unlock the phone. I use a pre-paid One SIM when I am in Austria, and the phone does currently not work with it.

(to be continued)

So much has been written and preached about the difference between the words ‘principal’ and ‘principle’ that I don’t need to repeat it here. But in at least 50% of all job postings that are about a principal position you continue to use the term ‘principle’. You are not doing your clients a favor by publicly documenting your incompetence! The best candidates will skip right over such a job listing.