Thursday, January 27, 2011

Behavior Driven Development

No this isn’t a new program from HR, it is a theoretically nice way to ensure that your requirements, documentation, test cases, and code are all in sync. The difficulty with anything like this is that you have to actually use it.

Below is a link to a 30 minute video on how this can be done using a free tool that integrates with Visual Studio and NUnit.

http://specflow.org/specflow/screencast.aspx

Check it out. Remember, you can give it a try on a new module or feature for an existing application or even apply it an existing project just to test it and also document it.

Tuesday, January 25, 2011

Why is my MS SQL Database so slow now?

If your database worked great when you developed and is now slow, here are some things to consider. If you didn’t create indexes initially, you data may have grown since you started, and need some indexes. If your data changes a lot, the database can become fragmented. Fragmentation can come at the disk level and can be remedied by something like Windows disk defragger tool. Fragmentation can also come at the database level which could mean even certain tables or partitions are fragmented.

Checking for Index Fragmentation

Use DBCC SHOWCONTIG to determine how fragmented your tables are. You can use different parameters, if you just execute with no parameters it will be run for all tables in the database. I recommend using the following parameters so that it can be shown in table for all tables in the database.

DBCC SHOWCONTIG WITH TABLERESULTS;

NOTE: You will see an column called IndexName, but know that this is just the name of the primary key index, not all the indexes on the table.

In particular, the first thing to look at is the Local Scan Fragmentation. This is the percent that is fragmented. For a very healthy table 0% is what you want. Secondly, look at the Scan Density line. For a really healthy table you want 100% which is the ratio or Best Count to Actual Count.

You should eventually move to sys.dm_db_index_physical_stats instead since DBCC SHOWCONTIG will be removed after SQL Server 2008. I don’t know how to get the Scan Density though. So, I show both here. However, some one at Microsoft posted that you don’t need the Scan Density and should instead use the contiguous extent allocation which is provided by sys.dm_db_index_physical_stats, but I don’t know which column they are referring to. Does anyone else know? I suspect it has something to do with fragment size.

Evaluating Index Fragments

A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size. For more info click here.

If you want to go off of just the average logical fragmentation percent and you have SQL 2005 or greater you can use the following statement which is very fast if you just want to see the fragmentation of the index on the table MyTable in this example

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Alternatively, if you want to see it for all the tables, you can do this one.

SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), null,
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Possible Solutions

If you have index fragmentation you have three options noted in docs on DBCC SHOWCONTIG

  • Drop and re-create a clustered index.

    Re-creating a clustered index reorganizes the data, and causes full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks of this method are that the index is offline during the drop or re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created.

    Example:
    CREATE CLUSTERED INDEX IX_WorkOrder_ID ON WorkOrder(ID)
        WITH (FILLFACTOR = 80,
            PAD_INDEX = ON,
            DROP_EXISTING = ON)
    ;
    or
    DROP INDEX followed by CREATE INDEX
  • Reogranize / Defrag

    Reorder the leaf-level pages of the index in a logical order.

    Use ALTER INDEX…REORGANIZE to reorder the leaf-level pages of the index in a logical order. Because this operation is an online operation, the index is available when the statement is running. The operation is also interruptible without loss of completed work. The drawback of this method is that the method does not do as good a job of reorganizing the data as a clustered index drop or re-create operation.

    Note: You can also use DBCC INDEXDEFRAG since it is old SQL Server 2000 equivalent.

    Example: ALTER INDEX PK_Employee_ID ON Employee REORGANIZE;
  • Rebuild the index.

    Use ALTER INDEX with REBUILD to rebuild the index. For more information, see ALTER INDEX (Transact-SQL).

    Note: You can also use DBREINDEX since it is the old SQL Server 2000 equivalent.

    Example: ALTER INDEX PK_Employee_ID ON.Employee REBUILD;

Choosing the right solution

The rule of thumb that Microsoft recommends is the following:

% Logical Fragmentation Corrective Statement
< 5% No action should be taken
> 5% and <= 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Use the ONLINE = ON to keep the index usable and data available to users. ONLINE = OFF can also be used if availability is not a concern

Automation

Example E in the docs has a great script for defragging any index that has more than a specified threshold of fragmentation.

The easy way is to Rebuild indexes in the Maintenance Plan for the database. Though this requires the indexes be taken offline, so if you run it weekly and load data daily, it may not be often enough. Then you may want to use the script below.

