VistaDB .Net Database Blog

Entity Framework support - Be there first, or getting it right?

js_vistadb — Thu, 02 Jul 2009 19:50:10 -0300

I have been working on this blog post for around six months. I keep writing and shelving it. Today I read a blog post from the DexExpress CTO that got me back to wanting to publish this post.

Early Adopters?

To put what Julian said in different terms; Do you want all the Early Adopters for your product? There are pro and con arguments to having the early adopters when it comes to technology.

While they tend to be some of the most passionate people out there, and they are usually eager to play with a technology but have little to no loyalty to that technology. By the time you have stabilized your product cycle they will be long gone on to the next technology buzzword to play with it.

They also tend to have the most harsh reviews of the early technology. They will bash it as not complete, or not like product x, etc. But by the time you have addressed their issues they will never come back to update those huge blog rants they did against you in the early days. Their social media contracts tend to be very one sided – whatever is in it for them today. After all anyone on the Internet can post a blog slamming a product or technology as inferior. You don’t need credentials or facts to back you up. Just post it – people will believe it. And if the company actually listens to you and implements your suggestions you can always shrug them off as not being “agile” enough for you (IE, they didn’t give you that build the same day you asked for it).

Buzz word and gone

Microsoft is great at playing into this early adopter hype. They print magazines with all their buzz word laden technologies sometimes years before you or I will be able to put it into production. And by the time you do get around to wanting to use it in production they have often already abandoned it for another, newer, faster, more buzz word laden system.

Look at Windows Workflow (WF). Anyone who early adopted it is probably kicking themselves now. Microsoft has basically punched reset on the entire design. I really looked at implementing VistaDB serializers for WF.

They can’t remove it from the framework!

And to all the people who use the argument that they can’t remove it from the framework - that is just silly. LINQ 2 SQL (L2S) is a good example of this. Microsoft printed tons of material about it, but you weren’t supposed to take it seriously. It was just until the more serious Entity Framework came along, didn’t you know?

Well, from the number of articles, books, etc I don’t think anyone understood that argument. So if you did buy into that technology what about .Net 4? Well, Microsoft has said that L2S will exist in some capacity, but what does that mean? To me it means it means you had better be embracing .Net 3.5 SP1 for a very long time.

If your favorite buzzword technology was implemented in a SP how stable is that to actually deploy? I know lots of shops that still refuse to deploy .NET 3.5 SP1 because of the problems they had with the “Service Pack” changing how things worked for their applications.

Sync Services

We actually got bit by Sync Services rewrites three times before we stopped. Is it stable today? I don’t think so. From what I have read on the renamed and polished Sync Framework (as it is now being called) it is still being tweaked and changed. There are lots of people pushing for changes for it to support Azure (ahem, aka cloud computing, aka SQL Server in the Cloud, whatever they want to call it today).

Got Entity Framework?

I have gotten a number of emails from users wanting to look at our Entity Framework (EF) provider. After all didn’t we talk about it late last year? Yes, and we did implement it. The entire VistaDB EF provider is complete. But we will not ship it until we know it performs good enough, and that it will be around for us to support our customers.

Ship it!

So why not ship it? Why not give it to all those people who want it? Why not ride that Early Adopter wave?

Basically, because I won’t do that to you. I actually care about the technologies and interfaces we unleash on customers. I want to ensure whatever technology we present publicly as an interface is stable and not going to go away on you without warning.

We have been making lots of changes in the engine to support patterns that are particular only to the EF provider. They are not complete, and may not be by the time we release 4.0. There are just a lot of strange behaviors in the way it works because it is LINQ based – not relational based. We want to ensure that the majority of the common patterns are handled with decent performance, and then continue to improve the engine over the lifetime of 4.x.

What type of mismatch?

All ORMs exhibit a mismatch when they are mapped onto relational data. It is known as the Relational Impedance Mismatch and all ORMs have it to some extent.

An example of the problem in real world terms: If you have a group of objects you might like to foreach() over them to do something. With Entity Framework that might result in thousands of round trips to your database to answer each of those foreach iterations with one single query. You are much better off to work on a result set and pair things using set operators. EF is supposed to provide for this scenario through DataSets, but it is actually highly dependent on what code the programmer actually wrote. If the programmer does not understand what the framework is going to generate they can beat the crap out of database connections.

We have found some common CLR coding patterns that translate horribly to EF generated database calls. We have to address these issues before we ship something.

Support and Documentation Problems

Another problem with EF is the documentation. We are an ADO.NET compliant database engine. That means if you understand ADO.NET patterns and practices you can pick up our engine very easily. There is no such level of competency with EF. Everyone will be in a huge learning curve. Most of the documentation you find for LINQ is around Linq2SQL syntax (which is NOT the same). And to make it worse that L2S syntax will compile and blow up and runtime in most cases with very bizarre runtime errors. This would cause a huge support load on us. I cannot imagine how we would be able to keep up with those types of tickets when most of the issues have nothing to do with us, but the EF framework itself.

So if we ship an EF provider we are in effect having to support the entire EF framework including tools designers and runtime. That is a REALLY tall order for a small company.

Entity Framework Stability

So if we are going to have to support it, how stable is it? In my experience of our online store and related tools it has a ways to go for stable. I still have to regen our store edml model about once a month because Visual Studio somehow corrupted it or got out of sync. I also still have the GUI wizard in Visual Studio crash on me regularly. And this is all when working with SQL Server.

Who do you think is going to get those tickets if they were working with our EF Provider? We would, and there is nothing we can do to fix them.

I know of five companies that started using EF last fall for major projects. Out of those five only one is still using it in any way. All of them abandoned it for their main DAL, but one was still using it for internal tools and utilities. That tells me the framework has a ways to go for widespread adoption.

When will we ship it?

The current plan (and this is of course subject to change) is to start preview builds of 4.0 shortly after we ship 3.6. VistaDB 3.6 is feature complete at this point. We are just working on the samples and fine tuning the installer.

Rather than release VistaDB 3.7 we are going to start releasing VistaDB 4 preview builds. I will post more information on what that means in engine terms later, but our plans are to release a series of preview builds and then ship a stable around the October timeframe.

There are two things that have to happen for us to ship an EF provider.

One is that I have to see the .Net 4 EF model and know we are not going to be required to totally rework our existing code. If we have to rewrite then I will never release the current provider. It would be a waste of time to ship something that is obsolete almost as soon as it is released. I want to make sure we can support the VistaDB EF Provider for a minimum of 2 years.

The second is the Visual Studio 2010 plugin model specifications. Right now our current plugins do not work with VS 2010. If Microsoft is going to leave the current model in place and require us to update the designers then we will have to create a new build for VS 2010.

Visual Studio Support

We cannot afford to support three versions of Visual Studio (each with it’s own designer) at the same time, so it would mean a drop of VS 2005 support for VistaDB 4. I don’t want to do that. I am hoping that Microsoft fixes their current issues, or publishes a way for third parties to not have to build multiple plugins to support different Visual Studio editions.

