topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 9:23 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Review of VistaDB  (Read 34924 times)

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Review of VistaDB
« on: May 23, 2011, 09:27 AM »
VistaDb Review


Basic Info:
App NameVistaDB Embedded Database Engine
App URLhttp://www.vistadb.net
App Version Reviewed4.1 32-bit
Test System SpecsWindows 7 32-bit/Visual Studio 2010
Supported OSesWindows XP/Vista/7 32-bit and 64-bit
Support MethodsE-mail, Knowledgebase, Chat, Forums
Pricing Scheme$1295 Site License, Royalty Free, though mISV pricing is available.
Trial Version Available? Yes, 30-day no limitations.
Upgrade PolicyFree updates for new builds of the same version.  Support Subscriptions available.
Reviewer Donation Link wraith808

Introduction
VistaDB is an embedded, single assembly SQL-based database engine for .NET.  VistaDB is highly compatible with SQL Server, down to the ability to execute T-SQL against the database, and utilize native data types and stored procedures.  It has full compatibility with Visual Studio 2008/2010, and allows utilization of CLR procs and triggers, utilizing a fully managed and typesafe c# architecture.

Review Notes
I've always found when developing desktop applications, the database support is somewhat lacking.  I've used Microsoft Access, DBase, SQLLite, Paradox, various bastardizations based on XML... and all of them have left me a bit disappointed.  Why couldn't there be something like SQL Server or Oracle for the desktop?  In another thread, Renegade suggested VistaDB.  But it's a bit pricey, even with their various programs to help out those that aren't going to make an living off of the software they develop.  So I'm going to have to go through a pretty intensive evaluation process.  I was going to do a review after the fact - but figured that my evaluation could be the review, as I'm sure otherwise I'd miss some of the points that would come to me in a review.  As a result, as I go through the evaluation process, I'll post a review on the current task and the advantages and shortcomings of VistaDB- and my verdict will come at the end, as I come to a buy/no buy decision.

NOTE: Because of the fluid nature of this review, if there's something that you need me to take a closer look at, feel free to let me know.

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #1 on: May 23, 2011, 11:12 PM »
Step One: Downloading, Installing, and Registering the trial

In many cases, especially with smaller companies, registering a trial can be a trial in and of itself.  Though one does have to give contact information for the registration which I usually dislike, even as I understand, a key from the e-mail entered into the database administrator as you open the application is all you need to register, so it is straightforward.  There is also something else that I see as great foresight- there is a way that you can reset your activation, I'm assuming if you need longer to evaluate.  That this is already present speaks volumes about their understanding of the desired audience.  The installation itself is pretty standard, so nothing to report there, which is a good thing when it comes to installers.

Rating for Installation 9/10

Notes: One point deducted for having to give particulars to get an evaluation key)

Step Two: Deciding on a project

I'd already been working on a project for a game launcher/cataloger from a request in a thread.  I hadn't decided on the data store for the application, so this thread on VistaDB came at a perfect time.

Step Three: Database Design

The application will have a main Games table, with most of the primary information requested.

Two foreign keys are going to be on that table, both linking to GameCompanies to detail the Publisher and Developer.

GameCompanyContacts will be connected to that table, to allow many contacts for each GameCompany.

GameCustomFields will be connected to the Games table, to allow the user to enter custom fields to be displayed on the particular game.

GameLinks will also be connected to the Games table to have URLs for resources connected to the game (for support pages, mobygame page, etc)

GameTags and GameGenres will be connected to the Games table by two pivot tables (Games2Tags and Games2Genres) to allow many GameTags and GameGenres to be re-used amongst games.

Creating these tables was a lot easier than with other solutions that I have utilized, because of the inclusion of an administration tool.
VistaDBDataBuilder.png
Vista DataDB Builder

The VistaDB Data Builder is a full fledged tool, useful in its own right.  It allows the administration of all objects in the database, direct SQL, and manipulation of the objects in LINQ.

I haven't been able to work with the LINQ objects, and have a forum post on that particular subject in the works, so I will update with how that is going.  But the graphical database administration works wonders.

The first step is to create the database and tell the administrator where you want to store it.
VistaDBDataBuilder1.pngReview of VistaDB