This script actually follows the recommendations above, except puts changes the 5% boundary to 10%. The problem with the script is that it does NOT support anything except the default schema, uses 10% instead 5%, and includes indexes that don’t have names such as when there is no primary key on the table. If you want to use the same script I use that I have modified to support these issues, click the link below.

Friday, January 21, 2011

FireFox supports Integrated Windows Authentication

Most people (including me until recently) don’t know that FireFox support Integrated Windows Authentication (NTLM). The behavior that most people are accustomed to is when you go to a web site on a corporate intranet that often requires Windows Authentication using Firefox you get a prompt for your username and password and for Internet Explorer you are logged in automatically. So, the assumption that many people (me included) make is that Firefox doesn’t support Integrated Windows Authentication. Fortunately, this is a wrong assumption.

Internet explorer determines what sites are ok to use Integrated Windows Authentication on by looking to see if the site is in the Intranet Zone which can be done by looking the url, etc. Well Firefox doesn’t use that criteria. Firefox instead uses a white list or in others you have to explicitly tell it which sites to trust and that will then use Integrated Windows Authentication. Since the list is empty by default every site that has Integrated Windows Authentication enabled still gets a prompt. The simple solution is to add the sites that are on your intranet that use Integrated Windows Authentication to this list.

The Easy Way

The good news is that someone created a nice and easy to use Add-on for Firefox. So you can open up Firefox and go to Tools menu | Add-ons | Get Add-ons tab and type in the search box: NTLMAuth. You will likely get one result and it is for an add-on called NTLMAuth For Firefox. Click the Add to Firfox… button, install, and restart Firefox.

Open up Firefox again and go to Tools menu | NTLM-Enabled Sites

You will get a screen that looks like this:

image

All you have to do is add the sites you want to have Integrated Windows Authentication enabled for. No more prompt will be shown for these sites. Please note that you do NOT want to use http:// or anything after the domain name except a colon then port number if it is not port 80.

For example, you could put www.apple.com or myapp.mycompany.com:1234.

For Geeks

The truth be told you don’t need the add-on at all. You can do this with just Firefox itself. The add-on just saves you from changing some configuration items in Firefox.

If you want to do it yourself, here is what you need to do.

  1. Open FireFox
  2. Type about:config in the address bar. (Tip: Don’t let Google try to search on this item if that feature is enabled by hitting escape after you type it). Hit enter of course to go to it.
  3. If you are using FireFox 3.x or later you will be warned. Agree if you want to continue. If you have already told it to not bother you in the past you won’t get this prompt.
  4. You can look through the list or simply type network.automatic in the Filter at the top of the the screen. Look for a line that is called network.automatic-ntlm-auth.trusted-uris. Double-click that like to bring up the tiny little editor. It’ll look like this.

    image 
  5. You can now type in your sites separated by commas. It worked for me without spaces, but I read that you can use spaces also.

    TIP: I recommend just typing them all into notepad or some text editor, then copy and paste the big line into this little textfield.
  6. Click OK and that is it.

Thursday, January 20, 2011

Figuring out what your proxy is

If you work in corporate America chances are you are going through a proxy when you are surfing the web. Sometimes the proxy server is specified, sometimes you have a url that then determines what proxy to go to. If the server is directly specified as shown in the Proxy Server address below (the lower yellow area), then it is easy to tell. What is there is your proxy server.

On the other hand, if you have a .pac file or out configuration script that is being used then it is hard to tell from here because the rules are in the .pac file. This would be specified in the first yellow area shown below. You can use FireFox or sometimes Internet Explorer to save the .pac if you go to it in the browser. It can be saved and opened in any text editor. You can’t change it, but you can at least see what it is doing. But the question I had was how do I confirm what proxy server the .pac file has directed me to?

image

No matter what these settings are you can tell for sure what you proxy server you are actually connecting to by using the command line. First make sure you have Internet Explorer open and on the page in question. Open a command prompt (Start menu | Run… | cmd.exe) and then type the following:

netstat –b –n –p tcp

Now look for iexplore.exe. You will see something like

image

Notice I have highlighted the proxy server and port in yellow.

If you want to know what that is from a dns name perspective, you can use nslookup to look it up.

In this example, you would type:

nslookup 134.27.74.36

This will return some name of the server if you are lucky.

Tuesday, January 18, 2011

How to pass a list of ids to a stored procedure and iterate over them using a cursor

