It can very frustrating trying to help someone who is asking for help, but not giving you what you need to help them.  I wanted to put together this post as a sort of FAQ on how can we help you with things like queries and schema problems.  If we can’t get certain information we often can’t offer more than general guidelines – and those may or may not apply to your problem.

Query Performance

In general we do not provide support on query performance.  There are just too many ways to write the same query, some are fast, some are slow.  There is often no way to know short of trying them.  In most cases either query is good enough and people just move on.  But what if your query needs a certain response time? 

If you are simply taking a query from Access builder and it is too slow, that is not good enough. Did you try ANYTHING else?  Check your indexes?  Anything?

Engine one off code and customization

Microsoft SQL engines (Access, SQL Server, and SQL CE to a lesser extent) will rewrite poorly written queries for you.  They sometimes build indexes, temp indexes, lookup tables, cross reference lookup temp systems, all sorts of things under the hood that you may not know about. 

So why don’t we do all that?  Microsoft spent $1 BILLION on the company that provided that technology.  Yes, that is with a B – wow.  And that is part of the reason SQL Server is so large. 

It has a lot of code paths that are special cased code for one off scenarios.  We don’t do that because we want to stay small and lightweight, and because each of those cases you add also have to be documented, tested, and maintained.  Each of those add a lot to our workload.  If you have 10 special cases to 10 functions you now have 100 combinations to test.  Start chaining that out through all the clauses and you end up with huge complexity for test purposes.

Special Cases?

Does that mean we will never special case code?  No. We will do it, but only with really good evidence that it is going to impact a lot of use cases.  How do we build those use cases?  Use cases is where you can really help us.  We have a lot of usage scenarios based upon standard ADO.NET patterns and practices.  Then we have quite a few from our own applications we built that broke those patterns.  Add to that problem reports from users, and you start to get a pretty good comprehensive coverage of SQL patterns.

Continue Reading...

We have been hit quite a bit lately with tickets and requests from users around third party tools.  Yes, third party tools can simplify your development efforts in some cases.  But are you really making your support requirements more complex?

Debugging third party tools

We simply do not have the resources to help users debug issues with third party tools.  In many cases the questions are usually very specific around this vendors tools and how do the features map up to traditional ADO.NET use cases.  We simply have no idea how the vendor intended for you to perform certain actions. 

Did the vendor encapsulate you from VistaDB?  If you are not working with ADO.NET constructs we will have no idea how to help.  Either the vendor did or did not encapsulate you from the database.

Encapsulation from the database?

A fundamental property of encapsulation is that it hides the underlying complexity from the user.  The user should only have to know what the component does, or how it achieves the underlying work.  Encapsulation should also protect the user by not allowing them to get the underlying object into an inconsistent state.

Another benefit of encapsulation is that in good implementation it should reduce the complexity of the system, and limit interdependencies between software components.

Relational Databases are complex.  The ADO.NET data model is complex.  When you start looking at the knowledge required to navigate between SQL, relation data, and ADO.NET it can be quite formidable. 

Continue Reading...

ADO.NET is a set of libraries included in the .Net framework to facilitate the communication of applications with various data storage mechanisms.  These libraries form the basis for all third parties to provide data access services to users of .Net applications.

Visual Studio 2005 and 2008 did not change the data access model.  In fact ADO.NET 2 is the longest running Microsoft data access technology without a major revision.  I don’t know if this is going to change in .Net 4, but the stability of ADO.NET is a major reason for its adoption in VistaDB.

In this article I going to give a high level overview of the ADO.NET object model and how VistaDB supports that model.  I will then follow up with more articles discussing specifics for this model.

Continue Reading...

I got a query from a customer this week that was taking a long time to run.    When I first looked at the query my first thought was that the indexes were wrong, or just the joins across 4 tables were killing the performance.  But almost all of the lookup tables were really simple value lookups.

The submitted query took 1 minute 55 seconds on my machine with his data.  After rewriting the query, and adding a few indexes I was able to get it to run in 0.86 seconds.  That is an AMAZING performance change for a fairly simple rewrite of the query.

Continue Reading...

We have rolled out BlogEngine.net as our blogging engine now (you should be reading this through BlogEngine unless you are reading the RSS).

The migration actually went really smooth.  There are a few things I miss from the old engine, but the presentation side of things is great.

In the process I decided I wanted to customize the output of the blog for the single page displays to include AddThis.com, DotNetShoutOut, DotNetKicks, and DZone social network voting systems.  But I only wanted it on the single page view, on the main view I think they tend to look too cluttered.  Part of my way of doing it led me to write my first extension.

Continue Reading...