After this, the main window appears with an appearance very similar to the SQL Server Management Studio.
VistaDBDataBuilder2.pngReview of VistaDB

Clicking the new table button brings up a window to create the table, and after the table is created, columns can be added.  
VistaDBDataBuilder3.pngReview of VistaDBVistaDBDataBuilder4.pngReview of VistaDB

It's worth noting that the buttons in the toolbar are not all related to the tab that you're on; the save and cancel buttons are for the entire table, but the other buttons relate only to the columns tab.

As columns are created, each is typed with a standard SQL type, and clicking in the first column of the table sets that tablecolumn to be part of the primary key.  A point I noticed at this juncture is that modifying multi-column keys seems to run into some problems at points.  It's not all the time (indeed, I had problems reproducing it to the point that I couldn't narrow down the steps), and is easily resolved by deleting the columns in the key and re-adding them, but it is something that I noted.

After columns are added to the key, whether they are sorted ascending or descending can be set in the first tab as the fields in the key are shown here.

So, after adding all of the tables, I then decided to create foreign keys.  For the most part this was pretty straightforward; go to the table where the foreign key is to be created, select the foreign key section, and click the add foreign key button.
VistaDBDataBuilder5.pngReview of VistaDB

In the dialog, the keys of the parent table for the FK are shown, and the other side of the key must be assigned.  The keys also support cascading updates and deletes.  This works well in most cases, but as shown above, for this application, there are many to many relationships to be represented by pivot tables.  In my case, the pivot table was defined as shown in the image above.  For each FK, you have to assign all fields, which doesn't work for this.

But this gives the perfect segue into the query analyzer.

Clicking the query leaf of the tree view opens a view very much like the SSMS query analyzer.  Type in the SQL and go.  Since the particular setup that I wanted wasn't covered by the interface, I didn't know if it would work.  But I tried the SQL, and indeed it did create the relationship I wanted, as shown below.
VistaDBDataBuilder6.pngReview of VistaDBVistaDBDataBuilder7.pngReview of VistaDB

At this point, the entirety of the database is created, and I've had to touch SQL very little.  The SQL that I did touch worked without a hitch, so I have little doubt that I could have done the whole operation in SQL if preferred.  And this touches only the surface of the capabilities of the Data Builder- it's a full fledged application in and of itself.

Rating for Data Creation 10/10

So the first three steps of the application are done (for now), and VistaDB has proved itself quite capable thus far.  Next up... adding the data sources and manipulating data from within the application.

Step Four: Database Management/Use

I've found that as I've delved deeper into VistaDB, it's definitely giving you a lot for your money if you're already invested in SQL Server as a RDBMS platform.  Unfortunately, that has made the few hiccups I've had stand out.

My first decision was how I was going to handle database objects.  I'm going with the MVVM (Model-View-ViewModel) pattern to get some more practice in for work with that pattern.  At first, I was also going to use entity modeling, as that is sort of what I'm doing at work also - in that case using EditableObjects to represent the data objects.  But I couldn't get the entity modeling going as I stated above, and still have not heard back on that particular thread.  Instead, I decided to use typed datasets, which is also something I haven't used before.  Looking back, it might have been better to only use one variable, instead of the several that I've used, but I'm committed now.

The first problem I had was in generating the table adapters for views in the database.  For some reason, the table adapter was created, but it was not exposed in the dataset, so I had to hack it.  I've not received a response on that either, so I'm not sure if that's something with the implementation of views, or Visual Studio- I'm leaning towards the latter.  I also have found a problem with recursive queries, i.e. I wanted to use a function to return the result of a query on a pivot table as a comma delimited list.

Code: Text [Select]
  1. DECLARE @r VARCHAR(200)
  2. SELECT @r = ISNULL(@r+',', '')
  3.         + gt.description
  4. FROM GameTags gt JOIN Game2Tag g2g ON
  5.         gt.keygametag = g2g.keygametag  
  6. WHERE g2g.keygame = 1
  7.  
  8. SELECT @r

This only returned the results from the last row.  No matter how I simplified it (I removed the joins and everything else to make it as simple as possible, and still no go).  On another note, if anyone has any other ways of doing this that don't involve cursors, I'd appreciate the help.