As you probably know there is no array type in T-SQL. Let’s say the end user selects some records and you want to pass the ids of each of these records to a stored procedure. You can just use Dynamic SQL to handle this, but what if you actually want to use a cursor to iterate over each one and do something for each item. There are many reasons such as calling a stored proc for each item or maybe sending mail, etc. In general I don’t like cursors because they are slow, but I think there are cases. I had one of those cases, and the number of records I needed to loop through was small so I have no problem doing so.

The idea is to just pass the list of ids as comma separated values as a string as a parameter to a stored procedure. Be careful how this list is generated. You never want to leave this data unchecked for SQL Injection attacks. In particular, I recommend building an array of integers (not strings) in .NET and then converting the array to a comma separated list of ids. I got the idea from here. I also has a wealth of other techniques for simulating arrays in T-SQL.

The problem you will soon run into is that you can’t use standard Cursor syntax when you use Dynamic SQL. The code below shows you how you can do so using a Cursor Variable. You can also do it using a Global Cursor. If you want to go the Global Cursor route, check out the code here.

The code below basically is called something like this:

IterateThroughItems ‘1234,456,789’

This would select three records with ids 1234, 456, 789 and then print out the ID. Instead of printing out the ID, you could print the FName, LName, sendmail, update other tables, whatever you like.

-- WARNING: The end user should NEVER be able to enter or submit any information that is then passed into
-- the @IDs parameter. If they do, SQL-injection is available for them to hijack the database.
-- Only pass data that has been type-checked, etc. For example, I recommend using an array or list of
-- integers in .NET and then using string.join() to populate the value of @IDs.
create proc IterateThroughItems @IDs as varchar(2000)
as

Declare @SelectStmt as nvarchar(3000)
Declare @Sql as nvarchar(3200)

Declare @ID as int
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)

-- this is a cursor variable and is needed so we can use dynamic-sql which we need to deal with the list of ids
Declare @PersonCursor CURSOR

Set @SelectStmt = 'select ID, FName, LName from Person where ID in (' + @IDs + ') order by ID'

Set @Sql = 'Set @PersonCursor = CURSOR FAST_FORWARD FOR ' + @SelectStmt + '; OPEN @PersonCursor'

exec sp_executesql @Sql, N'@PersonCursor CURSOR OUTPUT', @PersonCursor OUTPUT

FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName

WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing: ' + Cast(@ID as varchar(20))

FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName
END

CLOSE @PersonCursor
DEALLOCATE @PersonCursor

Using LINQ in a foreach loop to build a query

I love using LINQ because it makes building up queries so easy. The exception to this is when using a foreach with a union. I suspect it is just the foreach that is causing this behavior, but for sure the behavior is consistent when using both.

Let’s pretend that you have a list of people that you let the user select one or more people from. You want to pull back the person records of just the records they selected. Maybe you want to do a database round-trip to get the latest data before an edit or may you just want to filter the list in memory. In either case, this post addresses both of them since LINQ is used in both cases. The only difference may be is that instead of a List<People> you may be using an IQueryable<Person> if you are using the database. The tricky step is identical. I know because I was originally seeing the issue on the database example I had. I wrote this using just standard LINQ (to Objects) and in memory objects to make the running of this easy for you.

With that said, I have created a Person class below that is meant to simulate the Person table in the database. I then populate it with three records (Person objects). I then specify that I want just the people with the ids 1 or 2. I then run the query, dump the results. This shows the issue. We only get one record back. Then I run the next query and dump the results. This time the results are correct and give us two records as expected.

What is the difference in implementation you ask? There is very little different. In both cases there is a foreach loop and use the union method to build up a query. Union is similar to using an OR in the where clause of a SQL statement, but often the performance is much better. The downside is that much of the query is duplicated and makes maintenance a pain. No so with LINQ. It is very easy to do so here.

The ONLY difference between what works and what doesn’t work is that I declare a LOCAL variable INSIDE the foreach loop. I then use the LOCAL variable as a parameter in the LINQ expression. I have to assume this causes it to get the value from the variable before the LINQ expression is evaluated and thus it has the value we want. Otherwise, I assume it just looks at the current (last value) of the variable declared in the foreach statement itself at the end after the foreach loop has finished executing. I don’t know exactly how it works, but that is my assumption based on what I see from the behavior of the two methods. I don’t know if I would call this a bug or not, but it is not intuitive to me and makes for it to be very easy to write buggy code if not properly tested.

public class Person
{
public string Name {get; set;}
public int ID {get; set;}
}

List<Person> people = new List<Person>();

