There are way too many layers of separation in this application. A single change takes hours because the change has to be performed in so many places. The application was built using a large number of layers so that they could be replaced with something else at a later time. The problem is that we never have time to replace […]

Language features like those in R, which is designed to work with vectors, have often be performed using traditional SQL syntax. While SQL Syntax is not designed for statistical analysis the set nature of the language does lend it to solving some statistical problems. Perhaps this might even be a more efficient solution if large amounts of data must be […]

This last year I focused on perfecting Web API skills in the Dot Net framework. I chose Web API because of its ability to create a RESTful service interface accessible from many different consumer platforms, languages, and operating systems. Microsoft uses the Model and Controller portions of their MVC implementation to easily implement a RESTful service, which may be self-hosted […]

Today I have been looking at the extension in Entity Framework (EF) 6 allowing the auditing of the generated SQL and it’s execution. http://msdn.microsoft.com/en-us/data/dn469464.aspx This is built into the EF library allowing the developer to intercept, log, modify or even cancel the generated SQL code. This can be used to solve any number of problems. If you need to trace […]

When it comes to programming, I no longer have any idea how quickly or accurately I can type. I also have a reduced memory of the objects I use on a daily basis, either written my myself, or contained in libraries I frequent. Why am I able to get away with this reduced set of skills? We have all come […]

Does it matter what order you join tables in an SQL Server query? Not unless you use the command SET FORCEPLAN ON. If you use FORCEPLAN SQL Server will join the tables in the order you specify regardless of Query Optimizer calculations. If you do not specify that you wish to override the query optimizer, by setting FORCEPLAN on, which […]

Indexes are important for any database. For canned packages the application designer makes the best indexes they can. The problem is that indexes on any system go through a lot of change as data is added, updated or deleted. Data changes cause indexes to become fragmented, and statistics about the indexes to become inaccurate. As your data is modified it […]

Even canned software solutions using a SQL Server database may benefit from tuning. SQL Server does a pretty good job of hiding these details from you with “out of the box” configurations performing database maintenance operations, sometimes without users even knowing they exist. Automatically creating or updating statistics helps keep your database tuned as your data grows. When a canned […]

There are four data types that will be deprecated in a future release of SQL Server as announced by Microsoft. They are the Timestamp, Text, NText and Image. The great news is that they have all been replaced with equivalent or more powerful data types so that the needed functionality has not been eliminated completely. http://msdn.microsoft.com/en-us/library/ms143729.aspx The downside is that […]

I was talking with a colleague recently who uses a canned application to assist in his daily work. The application uses SQL Server, granting him a them a lot of opportunity to extend their understanding and mining of the data. The problem they are experiencing is that the database is slow, and continues to get slower as it accumulates more […]