I also had a problem in that I couldn't return multiple results from a query in the query analyzer.  But I could return multiple tables in a stored procedure, so that turned out not to be a problem- just an inconvenience.

The documentation is very straightforward in informing the user that all T-SQL is not supported, and giving a pretty extensive list of commands that aren't supported, though this was not listed.  I suppose it's jarring, because other than these few cases, it feels like I am using SQL Server, which is amazing.

Also of note, of the three problems I've had, I've posted questions on the community forums, and have yet to receive a response.  As none of them have been anything but annoying, I've not resorted to pre-sales support, so I don't blame this on their support- I'm just saddened that there doesn't seem to be as much chatter on the forums as I like for software of this type.  At the end of my project before making my final decision, I'll go through support on one of my issues (or sooner if I come across a show-stopper), to evaluate their support.

Also of note is the fact that I've had to touch SQL very little in managing the database, and again, when I have, it's been straightforward, so it's been quite nice so far to have both options.

I'll update this section if there's anything of note as I modify and manage the database.

Update (2011-05-28): I've received a response regarding the recursive query, and they are not supported.  The staff member that responded pointed me to a thread that gave a pretty involved explanation as to why.  It's disappointing, but as I said, they're pretty up front about only supporting a (very large) subset of T-SQL.  I am encouraged at the speed of the reply, though; I'm not sure why the other threads languished, but this one was a pretty fast turnaround.

Addition (2011-06-04): Experimenting with the database at the same time as I was developing was causing a bit of problems, as would be expected.  I have begun to keep backups of the database, but I also had some test data that I was using, and putting it in twice was too many times, and I wasn't really wanting to write sql scripts to load the database.  I found the solution under the File-> XML Import and Export.

Once you select the menu option, a quite simple screen appears that is has a power that belies this.

VistaDBDataBuilder11.pngReview of VistaDB

You can export the data, the schema, or both to simple xml documents.  When exporting, it seems obvious that you have to select tables.  When importing the schema and tables, the tables list is empty, so just pressing import seems the right thing to do.  When importing data by itself, you have to select the tables that you want to import.

But this has saved me a lot of time and frustration.