I don’t think that Visual Studio 2010 is going to be a runaway adoption from day one. I find that a lot of companies are just now migrating to Visual Studio 2008, but many are sticking with 2005 and hoping to leapfrog directly to 2010. But it comes back to that Early Adopter statement above. Those users who adopt Visual Studio 2010 early are going to be vocal users.

.NET 3.5 SP1 or 4 support?

You can’t build an EF provider without using .Net 3.5 SP1 – it simply didn’t exist before that time. That means you are dependant on a SP being present. (WHY, oh WHY didn’t they call it 3.6 since it added so many new features!) This means we have to ship a .Net 3.5 SP1 assembly to support EF. Would you do that and commit to that SP for the next 2 years, or wait for .Net 4?

There are a lot of great things in the .Net 4 framework. We would love to take advantage of them, but you just can’t do that without also maintaining .Net 2 versions at this time. I do eventually think that .Net 4 will replace the current .Net 2 stable that most people use. I think a lot of companies are going to jump straight to 4 from 2 when they finally switch their compile targets. And that means the version of Entity Framework in .Net 4 is a very serious factor for us in what to support in VistaDB.

Right now I think we are leaning towards 3.x staying a .NET 2 assembly without Entity Framework Support. Then stepping up to .NET 3.5 SP1 for VistaDB 4 with EF. That would mean no Visual Studio 2010 support in the initial releases unless Microsoft changes something.

What are you doing?

What about you? What are you doing with regards to .Net versions and Visual Studio? Are you going to start shipping 3.5 SP1 tied assemblies? Or would you rather wait for 4?

Is your company still shipping 2.0 assemblies today and skipping .NET 3 entirely?

I hope this has provided a little glimpse into how complicated it is for us to decide how and when to ship things. It is never easy, but the entire .Net 3 product cycle has thrown a lot of our plans into a spin.

VistaDB 3.6 Build 86 Preview

js_vistadb — Wed, 01 Jul 2009 19:50:17 -0300

This is an update to the VistaDB 3.6 preview builds as we continue to push forward towards release.

The Data Builder now has tabs support on the SQL input windows (yea!). And some minor display changes.

There are a lot of API changes in this build, but I think we are finished for changes to this version. The majority of the API changes were to complete the ripping out of the V-Index system that was non standard. It has been deprecated since VistaDB 3.2 or 3.3, and we are finally removing it.

We also changed the event handler and object class name for monitoring PRINT messages from your SQL Code. The help has a new topic on how to monitor SQL PRINT messages in your code. We changed the class to match the naming from SQL Server.

This installer will only be able to uninstall and upgrade 3.6 installs. If you are running any 3.6 prerelease you can just run this setup. If you are running any previous 3.x build you must uninstall it first before installing this build.

VistaDB 3.6 PREVIEW
Build #86, 07/01/2009

NOTES

The NUnit tests in this build have been updated. There are over 600 NUnit tests present in the current system.

IVistaDBCredential interface has been removed. This also removes it from any calls that may have had it implemented ( OpenDDA ).

DefineVIndex has been removed. It was only included for 2.x compatibility and deprecated since 3.3. VScripts are not supported in indexes at all - we have removed them from all index structures.

IVistaDBIndexInformation changed to remove the V-Index specific properties of the interface. They were not supported and flagged as deprecated. This also then chains down to all classes that implement this interface. DBStructure.IndexStructure is the most notable since it is returned by enumerating over indexes on a DDA Table.

Data Builder Changes

Data Builder has quite a few small changes and updates to dialogs. One of the most notable is that almost all of the forms that accept text input now support Tabs! You can Tab in your SQL query code and the tabs should be preserved now. The Line / Col display may not be quite correct in all situations since the engine does not know how to count the tab character. So error messages may be off by a few characters if you use tabs until we get all that working correctly.

Alter / Create table form Identity checkbox has been changed to render black when enabled and gray when not. This should make it easier to tell when you can check the box.

The Use Default Value in Update has been appended with (Not SQL Standard). This is one of the cases where we have a feature that SQL Server does not support. If you want to upsize to SQL Server at some point later the only way you can accomplish this feature is through triggers. We wanted to make sure it was labeled as non standard, and may eventually make a "strict" compliance mode that does not allow non standard features.

ISSUES AND FIXES

Changed the PRINT notification event and callbacks to match SQL Server naming conventions. The event is now called VistaDBInfoMessageEvent and VistaDBInfoMessageEventHandler.

TransactionMode=Ignore had a bug when a transaction was auto enlisted in a child execution. The rollback was reporting an error. There are no other known issues with TransactionMode Ignore at this time. We would appreciate any testing you may be able to provide on this feature.

OTHER NON TICKETED ITEMS

VistaDB.Diagnostic.Errors have had some changes to better english constants. The error numbers are all the same. Some of the error messages were also rewritten to not use ' at users requests for easier parsing from within CLR languages at runtime.

Documentation updates and changes.

VistaDBEngine classes will throw ReadOnlyExceptions upon user modification rather than NotImplementedExceptions (the engine class is readonly).

VistaDB 3.6 Build 85 Alpha

js_vistadb — Fri, 19 Jun 2009 15:44:00 -0300

As was discussed in the previous blog post about VistaDB 3.6 we are now ready to being Alpha testing.

Why Alpha?

This is a new installer. There could be problems with the install process and I want to work them out quickly. I expect to have a few releases of 3.6 during the Alpha stage before we are ready for Beta.

There are a lot of internal engine changes. We have 3 known bugs we are still working right now, but we want to get others started testing as well to make sure we didn’t break anything with existing apps.

What is in this release?

New Tarma based installer. I am planning to do another post about our switch to Tarma, but things have gone very smooth so far. I am quite pleased with the performance and options of the installer. Their support has also been fantastic and very helpful.

The sample databases are now copied to the shared public folder of users. Shared Documents\VistaDB\Databases and Scripts are now included. This is to make it easier to use the demos on a UAC enabled machine without requiring Admin rights. The blank database template (blank.vdb3) is also copied there now. When you right click on a folder and add a new database that blank.vdb3 is the file copied. This allows you to setup defaults you always use (page size, locale, etc) and get them when a new database is built.

Data Migration Wizard is NOT included yet because we are making changes to the interface and functions. The older DMW will be made available as a separate download for users who need VistaDB 2 migration with instructions on how to use it.

Samples are also not included other than the NUnit test project. Samples are being restructured and will be ready before the final release of 3.6.

Release Notes

VistaDB 3.6 ALPHA
Build #85, 06/19/2009

DISCLAIMER: This should be considered a pre-release version of VistaDB and as such should not be considered stable or safe (or deployed to end users). There may be issues within the code we have not uncovered yet.

Always make backups of your data. The file format has not changed and all current applications should continue to run. We have made some interface changes, but they are additive - nothing has been removed.