void Main()
{
// simulate a database and a table called people that has 3 records
people.Add(new Person{ Name="Brent", ID=1});
people.Add(new Person{ Name="Lance", ID=2});
people.Add(new Person{ Name="Kim", ID=3});

// this could ids of the records the user selected
List<int> idsToFind = new List<int>();
idsToFind.Add(1);
idsToFind.Add(2);

// execute the query
var allPeople = GetPeopleByIDsBroken(idsToFind);

// output the results
allPeople.Dump();

// The results will be ONE record and that is Lance
// This might be surprising because we passed in the ids for Lance and Brent.

// execute the query
var allPeople2 = GetPeopleByIDsWorks(idsToFind);

// output the results
allPeople2.Dump();

// The results will be TWO record and that is Brent and Lance
// This is the expected result and what we wanted.
}




IEnumerable<Person> GetPeopleByIDsBroken(List<int> ids)
{
IEnumerable<Person> builtUpQuery = null;

foreach (int id in ids)
{
// first time through only.
if (builtUpQuery == null)
{
builtUpQuery = people.Where(p => p.ID == id);
}
// all subsequent times
else
{
builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == id));
}
}

return builtUpQuery;
}

IEnumerable<Person> GetPeopleByIDsWorks(List<int> ids)
{
IEnumerable<Person> builtUpQuery = null;

foreach (int id in ids)
{
// We have to get a local copy of the id.
// Othewise, the value is not taken from the id in the foreach loop
// until the entire expression is evaluated
int localCopyOfID = id;

// first time through only.
if (builtUpQuery == null)
{
builtUpQuery = people.Where(p => p.ID == localCopyOfID);
}
// all subsequent times
else
{
builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == localCopyOfID));
}
}

return builtUpQuery;
}


Sunday, January 16, 2011

BrentBot is born today