Update (2011-06-06): Though it should be obvious, since autoincremented key support is at the table level (d'oh!) if you export the data without the schema and then import it into a database with non matching seeds for these keys, you need to update them.  The data will import correctly, but then when you try to insert, you will get errors as it's attempting to insert a duplicate key.

Rating for DataBase Management/Usage 9/10

Notes: Increased from 8/10 to 9/10 after working a bit more with the db on 2011-06-04

Step Four: Programmability

So, now I'm to actually doing something with the database.

Stored Procedures
Because of how some things link together, and the requirement that in querying for this data, I need the results of a previous query, I decided to use stored procedures for this.

Standard DDL works when using stored procedures, and I found that the graphical interface was also pretty straightforward, and had the added benefit of restricting your data types to supported datatypes.

VistaDBDataBuilder8.pngReview of VistaDB

Functions

Pretty much the same as stored procedures, with the added benefit of supporting a construct that I thought wouldn't be available - table value parameters.  I don't know why they aren't available in stored procedures (and indeed, I'm not sure that they're not, they just aren't touted in the documentation as they are for functions, nor are they available in the interface, so I'm not inclined to believe offhand that they are).

The interface to create them is nearly the same as the stored procedure interface, except for when a table return type is selected; in that case, it allows you to define the table.

VistaDBDataBuilder9.pngReview of VistaDB

This brought me to a rather bad part about it, and one that I'm still attempting to find if there is a way to fix.  Fortunately, I'm using two different databases, and backing up often, or this would have been rather off-putting.

In the interface, it doesn't allow you to define a table as an input type, so I created the DDL to allow it.  Something rather pedestrian, i.e.

Code: Text [Select]
  1. CREATE FUNCTION [fnGetPublishers] (@games TABLE)
  2. RETURNS TABLE ( Description VARCHAR(50) )
  3. AS
  4. BEGIN
  5.    return select gc.description from gamecompanies gc where gc.keygamecompany in (select keygame from @games);
  6. END

Then to call it

Code: Text [Select]
  1. DECLARE @GamesTemp TABLE (
  2. KeyGame int
  3. );
  4.  
  5. INSERT INTO @GamesTemp (KeyGame)
  6. SELECT    KeyGame
  7. FROM    Games
  8. WHERE    KeyDeveloper = 1;
  9.  
  10. select * from fnGetPublishers(@GamesTemp);

Apparently I did something wrong in my DDL, because from then on, the function leaf of the treeview wouldn't open, and I'd get an error invalid syntax near SELECT everytime I opened the database or tried to open the function leaf- to the point that I had to force quit the application.  The database still worked, and I was able to take my last edits and transfer them to my last copy of the database.  But I'm not sure if this is a shortfall of the Data Builder or the database not to be able to handle the code.

CLR Functions

In my last section, I lamented the lack of recursive queries.  I now lament no more, for it forced me to look into CLR functions.  CLR functions are basically the use of managed code assemblies to be able to be called from your database.  And they allowed me to do what I was going to do with recursive queries, and a lot more!  They're available in SQL Server, but I used them only scarcely.  But now, I'll be using them a lot, I think.  

And they're quite easy to use:
1. Create your code project, using the attributes that they set forth to mark your methods as functions or procedures.
2. Load your database in to the Data Builder.
3. Use some SQL to load the assembly and define the functions (or use the nifty UI to do the same)

VistaDBDataBuilder10.pngReview of VistaDB
The Nifty UI (TM)

And you're done!  The functions/procedures are called as any other procedure, and there's a way to use the same context that the function is being called from to connect to the database.  So any support you want to have is just a step away.  And the assemblies are loaded into the database, so there's nothing else to distribute.

Rating for Programmability 7/10

Note: Would have been higher, but that problem with incorrectly programmed functions that are correct in SQL worries me a bit.


Step Five: Integration and Use
I decided to use strongly typed datasets with the VistaDB database.  It should also be noted again that my experience is with Visual Studio 2010.  And a caveat is that I've only touched on typed datasets before this, so a couple of these points might be due to that lack of experience, though I've searched and found no other incidences of the points that I did find.

The actual use of the database from an interface perspective isn't any different from using a SQL Server connection.  I utilized the inbuilt Data Source Configuration Wizard, and was able to point it to my instance of the database and it built the tables and adapters with few problems.

First, the table adapters for the views were not exposed.  I'm not sure if this is normal; it doesn't seem to be from my searches on the internet.  But this was quickly solved, as I was able to manually put those properties in the designer code file.  It is tedious, having to change this every time I change anything in the dataset, so I keep a pristine copy with all of the methods available, and merge those into the file every time it's regenerated.  But still a bit of a pain.

Second, it doesn't seem to recognize composite keys on views.  I have composite keys on a few of my relational tables that I use to create many-to-many relationships between a few of my objects, and I had to manually update the keys on the table to match the fact that the view could have many instances of the same key, as long as the composite key was satisfied.  Again, not a major issue, just something that I have to do every time the dataset is regenerated.

From there, everything was gravy.  I created a model to contain my data, and exposed methods to load the datatables and expose the correct dataviews/tables.  The standard databound controls worked flawlessly against the data layer, and I was able to use the standard binding manager/binding contexts to access and update the data.  I was also utilizing a stored procedure to update one relationship behind the scenes, and that worked flawlessly also.

Rating for Integration 10/10

Notes: The dataset stuff is annoying, but not insurpassable.  If that is the normal behavior of the dataset generator, then I'd give it a 10/10- it's pretty transparent and just like using SQL server, which is pretty much the highest compliment I could give it.  The fact that this section is so short speaks to how much I was working with the program rather than the database, which is what I want at this stage of the development.

Notes: Updated rating to 10.  I tested against a SQL Server database using typed datasets, and it doesn't create the adapters for those views either.

Step Six: Performance
Off to a test project to test performance.  I had an input file for a client, so I decided to use that since it was laying around.  It's a .CSV file, with 46 fields defined, so I set up a new database with those fields, and a separate key from the one in the file so I could scale it.  The file only has 1894 records, and while that is a decent amount, I wanted to get up to 100000 records being inserted for a real test.  I didn't use a typed dataset in this case, and made my own model, loading the data from the file into a list of objects that implemented the IEditableObject interface.  Then I created a cache of records- at first I used the standard 1894, but then I used a random assortment of the objects.  The results are below.

RunRowsTotal Time (mm:ss:ff)
01189400:00:06
02189400:00:06
031000000:36:12
041000000:36:84
052500001:38:05
062500001:46:57
075000003:43.88
0810000006:27:62
0910000009:16:12*
1010000008:44:46*

* - the last two tests were run on my work machine (Core 2 Duo E8500 @ 3GHz), which is quite a bit slower than my home machine (i5-2400 @ 3.3 GHz).

I should hope not to be processing more than 100000 rows at once on any application that I am using VistaDB for- indeed more than the base amount of the file would be an aberration.  But I just wanted to see the relative processing power, and given the hardware, it does seem acceptably performant, even on those unrealistically large datasets.  And on the client file, it was blazing.  In comparison, that same file using Access takes about 30-45 seconds to process.

After loading the table a few times, I had 138,273 records.  To get join performance, I wanted to have another table with a similar number of records.  So from within the Data Builder, I created a copy of the table, then ran an insert into from the original table to copy the data.  This operation took 29.593 seconds.

Then I ran a pretty simple join on the table, on a FTS indexed field, and on a non-indexed field.  Creating the index on tables of that size took only a couple of seconds, then a like query on the FTS index for a total of 152 records took 2.658 seconds to run.  A join on a non-indexed field with a where clause on a FTS indexed field for a total of 11,560 records took 3:21.9 minutes to run.  Indexing that field took the query down to 2:12 minutes.  These are pretty extreme cases; in standard use, I didn't have to wait for queries to return.  But I wanted to get an idea of the capabilities of VistaDB.

Rating for Performance 10/10

Step Seven: Support

As I stated above, I had a couple of issues, and utilized their support e-mail for a couple, and the forums for a couple more.  They do seem to frequent the forums, though there is a bit of a turnaround time, and it does appear that it depends on where the help request is posted; I had a problem getting the entity framework to function, and I never received a response to that post- that post was in the Entity Framework section of the forum.  In contrast, questions in the SQL Related and General forums were answered within a few hours.

And I was quite impressed with the e-mail support- I had a problem with the CLR procs, and though I found a way around it, they followed up on the e-mail, asked some pertinent questions and for examples to replicate the problem, and when they couldn't get it fixed immediately, followed up again to make sure that this was not a blocking issue so that they could prioritize it accordingly in their backlog.

I attempted to see how frequently fixes and patches are being posted (and new versions), but I couldn't find anything beyond the latest release, so no information on that front.

But by the speed and nature of the responses to the few queries that I did have, I have a pretty good feeling about the support.

Rating for Support 9/10

Summary and Final Analysis
Truthfully, when Renegade first brought up VistaDB and talked about how it compared to SQLLite, I was hoping that VistaDB would somehow fall short, even as I wanted something more than SQLLite or SQL Server Express (or Access).  It is quite an investment.  But for that investment, you get a stable, seemingly well established and supported product, with tools to make development easier.

It's pretty much a no-brainer that I'm going to go ahead and buy as soon as I get the money- which is the one con of the product, but understandable in comparison to what you get.  The mISV program is a considerate gesture that does make it more affordable for small shops, and a program that I plan to take advantage of.

Final Rating 9/10 - a buy rating.

Other Reviews
C-Sharp Corner
DotNet Freaks
Codango
« Last Edit: June 15, 2011, 11:10 PM by wraith808 »

steeladept

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,061
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #2 on: May 23, 2011, 11:41 PM »
Why couldn't there be something like SQL Server or Oracle for the desktop?
There is.

Oracle Express Database
SQL Server 2008 Express Edition

I know these are probably not exactly what you were talking about (My guess is you already knew about them).  Still, they are what you asked for, even if they are not what you meant.

Also want to throw in for consideration that you look at Alpha Five.  I know they have been trying to get me to buy their product lately, but I a) don't have a need at the moment and b) don't know much about them.  I can say they claim to be geared toward web development and more recently mobile apps, but they claim they work fine for desktop apps and claim favorable comparability to Access.  Are they any good?  Don't know - that is why I offered it as something else to review.  :P
« Last Edit: May 23, 2011, 11:46 PM by steeladept »

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #3 on: May 24, 2011, 07:32 AM »
Unfortunately, Oracle Express Edition is still client server, and that's not what I was looking for.  And SQL Server 2008 Express, while it would be ideal, is a bit too intrusive for the types of applications that I'm developing.  The school curriculum my wife is using in home schooling, while very good, uses SQL Server 2008 Express, and it's a real pain to install.  And that ups the requirements quite a bit for the application, not to mention taking out the ability to ever be portable.  One of the things I like about VistaDB (that I'll get to in the deployment options) is that its single assembly, and just an xcopy will install it.  Can't get too much simpler than that!