NOTES

AssemblyVersion changes - We are changing the assembly versioning to now just include Major.Minor. This means that your major.minor will be what is visible in the GAC, and will avoid binding issues for each new build. Only new minor builds will have to be rebound. I also greatly simplified the App and Web.config scenarios.

AssemblyVersion - 3.6.0.0
FileVersion - 3.6.85.1 MAJOR.MINOR.BUILD.EDITION

FileVersion is not seen in the GAC, but is visible if you right click a file and go to properties - Details. You can see the File Version on that dialog.

This should just mean that it is easier to bind against VistaDB. Especially users who build lots of CLR Procs will find that you only have to rebuild them when a minor version increments rather than for each build.

ISSUES AND FIXES

InvalidFunction exception when exec call to StoredProc and unopened connection - Changed the Preparser to look for this situation and tell you the database connection must be opened first. The engine CAN exec some things without having an open connection (Create database). But when the list of stored procs is attempted to be loaded it fails because the connection is not open.

RAISERROR syntax has been added to the parser for TSQL Procs. Note the spelling. This is a bit strange to me, but it is how SQL Server spells it.
You can now call it like this:

RAISERROR ( {IntID|StringMessage|Variable} ,Severity, State )
RAISERROR ( 'An error happened', 1, 1 )

Severity and State have no meaning in VistaDB, they are present for compatibility reasons.

Additional arguments to the call are not supported at this time.

PRINT syntax has been added.
PRINT output is not returned within results to queries. It is a separate side-channel of information sent back through the VistaDBConnection.PrintNotification event.

ConnectionString new option:

Transaction Mode=[on|off|ignore]

On is the default and transactions operate normally
Off means transactions are not to be attempted, creating one will throw an error
Ignore means that attempts to create transactions should be ignored. This is useful for people using tools that generate transactions through SQL and are unable to turn them off in the tool (ORMs frequently do this)
This option has also been added to VistaDBConnectionStringBuilder as the TransactionMode read only property.

VistaDBDataReader.Read() on a non-result returning command would throw an error due to a missing result set. It will not return false since no results are available.

OTHER NON TICKETED ITEMS

A lot of internal structure updates from our 4.x dev work. Things of particular note are the way that we match indexes eligible for inclusion with the optimizer. We are still using the same indexes (only those present), but this lays the foundation for us to build temporary or internal indexes for optimization. We have also removed cross object dependencies internally on some objects to avoid pinning them in memory longer than required.

Most of these internal changes should have little effect on runtime (ok, maybe a slightly smaller footprint in some classes).

We have simplified the code paths and internal classes in many cases to avoid virtual calls and removed them on internal classes where possible so the JIT optimizer may inline more of these functions as well.

Databases with large numbers of stored procedures (25+) will load much faster now due to internal changes made in the load structures and how we cache the data from the disk. We don't anticipate any problems, but we want people to test this code in their apps as well.

Some interfaces have changed as a result of code changes. And a few obsolete methods have been removed from the public interfaces. As an example HasDataSetResult was flagged as obsolete since 3.2 and has now been removed.

You may also notice some Intellisense cleanup as we have been attempting to go through the public classes and flag designer attributes as non browsable to avoid intellisense confusion of how to use objects. Many of the ADO classes have Design Time properties that are not intended for end users, only Visual Studio.

VistaDBException now inherits off of System.Data.Common.DbException instead of straight off of System.Exception. This allows for the generic user of the Provider Factories to trap DB errors and retrieve the provider error code without having to cast into the VistaDB namespace.

The DATE() function has been deprecated since it was not compatible with SQL Server syntax and caused quite a bit of confusion for users.

BuildVIndex has been removed from the Interface. It has been deprecated for quite some time. BuildIndex is the correct function to call.

Safety Warning

As always make backups of your application databases just in case. We are using this engine with our systems, but we ask that you not publish this edition to users other than if you have your own testers who understand the risks.

How to help us give you better SQL performance

js_vistadb — Thu, 18 Jun 2009 17:16:20 -0300

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.

Entity Framework SQL patterns are totally different

Entity Framework is one use case that recently broke a lot of scenarios for us. The short lived pattern of EF questions, and the way they are structured perform very poorly with our engine because they are so far out of our normal patterns. We have a pretty good sampling of these now and we will work on them in a future release to incorporate what we learned.

Submit Use Cases

We are more than happy to accept use cases from users to incorporate into our testing. As we go through and optimize areas of the code we try to look at all the scenarios we have and ensure we cover the majority of them.

Now if you have a statement with 10 inner joins mixing outer joins across tables that will probably never make it into the mainstream test pool because it is just not a common operation. Likewise, people who write up contrived scenarios that are possible in SQL Server and not us will not be included. There must be valid reasons for a test. Just because a consultant knew some whizz bang way to get SQL Server to do something does not mean we are going to implement that one off scenario from SQL Server code.

What to include

A good use case should include all of the following information:

Database Schema

We have routines to full any schema with random data for test purposes. But before we can do that we need to see actual schema.

Schema
- Fields like TEXT and IMAGE should also include an expected average size and datatype being stored
- Nullable fields should include % of expected NULL entries
- Any text based fields with Locale specific settings should be included
Indexes
Relationships
Triggers
Typical number of rows per table for an average user
Extreme number of rows for a power user (if applicable to your product)
Database Page size and Locale

Queries

The queries themselves are of course more difficult in some ways. Often a slight rewrite of the query will result in radical performance differences. Each SQL query that is important should be split into a separate item for test purposes. If queries must run as a combination to answer a single business logic question they should be grouped together to demonstrate the application use scenario.

SQL Query
- Number of times this query is run in an average session
- Number of expected rows returned on average
- Relative importance of this query to your application ( a scale of 1-10 is often all that is required here)
- Business Purpose of this query – it is part of a chain of queries?
- Are there interactive or free form query elements? (like search in an application)
Stored Procedures
- Why is this code in a stored procedure?
- Could this logic be duplicated in C#/VB.Net?
- Business purpose of the procedure
- Number of times called in an average session
- Relative importance of this query to your application

Application Information

What is the expected runtime for your application? Does it run on multi processor machines, or does your application only ever run a single query?

Application Purpose
- Target Specs
  - Memory usage
  - Diskspace
  - .Net runtime
- Threading internal to application
- Number of users
  - Readers vs Writers
- Caching strategy – is any data cached in your app to prevent frequent lookups?
- Special application needs

Submitting your information

I think we will need to create a form to allow users to submit this type of information. Should it be a web form you fill in, or some type of desktop app that can analyze your schema and ask you questions? What do you think?

As always we appreciate your feedback as we continue to make VistaDB better and more robust for your needs.

Microsoft deprecating Oracle Client from ADO.NET 4

js_vistadb — Tue, 16 Jun 2009 13:56:00 -0300

Microsoft has announced through a blog that the ADO.NET 4 spec will mark the Oracle client as deprecated with plans for removal. They are also recommending that users go to a third party Oracle provider.