Here is my robot - BrentBot
I am very excited to have created a robot from scratch. It took a lot of research to figure out how I wanted to build the robot. In the end, I followed an article in Servo magazine that walked me through it. Like most things in life, it didn't go quite as I planned. I didn't have all the parts that the article required, so a lot of them I found at Ace Hardware and later found out that Fry's Electronics had cheaper screws when I bot some other stuff there. If you have a Fry's electronics where you live, you can start there. Some Radio Shacks are supposed to have some of this stuff, but most requires it be ordered online and shipped to your home or Radio Shack for pickup. There are lots of Robot companies online. I personally like RobotShop since it has such a wide variety of things and they at least have a branch in the US even though they are a Canadian company.
The cost was about $100, but I didn't end up using everything, so like the article said, it is probably pretty close to $85. If you want professional results or just want to save a ton of time fabricating the deck, connecting wheels, making servo mounts, etc I highly suggest buying them. The cost was actually only around $15, but I wanted to see what I could come up with.
I made my deck out of nylon cutting board. It is very strong and durable and doesn't split at all (from what I can tell). The downside is that it is not as easy to work with as I had hoped. For instance, I used my Jigsaw/Scrollsaw, etc to cut it and found that even on the lowest speed it just melted the nylon and made a mess and was hard to cut. By the end, I figured out that if I just tap the trigger and then push the saw through until the blade stopped this was slow enough and kept everything cool enough that it was fairly accurrate to cut with and was managable. If I had to do it again, I probably would not use this material again, but it was free since it was an old cutting board. I did sand it down to make it smooth since there were so many knife marks on it.
The cutting board was a bit thicker than the original design called for and the wheels that I chose were smaller that suggested I ended up changing the original design from a two decker to a one deck robot for now. This actually worked to my favor. Now, I was able to mount the servos and the batteries on the underside of the deck and still had all the room on the top side of the deck. So really I have the same amount of deck space, but with half the height and much less weight. I like my design actually.
I chose the Arduino Uno as the brain for controlling everything. Mostly because it is open source and open hardware and it is WAY cheaper than the Parallax Stamp Kits. Personally, I don't like VB or anything that looks like VB and that is what the Stamp uses. Unless you want to pay even more the Javaline I think it is called. That is just way too expensive for me just to not write in VB like language when I have a much cheaper (and significantly more power) processor (Arduno Uno). My goal was to build a robot under $100 and I succeeded. Granted it doesn't do much because it doesn't have any sensors. I'll add those later at additional cost obviously, but they will come in time as they are needed.
As word about Parallax and the BeoBot. If you don't want to work really hard and do this all from scratch and you want support and supported enhancements, I think the BeoBot would be great. I also think the Lego Mindstorm is a bit more pricey, but you can do so much in a short amount of time with it. If I didn't have such a strong desire to build this robot from scratch just to say that I did it all, you may want to seriously consider one of those other options. The lego solution is great because there are parts readily available and you can create so many different kinds of robots in little time.
Don't underestimate the time it takes to fabricate everything. Designing a robot from scatch takes a lot of time. You have to think about how wires will be run, line up holes on parts, match nuts and bolts, solder, and in general just spend time though trial and error. Don't forget the error part is great for learning, but takes time.
I soldered the battery connectors and barrel connector for powering the Arduino. This was time consuming. I did remember how to solder thankfully. I also figured out to de-solder because I had the polarity of the barrel connector backwards. I don't know how I did that. I did figure out that the barrel connector needs the positive on the inside part and negative on the outside part. Even with that knowledge, I got confused. Oops. Oh well, in the end it gave me a chance to add more length to the plug since the first one was a bit short. Good luck de-soldering the wires from the barrel connector if you wrap them around the posts before you solder them. They will never come off without a LOT of work, though I did finally get the wires off and new ones put on.
When cutting the servo brackets they need to fit snuggly around the servo. This is because the mounting brackets on the servos are right there close to the sides of the servos. If you leave too much space around the servo the screws that go in the mounting brackets won't have anything to screw into. This kind of defeats the purpose of the servo brackets.
I did and you probably will also need to have two sets of power supplies. One for the Arduino and one for the servos. The reason is that the Arduino can only handle so many things plugged into it. This is especially true when it comes to the Servoes. Besides, it is good design to have separate drive system power. That way there isn't noise (electrical noise) that can make your Arduino and you cicuits act funny. Don't worry, it is actually very easy. All you do is connect the grounds together and they work great.
Once BrentBot started to work, I quickly found out I will need at least one swithc for the power. I don't have one right now and everytime I want to test anything, I have to disconnect the main barrel connector, and plug in the usb cable. Not to mention, I usually have to disconnect the batteries for the drive system. While not a pain, these plugs will eventually wear out and they are not really cheap to replace. A switch is a much better solution and allows for faster test cycles.
Servos are great for a simple light weight robot that is under say 3 pounds. Over that, you'll have to get the large servos or go to a motor and gearbox. In either case, always get wheels that mount directly on the servo's shaft. If you don't, you will be spending lots of time trying to figure out how to mount a wheel onto it like I did. In the end, I think it is quite straight.
When you are writing your code to control the servos there is a library to do so. This doesn't mean you have nothing to do. You still have to write your basic functions like forward, reverse, turn left, turn right. If you don't attach and the detach for each of these functions your servos may jitter when they are idle. To prevent the jitters all just make sure you detach after each function call. This has the added benefit of saving your battery life also.
The power for the Arduino is a single 9V battery. The drive system is powered by 4 rechargeable AA batteries. I highly recommend rechareable batteries. Otherwise, you will be spending a lot on batteries. They don't really last long.
By all means, unless you have servos already, be sure to by the continuous rotation versions. They are the same price and will save you from trying to figure out how to take one apart and modifying it. If you need to modify it, there are article on the internet that tell you how to do it. I didn't want to bother with it. It seems silly these days when continuous ones are readily available at the same cost.
I found that having old computer plugs, wires, etc came in handy. If you have the long header pins they fit the computer plugs perfectly because a computer is a circuit after all. I also found the wire useful for the power lines since they are pretty thin. Yes, I know you can buy wire, but it usually isn't two wires side by side which creates less mess, and in my case I had them already so they were free to me. If you don't have them, consider an old computer from a garage sale that would have stuff you can use. Goodwill is another option that can be fairly inexpensive. On the other hand, unless you find just a screaming deal, you are probably better off just buying wire and taping them together, or using zip-ties.
It is important that you get your polarities correct. This is particular important with your servo. if you get this wrong, you can permanently damage your servo. I think the Arduino can cope even if the polarity is reversed, but I don't really recommend it. The easy way to do this is with a battery tester or better yet a multi-meter. To see which wire is the positive one, you touch the wire to the red lead of the meter, and the other wire to the black lead of the meter. If you get a positive voltage (or if the battery tester says good) then the wire on the red lead is your positive wire. If you get a negative voltage or no ready on the battery tester then the wire on the red lead is negative. In general it is a good idea to start at a larger range on your multi-meter. Says something like 20, but starting at 2 should be good also since we are talking about small voltages here and it is know to be say 9Volts for a 9Volt battery.
I hope this is useful to someone that is starting with robotics.