But, yeah... I should have been more explicit.  And thanks for the suggestion of the other database, but having gone down this path (and with the amount of investment it takes just to do the evaluation/write a review) I'm sticking with one option to start... if it doesn't work out, I might try something else, but so far this *really* looks like a great option, though I'm trying to approach it objectively.  Also, just with a quick look at Alpha Five, it doesn't really look like a direction I'd want to go in.  It doesn't seem to support standard database development, but try to make things easier.  I haven't tried it, so I can't say for sure, but the information I see leads me to believe that, and they don't really give a lot in the way of information.  But that's a discussion for a different thread, just to keep this one more useable. :)

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Review of VistaDB
« Reply #4 on: May 24, 2011, 06:30 PM »
For Oracle and MS SQL Server, their footprint is simply massive. The download and installation is again a major thing to deal with.

I had a quick look at the Alpha Five site, and saw this:

Try the  interactive Codeless Ajax examples that ship with v10. (All were created without coding!)

It sounds like it is in a completely different sector, and not really a competitor to VistaDB. It looks more end-user oriented. VistaDB is squarely in the developer camp as a high-end database component. If you look at the tools for it, it's nothing like Access -- the tools are entirely development-centric.

The thing with VistaDB is that you get:

  • Most DB engine functionality in MS SQL Server
  • T-SQL
  • Low footprint
  • Strong SQL types
  • etc.

