Monday, 20 September 2010

Process is more important than Software

I have worked in various places over my nearly 20 years of working life and what surprises me is that it is often the basics that let us down, not subtleties or unusual scenarios. How many times do utility companies mess up a change of name or address? How many times does an airline lose luggage or mess up reservations?
I think most of us understand an extraordinary situation causing problems for any company but how do so many let the basics make them look like idiots?
One of the basics is the idea of a process. If you work out how to make the perfect cookie, do you try and guess the ingredients the next time you make some? Of course not, it goes without saying that you write down the recipe and follow it on the basis that if it worked the first time, it will work the second, all things being equal. I think it is the last part that seems to make many businesses consider processes either ineffective or maybe not even essential for business except maybe on a production line.
Consider my humble industry of Software Development. The first time you write a piece of software and get bitten by, for instance, somebody typing in dodgy input which makes the system fall over, you would think, "Ah, if I add that to a checklist for code reviews then it won't happen again". OK, we have to accept that we won't/can't necessarily retro-fix all the other places in code (although we should certainly consider it) but at least all NEW code will be OK. Although weaknesses do get found all the time, the total number in a given scenario is not massive so we can get to a place that only the newest exploits can affect our code.
This also works at a service level. Think of a support centre, it doesn't take much to look at the number of calls, type of calls and amount of time people aren't doing anything and work out how to improve the situation and free up resources. You might decide that you need a Frequently-asked-questions page or a current status web page or phone message that avoids calls from lots of people about something you already know about. The improvements to process and the lessons you learn don't change every day so over time you can hopefully employee less people to do more.
The title alludes to people who want to buy-in software to replace a current manual system but without thought to the process itself and what could be improved. Since computers and people are good at different things, it is safe to assume that a computer dominated process is going to be different from how things are done manually (a computer can run air-traffic control with less people because it can constantly detect and even correct conflicting air movements, something that is much more complicated to do by hand).
Anyway, consider your processes, what you are actually trying to gain as a result and you might find a whole load of people who are employed simply to support an unecessary business practice. You can then redeploy them to places that need extra resource at no extra cost to the company or you can let people leave and not need to replace them! Wonderful.

The 'Beta' ASP Calendar Control

We are having problems on our web site with the jQuery datepicker when used inline to look like a calendar. The problem only exists in IE8 (surprise, surprise) and it is fine in FF but anyway, I was recommended the asp calendar which although a server control might do everything we needed it to.
In terms of functionality, I think it provides all the things we need, you can say whether you are allowed to select months, weeks and/or days and you can also style various aspects. I must admit there were a few things which made it look a bit amateur though.
The color for the day table cells is harded coded to black so you cannot override this inthe styles. Also, you cannot set the styles for the selected day because although it is theoretically possible to do, the styles are not used and are not reported back to the code behind either apparently (very sloppy Microsoft, you do have testing procedures?). The other thing that you can't do is apply a margin to the day cells (which I was trying to do to match the JQuery calendar). This is presumably because the day cells also have a hard-coded width of 14% which takes precedence over the request for a margin which would require wider cells.
My conclusion? Well, to be pragmatic, this control works where the jQuery one doesn't despite being a server control which is less than ideal. Also, because it is a server control, things like date selection all work with the correct types and I don't have to cast between strings and dates in the code-behind. I guess I'll use it.

Wednesday, 8 September 2010

Error 401: Unauthorised when calling web service

I was calling a web service from code in ASP.net and got the dreaded 401 error. The web service was fine from a browser (but I couldn't execute anything since the test page only works on the local server and this remote). I used the Visual Studio "Add Web Reference" and logged into the HTTPS web service, it all seemed fine.
In the end, I ran up Fiddler (the web browser tool) and accessed the web service in the browser, the header came back telling me that the web service was expecting "Negotiate/NTLM" authentication and I was attempting to authenticate using basic. It now works and here is the code I had to use:

MyWebService service = new MyWebService();
CredentialCache myCredentials = new CredentialCache();
myCredentials.Add(new Uri(service.Url), "Negotiate", new NetworkCredential("MyUserName", "MyPassword"));
service.Credentials = myCredentials;
service.PreAuthenticate = true;
// Call functions etc

Note, I did not require any special certificate code which some people have suggested and this works on an HTTPS connection. I also did not need to specify the domain in the Add() function, just the username and password.

Friday, 3 September 2010

How to speed up SQL queries

One of my pet hates is people going to a computer forum and asking something like, "I have a query that takes 50 seconds, how can I reduce it to 10" without a copy of their code and with all the telltale signs that they are nowhere near qualified or experienced enough and who expect people to simply do their work for them. Anyway, I want to partially mitigate this by giving you some beginners advice on improving the performance of queries. After reading these, you should write good queries in the first place, it is definitely easy than rewriting something and hoping you haven't changed anything.

Structural Improvements e.g. always do this


  1. Avoid carrying out functions like LEFT, LEN etc in queries. These probably betray a poor design (like joining on varchar columns) but might be sometimes unavoidable

  2. Use WHERE and ON correctly. People sometimes get these confused. Your ON clause should ensure that the rows that get joined are logically matched and then a WHERE clause is to optionally reduce the result set. If you have an ON which is not complete enough, you will multiply the join significantly and then have to reduce it after joining using your WHERE clause.

  3. Do NOT use CURSORS, in most/all? cases you can use JOIN to avoid them.

  4. Do NOT use selects in a where clause. It means every row that is matched needs to call another select (300,000 rows = 300,000 selects)

  5. Test your query on large datasets before deciding it performs acceptably.

  6. Only select columns you need, not *

  7. Design your DB tables to link using ID numbers and not text/varchar columns


Design Improvements e.g. you might need to do this


  1. Use the query analyser to suggest index creation. Understand that indexes usually result in faster selects but slower insert/update/delete so depending on how often a table is read compared to how often it is updated, this may or may not be acceptable. The alternative is an indexed cached table (updated every X minutes)

  2. Select required data from tables to join into temp tables or table variables, especially if the dataset is large, the table is in another database or you need to join on it multiple times. You might also want to create indexes on these tables. Consider joining a table with 500,000 rows and 80 columns that takes up 180Mb. Creating a temp table with 8 of these columns and perhaps using a where clause to reduce it to 100,000 rows will obviously perform much faster.

  3. Use cached data if acceptable rather than joining raw tables. If the data you need does not need to be up-to-the-second correct, you might write a proc that updates a cached table every 15 minutes locally and then use this in your join