Open-Source Software: Myths and Misconceptions

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

“Open-source” software has become extremely popular and widely accepted for business use. As such, there is a vast selection of software released as open-source. Open-source options exist for nearly every software category from operating systems to computer games. The list of open-source software is seemingly endless. As might be expected, the popularity of open-source software has precipitated a large number of misconceptions and myths. This article attempts to clarify what “open-source” really means and dispel some of the associated myths and misconceptions.

Continue reading

Capturing Inserted Row Keys with OUTPUT Clause

Copyright (C) 2010 Richard L. Buckmaster. All Rights Reserved.

A powerful yet often overlooked feature of SQLServer is the OUTPUT clause on INSERT, UPDATE, and DELETE statements. The output clause allows capturing values from the INSERTED and DELETED pseudo-tables. This can be used to log values before and after an update, capture assigned identity values from an insert, and even capture entire deleted rows. Capturing assigned identity (or GUID) primary key values from an insert allows performing set-based parent/child insert operations where you would normally need to use a cursor or looping structure. This article discusses the output clause and provides several examples where it can be used to simplify common database operations.

Continue reading

.NET SynchronizedQueue Class

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

More often than not, there is a need for threads in a multi-threaded application to inter-communicate. This is especially true of UI applications that utilize service or worker threads to perform lengthy operation in the background. Windows .NET applications are no different. While several inter-process communications (IPC) options exist in the .NET framework, most of these are too “heavy” for inter-thread communications. A handy solution is the .NET Queue class. However, the Queue class needs a little help to be a truly useful tool through which threads communicate. I developed the SynchronizedQueue class specifically for inter-thread communications.

Continue reading

Converting T-SQL to MySQL: A Survival Guide

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

This article is written for those who are experienced Microsoft SQLServer programmers trying to make the transition to writing MySQL stored procedures and user defined functions.

MySQL is a very popular and widely used RDBMS because it is fairly full featured and the Community Edition is free. If it is the only RDBMS you have ever used then this article won’t be of much help to you. However, if you are trying to make the transition from a SQLServer world to MySQL you will likely encounter some frustration.

The differences between T-SQL and MySQL stored program languages are mostly syntactic and can be easily learned. The frustration comes from a few MySQL functional limitations, potentially dangerous peculiarities, and language nuances. The language differences can be learned. The functional limitations will likely drive you crazy until you’ve created your own work around solutions. Finally, if you are not aware of several major peculiarities of MySQL, you may encounter data corruption or loss.

Continue reading

Multi-threaded Application Basics

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

Multi-threading can improve an application’s performance and user productivity. Designing and implementing a well behaved multi-threaded application requires a thought process that may be foreign to a developer with no experience in multi-threaded programming. However, mastering just three basic concepts is all that is needed to get started. This article discusses the basics of multi-threaded applications, provides a few design guidelines, and includes a simple multi-threaded example application.

Continue reading

Five Revelations of Outer Joins

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

Do your outer join queries return a different number of rows than expected? Do you resort to using DISTINCT directives on your outer join queries to elimiante duplicate records? Or do you break them into multiple statements because you just can’t get those outer joins under control? May be you just avoid using outer joins all together? If any of these are true, you need to take the time to understand the affect of outer joins on query results. Once you understand exactly how they behave you can take full advantage of outer joins in your queries.

Continue reading

Shared-Nothing Architecture

Copyright (C) 2011 Richard L. Buckmaster. All Rights Reserved.

Shared-nothing (SN) architecture is currently all the rage. The Internet is buzzing with discussions about “true shared-nothing” solutions. But contrary to what its name implies, shared-nothing architecture does not mean “nothing is shared”. In fact, an SN implementation is very much the opposite. The basic concept is that SN architecture eliminates single points of failure and improves performance through easy scalability. Unfortunately, that description is so non-specific that it is subject to wide interpretation. As a result there is considerable (and often heated) debate about what SN really means. This article attempts to de-fog the concept of shared-nothing architecture and discusses SN technology in a real-world application. As a bonus this article discusses two well known (and very mature) open-source applications that implement large shared-nothing architectures. You may discover that shared-nothing is not all that new.

 

Continue reading

SQLGauge: Embedded Performance and Error Logging

Copyright (C) 2010 Richard L. Buckmaster. All Rights Reserved.

There are two intermittent problems database application developers encounter; SQL execution errors and poor database performance. These problems are especially difficult to troubleshoot when they occur only in a production environment where trace tools and debugging code are explicitly prohibited. Having encountered this situation many times I built SQLGauge, a tool that allows embedding performance and error logging ‘instrumentation logic’ as a standard part of my SQL code patterns.

Continue reading

SQLDNS: SQL callable DNS tool kit.

Copyright (C) 2010 Richard L. Buckmaster. All Rights Reserved.

It is common for web sites to capture visitor traffic information including the visitor’s IP address. IP addresses are most valuable when translated through DNS look up to a host name. Traditionally this is done by an application external to the database. It is much easier to analyze captured traffic information if you can do the DNS look up within SQL. I developed SqlDns to access DNS look up services directly from T-SQL.

Continue reading

NOLOCK Reduces Contention and Improves Performance

Copyright (C) 2010 Richard L. Buckmaster. All Rights Reserved.

The NOLOCK locking hint has the same effect as the READ UNCOMMITTED transaction isolation level. That is, they both allow accessing data rows that have not been committed (A.K.A. dirty reads). The major difference is that the isolation level directive applies to all referenced tables within the scope of the directive while the locking hint applies only to the table referenced by the join on which the hint appears.

Continue reading