You also have a clear upgrade path to MS SQL Server!

In some testing for an application I'm writing, I keep logs for some user actions so that they can get the data later. The first INSERTs take about 350 ms or so while it loads and ramps up, with subsequent INSERTs taking 20 ms or something like that. i.e. VistaDB is very fast.

Alpha Five didn't mention anything about using other tools with it, which makes it look like you either develop entirely in A5, or not.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #5 on: May 28, 2011, 03:23 PM »
(Added section for Database Management/Usage in main review above)

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #6 on: May 28, 2011, 05:05 PM »
(Updated with a response on the recursive queries problem)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #7 on: May 28, 2011, 07:35 PM »
wraith, this is something I have been thinking about also.  You mentioned giving MS Access a go, and I'm in that phase right now.  What did you feel was disappointing about Access?

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Review of VistaDB
« Reply #8 on: May 28, 2011, 09:17 PM »
wraith, this is something I have been thinking about also.  You mentioned giving MS Access a go, and I'm in that phase right now.  What did you feel was disappointing about Access?

ARGH! AH! AH! AH! AH! AH!

*whimper* *cry*

Mommy... Tell me the bad man didn't say that... Tell me everything will be ok... Tell me there's no such monster as Access...

*whimper* *cry* *piss pants* *#2 as well* *stare blankly into oblivion*




Ok... Seriously. Access is a great product for what it is designed for and supposed to do. However, it is massively abused, and put in roles that it is not designed for, or capable of supporting.

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

CWuestefeld

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,009
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #9 on: May 28, 2011, 09:25 PM »
This only returned the results from the last row.  No matter how I simplified it (I removed the joins and everything else to make it as simple as possible, and still no go).  On another note, if anyone has any other ways of doing this that don't involve cursors, I'd appreciate the help.

That's because you're selecting into a scalar variable, and then selecting out its (one!) value. Try just this:

