I just recently started using Microsoft SQL Server Management Studio Express (SSMSE).  This free tool is intended for SQL Server 2005 databases, but it works just fine with MSDE and SQL 2000 databases, as far as I can tell.  SSMSE obsoletes the tools I have been using up to this point, namely Enterprise Manager and Query Analyzer.

Enterprise Manager is slow to open on most systems I've used.  I have a server instance with 20 databases and I can take a nap before the thing finishes opening up.  SSMSE uses demand loading of the tree nodes, so performance is a lot better.

The whole integration between Enterprise Manager and Query Analyzer has a more natural feel.  Nice job, MS!

OK, I'm definitely not the first person in the world to figure this out, but it bit me in the behind last week.  So I figured I would share this bit of wisdom with you so it doesn't happen to you.

Most of the data that's stored in this MSDE (SQL Server) database lives in an "engineering variable data" table.  In this parallel universe, the term "variable" doesn't mean quite what we programmers think it should mean, so I'll call it a "datum"  A "datum" can have a value, a specification (that determines how it is used in a calculation), upper and lower limits, residuals, bound type, and other properties of interest to engineers.

I experienced a dreadful performance problem in the production system.  Out of necessity, the program would sometimes have to delete 10,000 records from the VariableData table and insert a new copy of those records.  In the development system with a much smaller database, this worked as intended.  However, when we started getting upwards of a million records in the table, this data replacement step would bring the program to its knees for 30-120 seconds.  Sometimes the queries would time out, gobble up RAM like candy on Halloween, or both.  I tried various hacks and workarounds to improve performance, including using a bulk insert, but to no avail.

We finally took a look at the indexes:

IX_SolutionCaseID: key to an integer in SolutionCase table
IX_Specification: key to a varchar(7), whose value can be one of 7 possibilities
IX_Guid: unique key for a uniqueidentifier
IX_VariableID: key to an integer in Variable table
IX_VariableVariableID: a redundant key

I realized that the way I get data out of the database is by SolutionCaseID and VariableID, which uniquely identifies a datum.  So the queries I was currently running were using one of the existing indexes and then doing a table scan to find the record I actually wanted. Ick.  So I added a new two-field key:

IX_SolutionCaseIDVariableID: added key for both SolutionCaseID and VariableID

Adding this index to a populated table only took a few seconds and it cut the wait time in half!

Next I started hacking away indexes that I never use.

IX_VariableVariableID: deleted - this had no noticeable effect on performance.
IX_Specification : deleted - this improved performance.  This is analogous to have an index on sex "M" or "F", which is always a bad idea.
IX_Guid: deleted - I never look up data by Guid, this is for UI magic only.  This had a huge benefit on performance

So the conclusions can be:

• Avoid indexes on GUID columns, unless they are primary keys and that's how you look things up
• If you look up on two fields a lot, make sure you have an index that contains both fields
• Don't index on columns that have lots of repetitive data.  You might actually win with a table scan on this one!

And if you didn't know this already, you can always force an index to be used in a select query (use at your own discretion):

SELECT Field1, Field2, Field3 FROM TableName WITH (INDEX = MyIndexName) WHERE Field4 = 42

And now my client is much happier, and there was much rejoicing!

Last week, I came across XML Marker, a free XML editor that is great for simple tasks.  It doesn't have the full range of abilities of a commercial product like Altova XMLSpy or StylusStudio.  I have been using the evaluation versions of these commercial products to do simple XML editing, but I can't justify coughing up $500 for a simple XML Editor.  XML Marker bridges the gap: it doesn't suck like Microsoft's XML Notepad but it give you multiple views of your XML document like the big boys.  I edited the config file for this web site, and it works great.  It doesn't have schema validation built-in, and that would be a nice addition.

Recently, I bought my wife and myself his-and-hers Windows Mobile 5.0 gadget phones.  Now I'll admit freely that I'm not a huge cell phone user, and I've always considered maintaining a cell phone as a necessary evil in the consulting world.  Since I have been experimenting with the .NET Compact Framework SDK, I figured this could have potential in software development projects I'm working on.

What is truly amazing is the amount of power that can be packed into one of these little devices.  This device, the T-Mobile MDA (link) has built-in WiFi, bluetooth and infrared beaming, it synchs with Outlook, et al, using ActiveSync, it comes with a mini MSIE (approximately IE 5.5), Pocket Word and Excel, a PDF viewer, some games, and other stuff.  There is a plethora of free and commercial software for the phone, and I downloaded a Command Prompt program and a Registry Editor.  Using Visual Studio 2005, you can easily develop and deploy your own .NET software to this device, including SQL CE 5.0, which comes with a cute little Query Analyzer for ad hoc querying and debugging.  Equipping this device with a 1 GB MiniSD memory card gives you ample room for about 100 MP3s that you can play with the mini version of Media Player.

Oh, and did I mention that you can make and receive phone calls with this device?

It is amazing that this thing runs on about 500 MHz processor and delivers this kind of punch.  When I think back to my first PC, that I bought 13 years ago, this little gadget blows it away.

I love almost everything about this device except for one thing: most web sites look downright awful on the web browser.  Nearly all web sites are designed for 1024x768 screen size, with the assumption that the user will be using it at a desktop or laptop.  Most web sites use a boatload of graphics, with the assumption that bandwidth is not a problem.  When my device is connected via WiFi, this really isn't a problem, but more often I connect to the Internet using GPRS, which is much slower a typical broadband connection and more latent than even a dial-up modem connection.

As a developer, I see the conundrum. Today, most people do not use mobile devices to browse the Internet.  But I bought this device for $249 last year, and I am sure that it will be half that price by the end of the year.  Why would I want to develop a web site for such a niche market?  Until Firefox started gaining popularity, I only designed for MSIE since that was biggest target.  But thinking forward, the mobile web market is poised to boom in the next two years.

So what choices do we have as developers?  We could work by redesigning one site at a time to comply with the new miniature standards.  This is a large development effort and will cost businesses a lot of money to implement.  Do I think this will actually happen?  Only the largest companies or the ones most directly involved in web businesses will do it.  For instance, GoDaddy has a mobile web interface that I used to register a domain using my gadget phone.  But most vendors are not doing this yet.

So what about medium to small-business web sites?  I think there needs to be a handly plug-in the can convert a web site to mobile formatting when a mobile web browser makes a request.  My idea is that I could create an HTML proxy that makes the actual HTTP request from the web server, but then reformats the HTML and imagery that is sent back as a response.  Current mobile web browsers make the full request and drop formatting on the client.  An HTML proxy that can be installed on the web site would provide an alternative where the crash diet occurs on the server, resulting in faster loading web pages on the mobile web browser.

My thought is that there would need to be certain flavors that are available immediately.  Most dynamic pages are written using PHP, ASP and ASP.NET these days.  These would be the immediate target systems.  I have yet to flesh out the details of this server HTML proxy, but I'm going to be investigating this this year.  If you are interested in enabling people to use the Internet more effectively please e-mail me.  I think this is a pretty good idea, but I'm willing to listen to better ones!