Visual Studio Magazine has picked up the story now as well.

Stop Helping the Competition?

Wow, this will mark the first time an ADO.NET provider has been removed from the .Net framework. Is this a case of Microsoft trimming the fat, or pushing Oracle? I always thought it a bit of a free ride that Microsoft was helping Oracle by building their provider for them, but no one else got such special treatment.

And equally as strange to me was that Oracle was built in, when no other third party could get their provider included. I guess they are going to make that equal across the board, no one else gets included.

Another reason to stay managed code!

This is yet another reason to stay with fully managed code as well. You won’t have provider install issues that these guys are going to have going forward. Oracle client users are now going to have to install a provider that probably will not be 100% managed code. That is going to mean two or more distributions (32 / 64 bit), and probably issues with medium trust support.

VistaDB of course is 100% managed code and has no such issues. Since we can deploy a single assembly to both 32 and 64 bit machines we get around the entire install issue. And since we are 100% managed code with no DllImports you never have a trust issue with the admin settings on the box.

Oracle = Large?

This is going to affect a lot of Oracle developers eventually. But since most Oracle shops are pretty large companies maybe it is not such a big deal anyway. Larger companies take a long time to migrate from one platform to the next. Let’s fact it, .Net 4 might be 10 years away for them anyway. Some of them just made the move to .Net 2.

Any smaller shops using Oracle? I honestly don’t know any, but we walk mostly in SQL Server circles.

New features and changes coming in VistaDB 3.6

js_vistadb — Mon, 15 Jun 2009 01:05:00 -0300

As I was preparing the release notes for the upcoming 3.6 Alpha release I thought it might be a good idea to also just post a general update to everyone on the blog.

VistaDB 3.6 Alpha

We are planning an Alpha release of 3.6 hopefully this week. We have a few minor issues before we can let it loose, but we are very close now. As with all of our prerelease versions this will only be available to existing VistaDB subscribers.

This release was designed as an overall update to the product line from user requests and feedback we have received. Some of these items were added to the 4.0 line and are being ported, others were built just for this release.

Assembly Version change

We are going to start setting our Assembly version Major.Minor.0.0 like Microsoft. A change will only occur when a breaking change is introduced. We will start using the Assembly File Version as the build information for users to know what build they are running. This will allow us to not need Policy files or binding redirects until a breaking change is introduced.

Installer change

The plan is to move to a newer installer that will let us build multiple versions easier on the backend through our build scripts. Currently each build takes us about 1/2 a day to complete and we would very much like to automate that process. We are moving to Tarma as the desktop installer and I can't wait for everyone to see the new install process.

Enum functions cache

The internal enum functions that pull information from a database for things like Indexes, stored procs, etc are all very paranoid about reading from Disk. There are many cases where this enumeration is quite expensive, and caching it at the database level should improve performance for a lot of meta data operations (especially things that need to get a list of procs like EXEC, and the DataBuilder).

Transaction Mode

Adding a new flag to let you tell the engine you don't ever want to use transactions, even if the SQL code calls for them. This is useful in situations where your ORM or other library is generating SQL code that is using transactions for every call. This will be a connection string flag to allow you to turn them on / off / or ignore them entirely. This will allow finer grain control from the user perspective at connection time for transaction support (which can be quite expensive).

Namespace cleanup

We are hiding some methods that are not intended for end users, and cleaning up some others to more closely match SQL Server.

Exceptions Inheritance

VistaDBException now inherits off of System.Data.Common.DbException instead of straight off of System.Exception. This allows for generics user of the Provider Factories to trap DB errors and retrieve the provider error code without having to cast into the VistaDB namespace.

Data Builder UI Changes

We have some more UI changes we would like to make to the Data Builder. I don't want to say too much at this point, but it is to make your life easier writing SQL in Data Builder.

SQL Syntax Changes

RAISERROR Support

SQL Syntax support for RAISERROR within TSQL Procs. This feature has been on our list for a while now and I am quite pleased with the initial implementation. There are also some new guidelines around using TSQL commands like this in the help file.

PRINT Syntax

This has been requested a lot. The ability to print from within SQL and receive it as a part of the log in the DataBuilder, or subscribe to those messages from within your own application. New documentation has also been added for this function with some guidelines as well.

DATE Syntax

DATE was the older VistaDB 2.x syntax for the SQL Server function GETDATE(). It has been marked as deprecated for a little while, and has always been flagged as not being compatible with SQL Server. It has caused more than it’s fair share of confusion so we are removing it in this release.

Quick Roadmap Updated

Just in case you were not aware we do keep a roadmap page on the website. And don’t forget to look at the sitemap for other pages that maybe you didn’t know existed.

VistaDB Continues Moving Forward

VistaDB continues to move forward along our design goals for the 4.x branch as well. We are trying very hard though to not let the 3.x product line fall behind while we work on that branch. I hope the 3.6 release will solidify in your mind that we continue to think of our customers at each step of the journey.

Purchasing the correct VistaDB license

js_vistadb — Tue, 09 Jun 2009 16:05:12 -0300

Looking to purchase VistaDB, but confused as to which license to purchase? You can always contact us through a Quick Ticket for help. But please take a few moments to read through the definitions and outline below.

Common Customer Scenarios

Open Source projects, hobbyists without the need for Visual Studio tools and other volunteer efforts with no commercial goals should look at VistaDB Express.

Any size entity working on a product that has goals to realize sales at some point must purchase a full license to VistaDB.

Here are some scenarios that are all acceptable for the VistaDB Personal Edition:

Personal Edition FAQ

Just starting your own company and you have no employees
Working on something for your family, or school
Prototyping an idea alone at home with the intent to sell it one day
Single person consultants who build solutions for clients and do not provide them with the source
Building a website for your own personal use
Building a product for sale by yourself (regardless of your legal entity) – As long as you are a 1 man shop of any legal entity type you may use the Personal Edition

The following situations all require VistaDB Business Licenses:

Business Edition FAQ

Consultants who provide the client with source (commonly referred to as a work for hire agreement) must get the client company approval to use VistaDB, and they must acquire a valid license. If you build a system for a large company they must purchase a Corporate License.

Any group of 2 or more people working on a project that involves VistaDB must purchase a VistaDB Business License.

Building a product for commercial
Building a website under contract – the customer must hold the VistaDB license unless you specifically do not work for hire and retain all rights to your code
Building an internal tool for your company to automate tedious tasks
Contracts that contain Escrow agreements cannot include VistaDB. Both parties must purchase a valid VistaDB license with Source if they desire it. Source licenses are not transferable under any condition.

Personal Edition

There is now only one option available for Personal Editions, a single year with support. The 90 day option was being abused by companies so we have removed it.

We will cancel accounts who purchase incorrect editions for the intent of fraud.

We do monitor every purchase for compliance with our license guidelines, if we see someone purchasing a Personal Edition on a Corporate American Express it is definitely going to throw up some red flags.