Code: Text [Select]
  1. SELECT gt.description
  2. FROM GameTags gt JOIN Game2Tag g2g ON
  3.         gt.keygametag = g2g.keygametag  
  4. WHERE g2g.keygame = 1

Note that you're *not* going to get back a comma-separate list, you're going to get back a datatable. There's no standard SQL way to get that CSV list (and I say that with some authority, it's not just that I don't know how, I'm sure there's no *standard* answer). In SQL Server there's an undocumented feature that will get it (but I avoid such, so I don't recall). There's also a way you can use the XML facility to generate it, but it seems awful heavy-weight for such an operation. Or, of course, the cursor approach that you ruled out. The best answer, I think, is to just get the datatable and let your C# code transform the data as necessary. Use the DB for persistence, and handle presentation (i.e., putting things into pretty lists) higher in your architecture.

EDIT: or, as you mention, you might be able to do it with a recursive CTE, but that's (a) nonstandard SQL, and (B) non-supported here anyway.

For some reason, the table adapter was created, but it was not exposed in the dataset, so I had to hack it.

I can't tell if this is definitely your problem, but I've had a lot of trouble with "quirks" in the DB-related visual designers of Visual Studio. In particular, table mapping have been a problem for me. I frequently need to regenerate the whole thing when all I want is to change a single entry in it.

Anyway, thanks for this review. I've considered VistaDB, so seeing a step-by-step in-depth review is very interesting to me.
« Last Edit: May 28, 2011, 09:29 PM by CWuestefeld »

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #10 on: May 28, 2011, 09:34 PM »
wraith, this is something I have been thinking about also.  You mentioned giving MS Access a go, and I'm in that phase right now.  What did you feel was disappointing about Access?

1. The need for some sort of access runtime
2. The use of SQL that is different from T-SQL, which I am used to
3. The efficiency in large database operations
4. The lack of support for triggers, stored procedures, and functions
5. The lack of ability to use SQL CLR procedures/functions

In short, VistaDB is limiting in some ways next to SQL Server, but Access is limited in almost every way next to SQL Server.  As Renegade says, it's a valid option in the limits of what it's built for.  But building custom applications, I find myself too often limited by it, and I really don't want to do a full scale installation just because I'm using a database.

<snip />

EDIT: or, as you mention, you might be able to do it with a recursive CTE, but that's (a) nonstandard SQL, and (B) non-supported here anyway.

<snip />

Anyway, thanks for this review. I've considered VistaDB, so seeing a step-by-step in-depth review is very interesting to me.
-CWuestefeld (May 28, 2011, 09:25 PM)

Yeah... I use that bit of code (or something similar) to do this in SQL server, so I can do it there, and because this is just *so* close to SQL Server, not having it was a bit more jarring than not seeing that facility in Access, for example.  The penalties of expectation, because everything else so far is *so* good.

And thanks for the suggestions/feedback.  If there's anything in particular you're interested in, let me know!
« Last Edit: May 28, 2011, 09:39 PM by wraith808 »

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #11 on: May 28, 2011, 10:59 PM »
For Oracle and MS SQL Server, their footprint is simply massive. The download and installation is again a major thing to deal with.

You can say that again.I install mostly basic Oracle 10/11 databases (practically all features are turned off), but still you lose 700 Mb of RAM per database. But the "fun" doesn't stop there, because it runs quite some supporting software, losing a total of 900 Mb of RAM.

I don't know about the footprint of MS-SQL, but I don't think that it will be far off as both Oracle and MS-SQL have a big feature-set.

However, it is dead easy to install the XE (express) version of Oracle, it is even configured quite fast and stable right out of the box as well. And its web-interface is quite workable too. So I do not share the notion of databases being hard to install.

Of course, an professional product will only "shine" in professional hands.

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #12 on: May 29, 2011, 10:47 AM »
So I do not share the notion of databases being hard to install.

Hard to install is a pretty relative thing.  If I'm dealing with something that needs the overhead of a client/server architecture, then I'm willing to deal with the headaches that come from supporting unusual events that come from such a complex installation.  But dealing with a desktop application that I'm going to distribute for free/donations/very little, the reduction in hassle that comes from a minimal install involving only an xcopy is pure gold- made even more valuable by the fact that you can bypass even this by embedding it in your application.

Going back again to my wife's school curriculum, I installed it with absolutely no problems.  The first time my wife did it, she had problems, that if it wasn't for me being there, would have resulted in some sort of support time.  After I showed her a couple of things that the installation manual didn't go over, she was able to install the next years with no problem.  But that initial hump would be completely eliminated if the database engine was such a low impact installation that all of this was eliminated.

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #13 on: May 29, 2011, 05:16 PM »
I agree wraith.  This is something I was introduced to last year when i started working with sql databases in a corporate environment.  Thanks for putting into words!  Yes, why can't a database installation be more simple?  Not everyone that works with databases needs a multi gigabyte monster.  We just want a database, some rows and columns...on a very small, personal scale.  This was part of my initial attraction to Infoqube, and still is.  It's a few megabytes, and it allows an individual to do a lot of pretty neat database type things with it, without the headache of the multi-gigabyte installs, the programming languages, the help manuals, the other documentations, the extremely confusing descriptions and vague corporate technical jargon (just tell me what it does!!!!).

Also, somewhat related...I'm not a programmer, but I've noticed that my favorite Delphi programmers write more responsive applications than those who make programs in other frameworks like .net.  So if I were to get into small application building projects (which I may) I would like to use Delphi.  Is there a similar small standalone database utility like VistaDB for Delphi?

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #14 on: May 29, 2011, 05:20 PM »
VistaDB isn't a utility, it's a complete DBMS system.  The part above is more akin to SSMS - I'm getting to the crunchy parts in a bit.  And just like SQL Server Management Studio is just a tool to allow you to manage SQL Server, the tool above is for that purpose.  VistaDB as a database would be able to be used with Delphi (.NET) just as I'm doing now.

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #15 on: May 30, 2011, 11:26 AM »
(Added section for Database Programmability in main review above)

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #16 on: June 04, 2011, 02:19 PM »
(Addition to Step Four: Database Management/Use section, and upgraded rating from 8/10 to 9/10)

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #17 on: June 06, 2011, 08:53 AM »
(Added section for Integration in main review above)

CWuestefeld

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,009
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #18 on: June 06, 2011, 09:00 AM »
Notes: The dataset stuff is annoying, but not insurpassable.  If that is the normal behavior of the dataset generator...

I think you're still running into the VS designer "quirks" that I mentioned in a past post. It's really quite annoying, but I've had to go through the process of rebuilding this stuff many times, while dealing with vanilla SQL Server databases.

Are you planning on doing any work in LINQ? I'm curious how well VistaDB works in that context.

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #19 on: June 06, 2011, 10:09 AM »
Notes: The dataset stuff is annoying, but not insurpassable.  If that is the normal behavior of the dataset generator...

I think you're still running into the VS designer "quirks" that I mentioned in a past post. It's really quite annoying, but I've had to go through the process of rebuilding this stuff many times, while dealing with vanilla SQL Server databases.
Thanks.  That was my initial thought, but I wasn't quite sure.

Are you planning on doing any work in LINQ? I'm curious how well VistaDB works in that context.
I'm planning to do a side project to test performance under load- I'll try to fit some testing of LINQ into that also.

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 11,186
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #20 on: June 15, 2011, 11:11 PM »

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Review of VistaDB
« Reply #21 on: June 16, 2011, 12:05 AM »
Truthfully, when Renegade first brought up VistaDB and talked about how it compared to SQLLite, I was hoping that VistaDB would somehow fall short...

It's pretty much a no-brainer that I'm going to go ahead and buy...

Told you it was good~!  ;D  8)

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,612
    • View Profile
    • Donate to Member
Re: Review of VistaDB
« Reply #22 on: June 16, 2011, 01:32 AM »
Final Rating 9/10 - a buy rating.
You wrote up a fine review wraith, thanks for that. :Thmbsup:

Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Review of VistaDB
« Reply #23 on: June 16, 2011, 02:05 AM »
Final Rating 9/10 - a buy rating.
You wrote up a fine review wraith, thanks for that. :Thmbsup:

Review review: 10/10. :)  :Thmbsup:
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker