Get Rid of Half Your Stuff

October 1, 2010 Leave a comment

I started selling off my belongings in order to pay for a recent vacation to Alaska.  At first I was kind of worried about getting rid of my stuff.  All my old video games for 50 bucks.  Thousands of hours were invested there.  Old TV’s, bags of clothes, my dining room table, mirrors, furniture, bike racks, beds… all gone.  To be honest I’ve never felt better.  I know the only things I have are the things I really value.  It feels great.  So if you want to feel great sell half your stuff.  Seriously.  And take a vacation with the money you earned doing it!

Categories: Random Thoughts

SQL Full Text Query Across Multiple Tables

August 4, 2010 1 comment

Part of my job is updating and maintaining some legacy web applications. Most of the applications were written back when our network consisted of 6 laptops, and we never imagined we would grow so big. For this reason most of the applications were not designed with scalability in mind. This is an example of just such a oversight, where an SQL query seemingly worked fine for 200 records, but now that we have 10,000 we are seeing some serious issues.

Lately, people have been complaining that one of our web applications was timing out during searches. Since we are disconnected from the internet, we thought that setting the full text service property that forces signature verification to off (according to http://support.microsoft.com/kb/915850) might help. When this produced no improvement, we rebuilt the full text catalogs, which produced slightly better results, but we were still timing out for a fair number of the queries.

Upon further investigation I found that the query itself was running fine, it was just taking over 30 seconds, which is the default timeout for the SqlCommand.CommandTimeout property. In other words SQL was still running the query, but since it took longer than 30 seconds it automatically throws the error that the server has timed out or is unavailable, but really it’s the webpage itself that has caused the timeout.

An SQL query that takes over 30 seconds is indicative of inefficiency, especially considering the hardware we are running. Digging into the code, I found that a query was being constructed that searched the full text catalogs by using the sql CONTAINS clause sewn together by OR statements. So for each column in the fulltext catalog there was a separate CONTAINS statement, adding up to a grand total of 5. I narrowed this down to 3 CONTAINS statements by grouping columns from the same table into individual CONTAINS. It was still extremely slow. Then I broke out the query for each table, thinking I might give the user the ability to expand the search manually. When I did this I noticed that each query ran almost instantly (~0.01 sec).

So the solution was to run the query on each table individually, then use a UNION to combine the results! On the production database this took the query from 66.956 sec down to 0.116 sec. What a difference!

If you ever happen upon a forum where you get the advice to use an WHERE CONTAINS() OR CONTAINS() statement to query for the fulltext catalogs across multiple tables, make sure you set them straight!

Soybean Based Urinals

June 23, 2010 4 comments

The company I work for, like so many others, is going green. The company is pushing bike to work initiatives, electric hand dryers instead of paper towels, and the latest waterless urinals. While I have no doubt the electric hand dryers are saving both the environment and corporate funds (employees often wipe their hands on their pants before being held a minute or more to use the electric hand dryers), the urinals got me thinking.

The waterless part is great right? Saving lots of water? Sure, but the urinals smell terrible. It would seem they are also nearly impossible to hit, as every waterless urinal seems to have urine all over the floor in front of it. Those things are downers, but don’t really matter to the company right? Well I’m not so sure. Whatever they spent on the trial green bathroom facility is certainly wasted; traffic to the bathroom seems almost negligible. But that wasn’t even what got me thinking.

The urinal claimed to be made out of 30% of a soy based resin. Well at first that made me happy. All of my friends know I hate soy based foods, and the idea that I was urinating on something partially soy based just made my day. However, I started to think back to the impact that corn subsidies which promoted creating ethanol based fuels had on worldwide food prices, and wondered what impact soy based urinals might have if they were to suddenly become increasingly popular. I don’t know if you remember the report, but the finding was summarized as follows:

“When all the costs and benefits are tallied, the government, taxpayers, and consumers together would lose $6.1-$7.2 billion or $1.61-$1.92 per additional gallon produced during the 1986-94 period if ethanol subsidies were increased enough to prompt the ethanol industry to produce 2 billion gallons in 1995. Conversely, if ethanol production falls to zero, they would save some $6.8-$8.9 billion, or $1.35-$1.76 per gallon not produced.”

Make no mistake, they aren’t making soy into just urinals, they are also making biodiesel from it. An increase in demand or government subsidies of the soy biodiesel production could create a similar cost to tax payers, or a worldwide swing in the price of soy. When the price of soy goes up, all kinds of crazy things happen. It would seem that the cost of soy is directly correlated with deforestation in the Amazon, as people scramble to clear forest to plant the bean. Though the markets are complicated, we know that as the price of food goes up, people starve. We saw this very clearly in 2008.

My conclusion: enjoy the smug satisfaction of your flushless urination and as they raze the Amazon rainforests and children in third world countries drop dead of starvation. Go Green!

Categories: Random Thoughts Tags: ,

Google-like Suggest Textbox

June 18, 2010 Leave a comment

We all love the Google search real-time suggestions. 

It is possible to implement a similar feature in your own web development using the asp.net ajax toolkit. 

Inside the toolkit you will find the AutoCompleteExtender.  Below I will detail how to get this difficult extender working.  

Don’t bother looking at the documentation on www.asp.net, it is more or less worthless.

First, associate the autocompleteextender with your text box.

As you can see, you must specify a ServiceMethod and a ServicePath.  This is where it gets tricky.  

You must create a web service (I used an asmx file) which strictly adheres to calls from the autocompleteextender. 

The service will look like the following:

This should get the service up and running.  Notice the query itself must strictly adhere to the number of records you want it to return.  You can also play a few formatting tricks via CSS.  Mine looks as follows:

The final product looks as follows:

Some tinkering needs to take place to make the textbox size equal to the div size of the autocomplete box. This can be done in the css for the text box, making sure there are not extraneous borders or margins.

Using jQuery to call a Web Service in XSLT

June 18, 2010 1 comment

Transforming XML into HTML or the like using XSL Transforms is a powerful tool.  The biggest problem I faced when I began transforming certain XML tags into HTML controls is that the controls don’t do ANYTHING!  Fortunately I figured out a way to use jQuery inside of the XSL transform to call an .asmx web service.  I started by taking some XML, maybe something like:

<highlight>yellow</highlight>

In the XSLT when I find the <highlight> tag, you can do something like this:

This gives you a drop down style box, with the appropriate option selected, of course there will be a few more xsl:when options, and a an otherwise option that acts as a try:finally.  Now that you have a functionless drop down, its time to add the code behind that you want the drop down to execute on, say a change of selection.  So make yourself a Web Service (.asmx)  maybe something like the following:

Now for the trick: calling this web service using jQuery.  Of course, this jQuery must be in your XSLT, making it even more tricky to trouble shoot.  I used the jQuery.ajax() function.  It will looks something like this:

Now you have a fully function Control generated by XSLT. Enjoy!

Categories: C# Tags: , , , ,

Dynamic Help Removed in VS 2010

June 18, 2010 1 comment

If you are like me you love and cherish the dynamic help feature in Visual Studio. In fact, I never actually learned how to write code because this feature is so useful. I would just click on a class I had no idea how to use and up pops links to the msdn documentation, which probably holds the code I need to write already.

This feature has been removed from VS 2010. Though you are still free to locally install the msdn documentation, there is no dynamic feature. Today is a sad day.

Categories: C# Tags: , ,

Installation order for Visual Studio and SQL Server

June 18, 2010 1 comment

If you plan on installing the full version of SQL server (instead of the express version) with Visual Studio, I prefer to install Visual Studio first, preferably without the express instance of SQL server.

If you’re doing a fresh install of VS 2008 and SQL 2008 on Windows 7, install in this order:

  1. VS 2008, WITHOUT SQL Server Express -> requires doing a ‘custom’ install
  2. VS 2008 SP1
  3. MSDN Library for VS 2008 SP1 (if you’re so inclined)
  4. SQL Server 2008
  5. SQL Server 2008 SP1
Categories: C# Tags: ,
Follow

Get every new post delivered to your Inbox.