Since changing the pricing structure and enforcing our policies on all new purchases we have seen a large increase in Business Edition sales. This is important for all of us as it ensures that we can continue to provide the best possible product at the best possible price. When users defraud us of the correct license they are hurting the entire community of VistaDB users.

Business Editions

Small Business 2-24 employees may purchase this license for the purposes of development. The cost per developer install is ~$70, but each workstation must activated one at a time.

The Corporate license provides a key that may be used on any number of machines at the same physical location without the need to activate each individual workstation. Often this benefit is reason enough for many Small Business owners to buy this edition as well.

Support

We recognize that the current pricing does not take into account companies who develop internal tools compared external products for resale. We know this is a flaw in the current system (no system is perfect). Companies who develop commercial products for resale do typically generate a much higher load on our support system. We plan to address this in a future pricing change.

Many database companies require a percentage of sales or some level of commitment for payment at certain sales volumes if you are going to embed the engine into your commercial application. We really do not want to go to this model as it would require a large amount of tracking and maintenance that we simply do not have the resources to perform. We will have some sort of per install fee associated with the server when it becomes available.

There has also been a lot of internal discussion about going to a ticketing system for support. We would include a set number of tickets with a subscription, after which the client must purchase additional tickets. This would probably cut down on the frivolous contacts and those just looking for debugging help on their application, but is another system we would have to build and maintain. We may do it just to help us cover the cost of support.

As always the Community Support Forums are for anyone to ask questions, but they are not technical support. Only ticketed items are considered technical support.

Third party complexities

js_vistadb — Mon, 08 Jun 2009 18:43:55 -0300

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.

Encapsulation can also mean data hiding

In C++ classes I used to teach that encapsulation also meant data hiding (one of the tenants of good object oriented design).

How can you debug or diagnose a problem in their tool? Any tool that is wrapping VistaDB to hide the complexities of databases from your code is now responsible for that database management. Either the vendor hides you from the complexities or they don’t – there is not much room for a middle ground here.

If you still have to drop to ADO.NET code to do things like build relationships or do reporting then did the tool really save you that much?

What should you do?

Contact the vendor and ask for their support. If the vendor has an actual issue with VistaDB not performing an expected action then the vendor should contact us for support. You are after all supposed to be encapsulated from having to think at the database layer.

Many times we find that the vendor has a bug in their application, or in product documentation. We cannot help everyone track down issues in every third party tool.

Tools for VistaDB Express?

Vendors who sell commercial products may NOT use VistaDB for their development. It is a clear violation of our EULA. All commercial vendors must have a full license in order to build tools that expose any functionality for VistaDB.

There have been complaints to us from a few vendors who were only building when a new VistaDB Express build was released because they did not have a valid VistaDB license. If you sell a product that uses VistaDB you must have a commercial license, period.

Third Party Tickets

We will not accept tickets for third party tools without reproducible error cases for VistaDB runtimes. Issues that are generic in nature such as “SQL runs on SQL Server” or performance related issues will not be supported.

If the tool simply does not return correct results, or is somehow performing incorrectly we will not modify our database engine to fix a broken third party tool. This has been an issue before with tools that relied on incorrect behavior or undocumented features.

If the tool is encapsulating you from the database, it had better know how to generate compliant SQL for VistaDB. In many cases poorly written SQL queries will perform incredibly bad on VistaDB. If you have no ability to modify them we cannot help.

Zero modification of runtime or databases

The only way tools should be interfacing with VistaDB is through our runtime. No third party tool may modify the VistaDB database files directly. Any vendor who tells you they manipulate the database directly is lying. They have to run through the engine or they are putting your application at risk. We work hard to ensure that interfaces remain constant in expected behavior, but the underlying file format can and will change over time.

Is VistaDB really supported and tested?

Ask your vendor if they support VistaDB in their product. If their answer is “we load .Net Provider Factories” then that means they have probably not tested it with VistaDB. Do not accept a blanket – we load others so it should work answer.

Are you going to trust that to your customers? Most of the time these vendors when pressed later about an actual bug will usually leave the user without a fix because “it works with the [some other] database engine”. Don’t accept that answer.

If you are purchasing a product because you have been told it supports VistaDB make sure the vendor will actually support their product. If they give you a blanket answer about running through a test suite, think long and hard about where you will be if an error occurs in your application.

If you have no ability to fix the tool you will be in a very bad place with your customers, through no fault of VistaDB. Even though we have spent many, many hours trying to help customers get resolution from third party tools we cannot continue to do so.

We support VistaDB

We support VistaDB, we test constantly for compatibility with ADO.NET and all known published standards. When we find issues we strive to fix them as quickly as we can and to notify the entire community about those fixes.

Make sure the third party tool you choose is really going to save you time and energy. I hate to see frustrated customers dealing with situations where we can’t help them either.

VistaDB 3.5 Build 84 Source Edition

js_vistadb — Thu, 04 Jun 2009 17:39:00 -0300

The Build 84 Source Edition Update is now available within the Account Manager site.

See the Build 84 release notes for information about what is included in the current source.

VistaDB Source Edition Information

The full source of the VistaDB engine is made available to companies through a source license agreement. The source license also requires an active and current subscription to VistaDB.

The purpose of the Source Code is edition is NOT to provide daily builds. It is intended similar to how a source code escrow works.

At each major milestone companies sync up the source code in order to ensure stability in the event one company ceases to exist. We provide these drops at infrequent intervals because the majority of the time the individual builds are not important for a company.

If you want to run your application off the source builds that is fine, but always realize there will be a delay between fixes and source releases. Most companies do not ship nearly as frequently as we do and an occasional source drop gives them time to incorporate at their next update cycle rather than trying to track our current builds.

What’s included?

Complete engine source code. Everything you need to build the VistaDB 3 engine for Dot Net 2.0 and CF platforms.

What’s not included?

There are a few things excluded from the Source Edition. Things like our private keys we use to sign the assemblies, the tools we use to generate the API documentation, the methods we use to generate trial versions of the assemblies, Visual Studio plugins and a few internal tools.

NOTE: If you load the solutions and try to build you will get errors about the key files missing. Use the SN tool to generate new strongly named keys.

What can you do with the source?

The biggest reasons for owning the source is probably peace of mind. For some companies and business sectors having the code is a must for any serious development effort, for others it is just an insurance policy in case something happens to VistaDB Software.

You can build a complete database engine and customize it for your own products and services.

You can modify the engine to perform specific needs for your organization.

You do not have to share any custom modifications you make to the source back to VistaDB.

What can you NOT do with the source?

You cannot create a product to compete directly with VistaDB or to replace VistaDB. You cannot create any API based product intended to give third parties access to VistaDB file formats without them owning a valid license to VistaDB. You cannot create an open source product, or release any of the code to the public or any third party. You may not license the engine, components or DLL to any third party as a standalone product.

VistaDB 3.5 Express Free Embedded SQL Database for .Net

js_vistadb — Wed, 03 Jun 2009 15:32:00 -0300

The update to VistaDB Express to the 3.5 product line is now available.

What is it?

VistaDB Express is a free edition of VistaDB for non commercial use. This includes hobbyists, mono developers, open source projects, personal websites, user groups, sample application, schools, and much much more.

Why an Express Edition?

We get asked about distribution with free and open source projects (especially mono developers), we needed a license to specifically help these developers use all the cool features of VistaDB in their community as well.

Microsoft decided a long time ago that the Express Development tool (VB Express, C# Express, C++ Express) editions are not allowed to load third party plugins. These editions were specifically aimed at the non professional developer community. Since they can't load the GUI plugins for Visual Studio 2005 and 2008 anyway, we decided to simplify the offering for these users.

Where do I get it?

Get VistaDB 3.5 Express here or the download the VistaDB 30 day trial if you are a commercial entity.

VistaDB Express Highlights

Designed for use with Microsoft Visual Studio Express builds for personal projects, there are no Visual Studio plugins.

No Server Explorer or GUI Data Binding (Express Editions cannot load them anyway)
May not be used for commercial purposes
No in memory databases
No Isolated storage support
Database files may not be renamed (must be VDB3)
No Compact Framework support

Even with those restrictions VistaDB Express still has a lot of things you can't get from SQL CE:

Shared Hosting Support
ASP.Net websites with connection pooling support
CLR Procs – managed stored procedures
SQL Stored Procedures – highly compatible with SQL Server TSQL Procs
User Defined Functions (UDF) - support for managed assembly UDF functions
Updatable Views
SQL Exception Handling – Try catch support within SQL Procs
XCopy deployable Data Builder GUI for managing your database
32 and 64 Bit Windows Support with a single assembly deployment - no need to rebind
Single and Multi user support
Table-Valued Params and Temp Tables

For a full review of VistaDB vs SQL CE see our comparison page on the main website.

For a full list of restrictions view the EULA for VistaDB.

Data Builder UI Changes

New launch screen showing a cached list of recent stories from our Blog. We will continue to update this over time (maximum of once ever 4 hours). You may uncheck the update box to prevent it from checking for a new landing page. The launch page also shows the most recent databases for quick reload of the files.

New menu items in Data Builder include the following:

Online Help

Sends you to the online version of the help file.

Online Samples

The samples / tutorials main starting area on the website (under construction).

Check for Updates

Transmits your current Build information only to the server and asks for the latest update. If an update is available you will be prompted and the Account Manager site will be launched for you to login and download. Trial users will not be able to download later builds unless they upgrade to a subscription.

Query Plans

Query Plans have been removed. They were really nothing more than query guesses, and almost always inaccurate as to what was really happening in the engine. Once we get real plans working they will return.

Clear the Log on execute

Check as an option. This will prevent the log from growing long after multiple runs and make it very easy to see what was just executed only.

Help files

Help files have been updated, and a few new sample were promoted to the 3.x install as well. We will continue to evaluate these and add more as we can over time. A few samples were included from the forums as well. The online help is usually more current than the local version, but sometimes you are not online.

Updates to Engine

There are also a number of updates to the VistaDB engine from the last VistaDB Express build. We continue to release updates and improve the quality of VistaDB, thank you to all of our users for your continued support.

As always if you encounter any problems or have questions we would like to know. Please visit our forums and post your questions.

Upgrading from previous VistaDB Express versions

To upgrade uninstall the previous version first. Then install the latest release, add the reference in your applications to the latest DLL and you should be off and running.

If you have a problem running the binary don't forget that Windows Vista blocks binary files by default. Watch this video for information on how to unblock a binary.

Migrating your data to VistaDB

SQL CE Data Migration

If you are migrating data from SQL CE note that you must have SQL CE 3.5 SP1 in order to use the Data Migration Wizard. If your data is in CE 3.1 you must open it in 3.5 SP1 and .Update it to the most current format.

Access 2007 Data Migration

In order to migrate an Access 2007 database you must have the Access 2007 runtime or full product installed on your machine. There is NO 64 bit Access runtime, you must flag your application as 32 bit in order to run Access as a runtime on 64 bit Windows.

Overview of ADO.NET

js_vistadb — Tue, 02 Jun 2009 15:54:00 -0300

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.

The ADO.NET Object Model

ADO.NET at its most basic level was designed to help developers work efficiently with multi tier databases, across intranet or Internet scenarios. This is a tall order for a single framework, but I think they managed to pull it off quite well.

I often view the object model as being split into two parts – A connected and a disconnected side of the model. Even though there is no distinction between the two “sides”, it helps me to visualize how the data object was intended to be used.

Disconnected ADO.NET Classes

The objects located in the disconnected half of the model do not communicate directly with the connected objects. This is a big deal because it means each side is isolated through interfaces to communicate. I have found this is often the most confusing part of the ADO.NET spec because users think each side should be able to directly communicate.

The ADO.NET DataSet for example does not ever communicate directly with the database. To fetch data from the database into a DataSet you must pass the DataSet into the Fill method of a connected object (The DataAdapter).

DataSet Class

DataSets are a container class for a number of DataTable objects (stored in the Tables collection). You can think of this as an in memory representation of multiple tables, their constraints, relationships, and the actual rows.

The data stored in the DataSet is a disconnected copy of the actual database. Any changes you make to a DataSet only exist in RAM. You can quickly get a list of just the changes by calling the GetChanges method to return only modified DataRows. This allows you to submit smaller change sets back to the database.

The Merge method also lets to combine multiple change sets into a single DataSet. This is very useful in multi tier scenarios where you need to receive partial updates from lots of different systems and merge them into a single database change.

The DataSet is also unique in that you can read and write this disconnected data cache to disk. A common use is to put the DataSet into an XML document for transmission to multiple systems to merge into their local database.

DataTable Class

The DataTable allows you to examine the actual rows of a DataSet through rows and columns collections. You can store the results of a query in a DataTable by calling the Adapters Fill method. Once the DataTable is filled the database connection is released and operates disconnected only. You can then continue to examine the data without any further communication between the DataTable and the database.

This is important for scenarios where you want to work across a network share. Caching the data on the local machine is vital to performance of the application. Whenever possible you should not be using connected objects against a network shared database, the traffic is very expensive.

DataColumn Class

Each DataTable has a collection of DataColumns associated with it. This represents a single Column in the database. But the object doesn’t actually contain any of the data stored in the DataTable. This is just a metadata storage class about the column, its constraints, types, etc. It is quite useful for finding when a single column allows nulls, has a unique constraint, etc. The autoincrement property is also implemented at the DataColumn level since you want to ensure each row has a unique value.

One interesting usage for this class is the Expression property. This allows you to define how the data for this column is calculated. This would enable you to compute the value of an items total price based upon units sold and price per unit at the DataColumn level rather than through a SQL command. The added benefit to using this scenario is that updates to the Price and Quantity columns will recalculate the total price column in RAM without having to return to the database to rerun the SQL.

DataRow Class

Each row in a DataTable is represented in the Rows collection as a DataRow. To examine the data in a specific column use the Item property of the appropriate DataRow object. You can lookup the columns in a row through an integer index, or by the name of the Column.

The DataRow class is also where you perform updates to a DataSet. When your preparing to edit a row the BeginEdit method should be called on the DataRow object. The EndEdit method then can be used to save the changes back to the DataSet, CancelEdit provides an undo facility. This is key to how the DataGrid and other grid controls allow users to edit, commit, and cancel their changes in memory.

A key concept often missed by developers is that changes to the DataRow even when they are “AcceptChanges” calls are only committing them to the RAM copy of the database (The DataSet). You must still commit the changes back to the database in order to store them permanently.

Constraints Class

Each DataColumn may have multiple Constraints. Conditions such as unique are applied through this class. Constraint objects are maintained through the DataTables Constraints collection.

DataRelation Class

Most tables in a single DataSet will be related in some way. The DataSet can handle mapping things like Orders to OrderDetails with a little help from the DataRelation class. This is a way to enforce referential integrity from the database, without having to make a complete round trip to the database.

DataView Class

Although excluded in the diagram above, this is an important class. You can use multiple DataView objects to example the same DataTable in a DataSet. This can result in massive memory savings since only one copy of the data needs to be maintained in the DataSet. You can do a lot of view type operations with the in memory DataView object.

Connected ADO.NET Classes

Connected classes in ADO.NET are designed to communicate directly with the data source. Most of these classes map closely to basic data access concepts such as the Connection to the database, a Query, and QueryResults.

ProviderFactory Class

New in ADO.NET 2 this is an object factory that allows all .Net applications to generically load a provider without knowing much about it. Each Provider Factory includes a way to create Connections, ConnectionStringBuilders, etc.

It is this ProviderFactory object that allows VistaDB to be swapped with SQL Server at runtime through your app.config or web.config files. It is not a perfect match for communicating directly against the strongly typed classes, but it is very close.

Connection Class

A Connection is a representation of the actual phyiscal connection to the database. This may be through a server, or locally. You use this class to connect and disconnect from the actual database.

A connection also acts as the handle to the database for other objects like the DataAdapter and Command objects. They do not communicate directly to the database, they must go through a Connection.

ConnectionStringBuilder Class

This is another new class in ADO.NET 2 that simplified the process of building connection strings and remembering the options for a specific provider, it is a great utility class to save you time. Once you have built up this object you can assign the Connection class ConnectionString property to this object.

DataAdapter

The DataAdapter is a new concept, there are no matching concepts in ADO or DAO. The DataAdapter is the bridge between your database and the disconnected ADO.NET objects. The Fill method provides an efficient mechanism to fetch the results of a query into DataSet or DataTable so you can work with the data offline (disconnected). You also use the DataAdapter to submit pending changes from the disconnected objects back to the database.

This is a complex class with lots of internal jobs to track. We will cover it in more detail in another blog post.

Command Class

This class represents a question or query against a database, a call to a stored procedure, or a direct request to return the contents of a specific table. This is probably the hardest class to port your usage between database vendors as most of the calls at this level are vendor specific syntax.

Using a Command object with a database is pretty easy. You set the Connection property to an existing opened Connection object, and then specify the command you want to execute in the CommandText. You can supply SQL commands, or just the name of a stored procedure or table (although you have to then also change the CommandType property to what you wish to accomplish).

Commands have many ways to execute them, but all of them basically call ExecuteQuery under the hood. If you don’t care about the results, or only want the first row and column there are short cuts you can take (ExecuteNonQuery and ExecuteScalar).

Note that VistaDB does not support the ExecuteXML syntax at this time.

Parameter Class

To simplify the process of putting parameters into a Command object the Parameter class allows you to quickly put parameters into a query without string concatenation or worry about SQL Injection attacks. Most developers rely heavily on parameterized queries because of the added benefit of SQL Injection protection; strings are automatically quoted to the specs of the underlying database.

There are many ways to create Parameters for Command objects, but they are mostly just different ways of doing the same thing. Some people prefer one syntax over another, but they all end up as Parameter objects on a Command object at execution in the database.

DataReader Class

The DataReader is built as a way to retrieve and examine the rows returned in response to your query as quickly as possible. Not all of the rows have to be ready before you can start working with the data. Only a single row is exposed at a time, so the database engine can get you the first record quickly and then continue to work in the background to find the rest of the answers over time.

The data returned by a DataReader is always read only. This class was built to be a lightweight forward only, read only, way to run through data quickly (this was called a firehose cursor in ADO).

Transaction Class

Transactions are used to ensure that multiple changes to database rows occur as a single unit of work. The Connection class has a BeginTransaction method that can be used to create a Transaction.

A definite best practice is to ensure that Transactions are placed in Using statements for rapid cleanup if they are not committed. Otherwise the objects (and any internal locks that may be needed) will remain active until the GC gets around to cleaning it up.

What about Cursors?

You will notice that I never mentioned database cursors in the above discussion. That is because they do not exist within the ADO.NET framework. ADO.NET does not support server side cursors by design. The DataSet and DataTable classes most closely resemble a cursor, and the DataReader class is a close match for a client side forward only, read only, cursor, but it is not the same thing.

The right SQL goes a long way

js_vistadb — Fri, 22 May 2009 19:30:00 -0300

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.

Problem Query using multiple tables with no joins

I have rewritten the table and column names to make this more generic.

SELECT

  VisitID as ID,

  VisitDate,

  Minutes,

  RegionName,

  Reason,

  Name,

  City

FROM

  Visits,

  Clients,

  Regions,

  VisitReasons

WHERE

VisitReasons.VisitReasonID = Visits.VisitReasonID  AND

Clients.ClientID = Visits.ClientID AND

Clients.RegionID = Regions.RegionID AND

Visits.VisitDate >= '2009/01/01' AND Visits.VisitDate < '2010/01/01' AND

Visits.Available = 1

ORDER BY Visits.VisitID

Rewrite to use JOINs

Notice the FROM clause is grabbing from 4 different tables. This is a common Access style query. But what that is in effect going to do is SELECT * from each of those tables. The WHERE clause restricts the constraints, but by that point we have already loaded the complete schema for the table, indexes etc.

SELECT

  VisitID as ID,

  VisitDate,

  Minutes,

  RegionName,

  Reason,

  Name,

  City

FROM

  Visits

    LEFT JOIN VisitReasons on VisitReasons.VisitReasonID = Visits.VisitReasonID

    LEFT JOIN Clients on Clients.ClientID = Visits.ClientID

    LEFT JOIN Regions on Regions.RegionID = Clients.RegionID

WHERE

  Visits.VisitDate >= '2009/01/01' AND Visits.VisitDate < '2010/01/01' AND

  Visits.Available = 1

ORDER BY Visits.VisitID

Notice the joins. Basically by only selecting FROM 1 table we are telling the engine it is the most important thing for us to load. Everything else falls from there. The other tables are joined, but with tight constraints so they only load the correct amount of data from a primary key.

Indexes too

The other change was to add an index on the columns in the WHERE clause. Those two columns were not indexed, and only the date one really made a big difference, but it is still a good idea to have the indexes there.

Why?

Why is this such a big deal? Well, SQL Server and Access both rewrite your queries for you when they notice things like this. That query optimization is one thing that we are still working on. It is really hard to get it right and not mess up the original intent of the query. This is an area I feel we still have a long way to go. But you can acheive these results through careful writing of your queries. If you have a query that seems to perform poorly, try to do it another way.

Simplified Database

I did simplify the database and schema to make sense for the purposes of this demo, but here is the database with just a few rows in it. The customers had about 100,000 rows total.

Sample Database

ExampleMultiJoin.vdb3 (316.00 kb)

Sample Queries

Blog-SampleQueries.vsql3 (902.00 bytes)

What do you think about the blog update?

js_vistadb — Wed, 20 May 2009 01:55:00 -0300

What do you think about the new blog layout and update? Like it, hate it?

I am not 100% happy with the layout yet, but I am planning to make some layout changes to the main website and will update this at the same time to match more closely.

Things that make me happy

This blog runs VistaDB 3.5 - We have a lot more control over the specific builds of the engine that are run as a part of our blog now!
Better integration with the main website
Search queries also show related topics (nice feature in BlogEngine)
Integrated buttons for social media sites and AddThis.com
Blog Archive includes all topics broken out historically
Built in quick poll system
Sidebar remembers you when you return and have made comments here before, gives you some update information
Easier RSS feed per category for user control
Built in Sitemap generator for Google actually increased our blog indexes in Google pretty quickly

Dislikes

Integrated blog stats - I have split them out in Google Analytics now so I can track blog usage separate from main website
No versioning of blog posts
APML - I still have no idea why I would want to use it, but it keeps asking me if I want to apply my APML filter...
Admin panel for BlogEngine still feels clunky to me
Editor does not seem to always like Chrome and tends to default to a raw HTML view
Adding tags to a new post does not AJAX for existing tags - I have to remember them...

Two thumbs Up!

Overall I definately give the BlogEngine guys two thumbs up. Very easy to setup and run a blog using BlogEngine. And I also just learned they are running on Mono now.

BlogEngine rollout and my first extension

js_vistadb — Mon, 18 May 2009 00:44:00 -0300

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.

Missing?

First, there is nothing really missing. I guess miss a few features from Graffiti. The admin view page in Graffiti shows you the number of views your blog has had for the day, and is a new graphical boost to your blogging ego. I have found it a great way to track over time if the blog is gaining readers or not.

Of course it turns out this is also the most terrible thing in Graffiti for performance. Most people I talk to who say that Graffiti performs poorly with VistaDB are actually talking about this form. The data is stored in a table named Post_Statistics and it is very poorly normalized. My current table had 1.9 million rows in it. Each and every time a user read from a post an entry is made here.

This is a terrible table because there are NO indexes on the posts column, or the DateViewed column. Adding them speeds up the query to sum the dates a lot, but it is still going to be slow after a while. The data should have been normalized to only include hits from a specific date, and then to summarize the dates statistics into another long term table. Periodically cleaning this table has been the best way to improve the performance of Graffiti.

Another thing I miss is the ability to view multiple versions of a post. Graffiti stored all the changes to each blog entry, and let you view all the changes over time. Now to be honest, this was rarely used. But it was always comforting to know that if the editor went nuts when I was editing a post (or IE posted garbage) I could recover to an older version of the post by a simple rollback type procedure similar to what I do with my source code.

Social Network Extension

I liked the customizations I had made in Graffiti to include the AddThis.com Bookmark button and other social network sharing buttons like DotNetShoutOut. Initially I made the same changes to BlogEngine for my themes primary view. But I didn't like the look on the main form when you have 5 posts showing to see 5 AddThis buttons as it didn't make sense to me. I only wanted it to show when a single post was being viewed.

Well, it turns out I could have done this in the theme post view code behind (there is a SinglePost flag to tell you when the engine is serving a single post), but I made an extension. It only took about 30 minutes and I think it is sort of neat to have it.

This is a screen shot of the extension panel I made, pretty cool that you can include help as a part of the extension.

Basically you can turn this on or off for your theme and it will insert the button at the bottom of your post (optionally only when you are in single post view mode). You can specify the account for AddThis and some other options.

How to use the SubmitStoryExtension

You can use this same extension in your own BlogEngine.net blog by putting the code in your App_Code\Extensions folder and loading the admin panel. It will show up as an option and you can select Edit to change your options.

SubmitStoryExtension.zip (2.69 kb)

I was really impresed how easy it was to build, and it was fun to play around with the object model and get my feet wet in the code.

Visual Studio 2010 Beta 1 for MSDN Subscribers

js_vistadb — Sun, 17 May 2009 21:14:00 -0300

Microsoft Visual Studio 2010 will be available on MSDN May 18, 2009, and a general download available later for non MSDN subscribers.

EDIT: It is now available - we are downloading it and the .Net Framework 4 beta 1.

Microsoft Australia has pages up already showing the UI improvements.

There is also a PDF file available with information about Visual Studio 2010 here.

Some highlights from the information:

.Net 4.0
Cloud Development - Windows Azure which is basically utility computing where you pay by the hour for your usage.
Parallel Development - Not sure if this only means PLINQ for .Net developers or if they are only talking about C++ devs.
TDD Improvements - Editor changes for building test cases first, and then writing the code.
ASP.NET - CSS grids and design surfaces changes, more Javascript debugging improvements.
C++ Compiler - Lots of changes for C++ development, including MFC.
Sharepoint templates
New Project Templates
Windows 7 - MFC updates for Ribbon UI, and WPF changes.
Application Model Changes - Reverse existing codebases, and lots of changes in Architect Edition to support more model types (including Class Libraries finally).
Test Runner - This is potentially huge as it allows testers to submit bug reports complete with callstacks and traces that you can actually use to resume an app at the error point to avoid the dreaded No Repro on bug reports. I have seen this demo and it is quite impressive. We will see how it actually works though.

And just in case you didn't know the entire UI is now driven by WPF. It does not look like a major overhaul, but we will see once the new beta is released.

I personally am still taking a wait and see approach. We all know we will HAVE to use it. There is no other serious development tool for .Net developers. The question for me is how painful is it going to be to get our designers working in it (they have already said the designer model is changed), and what does .Net 4 entail for changes.

We will be downloading the beta and testing it with the engine for sure. Expect many updates about this over the coming months. Not because you need it today, but you will in the future.