Matisse, db4o or VistaDB

Hi,
I know this is not related to FSQL but since it is related to the topic of database maybe you guys have used one of this products.

I have an ASP.NET application that I need port to a low end computer that is running an HMI on windows XP with 512 MB the application that I need to port is a strip down version is our reporting and recipe management system with MS-SQL 2005. So I am looking for a small factor low system resources consumption Database that I can use instead of MS-SQL 2005 to see if I can improve the performance of the application. It has been difficult since I would like to be able to keep all my sps and SQL statements from SQL. We are an oem so we try to keep the cost down on our standard HMI computer. Our standard for this application is a separete computer with 4GB of RAM with MS-SQL 2005 Workgroup, IIS, FactorySQL, Kepware and the ASP.NET

Do you guys have any experience with this products or any other product

I haven’t used the databases you mentioned. FPMI uses HSQLDB, a lightweight Java DB that probably isn’t relevant for you. FSQL uses an embedded .NET DB whose name escapes me (it can be found under the install directory). I’m sure the guys will be willing to expand on these more.

I take it you have many existing PCs with 512 megs of RAM? What OS are they running and what other applications/services? (Hint run “msconfig” and look under the “startup” tab). What are the requirements of the application (logs x amount of data, realtime, etc, etc)? How many machines do you have and is it possible to upgrade them to a gig of RAM? That may be easier than re-writing your application. Also consider SQL Server or MySQL system settings variable tweaks.

There are many factors to consider when making a decision like that.

[quote=“nathan”]I haven’t used the databases you mentioned. FPMI uses HSQLDB, a lightweight Java DB that probably isn’t relevant for you. FSQL uses an embedded .NET DB whose name escapes me (it can be found under the install directory). I’m sure the guys will be willing to expand on these more.

I take it you have many existing PCs with 512 megs of RAM? What OS are they running and what other applications/services? (Hint run “msconfig” and look under the “startup” tab). What are the requirements of the application (logs x amount of data, realtime, etc, etc)? How many machines do you have and is it possible to upgrade them to a gig of RAM? That may be easier than re-writing your application. Also consider SQL Server or MySQL system settings variable tweaks.

There are many factors to consider when making a decision like that.[/quote]

I may have to do a rewrite of the app. My limitation factor is the computer. The computer hosting the HMI is maxout in RAM it only support 512MB. I look at the FSQL directory and it look if they used firebird but I am not sure since I also see SQLite.

Nathan, thanks for your info.

FactorySQL uses SQLLite, which is probably a great choice for what you're looking for, but...

Having been on the implementation side of many a database conversion project, I can tell you right now that the faster you abandon this idealistic (and unrealistic) hope, the quicker you can move forward. You have two choices as I see it: stick with SQL Server 2005 (bad performance), or re-write your SPs and massage your queries for a more appropriate database.

As an aside, we have noticed here at IA that users of SQL Server are by far the most frequent users of stored procedures. Users of other databases don't use them nearly as much. Is this what they teach on day one of a SQL Server training course, to use as many SPs as possible? Perhaps not coincidentally, the use of SPs tends to lock you into a specific database vendor...

Now for my point of view :slight_smile:

SQLite is terrific, for what it is. And what it is is an extremely lightweight data storage engine that supports a good set of the SQL language. There is fairly good .Net support- you can find a useful .Net connector that actually has the C based SQLite library build in, so everything’s self contained. Long story short though, you’re going to have to change a lot of stuff to more your logic from the stored procedure up to the application. SQLite is simply going to store and retrieve data, not execute any logic.

Now, it looks like Firebird has limited SP support, but like Carl said, vendor lock-in tends to be a significant issue.

For the other ones that you mentioned, I can’t really comment. The VistaDB website looks ok, but I really have no experience with it. You’d probably just have to try and see.

Regards,

Thanks guys, yes I used a lot of SP’s I have around 40 in my app. Why sps? they are suppose to execute faster than inline code or dynamic sql, easy for coding since I don’t that to build all the SQL in code and concatenate all the commands I just pass the parameters to the SPs if I need to change something is the database I don’t have to recompile the code. But I know this is big topic now even with Linq. Maybe I am just lazy :open_mouth: We have plans to move from SQL 2005 to an open source database (Firebird, MySQL or PostgreSQL haven’t decided yet) on our HostPC system ( FSQL, Kepware, MSSQL, and IIS running our ASP.NET) so that we can save some $$$ on every project since we have one of this on every system that requires a HostPC. The HostPC does all the work with FSQL( batch logging and trend logging), the ASP.NET handles all the reporting, trending (charts), recipes management where user can create recipes, edit, download to the PLC via the web using an OPC Web Client control…

Some of our system just have the HMIPC which requires a recipe management system which is an ASP.NET based system. Which is the one I am was trying to replace the MS SQL 2005 express with and embedded database. SQLite came up everywhere on my searches I also stumble on this http://www.versant.com/en_US/products/fastobjects which reads real good.

So back to the database selection for the hostpc Firebird, MySQL or PostgreSQL.
It looks like SQLite is the winner for the HMIPC. Know I want to keep the same application for like I have now for both system for easy code management and updates so I have a big task ahead of me :scratch: writing the application generic with two diff db engines a lot of thinking.

Thanks

Of course, if its time to re-write the application anyhow, might as well do it in FactoryPMI! :mrgreen:

Hey - its our forum, shameless plugs are allowed!

[quote=“Carl.Gould”]Of course, if its time to re-write the application anyhow, might as well do it in FactoryPMI! :mrgreen:

Hey - its our forum, shameless plugs are allowed![/quote]

I will love to try but I cannot do it.

Hey it Looks like Firebird may be the way to go. “Firebird scales from an embedded database up to a full enterprise-class client/server database engine and it does not require any changes at the application level.”

Cool, let us know how it goes. I’ve never personally used Firebird, but I’ve never heard anyone complaign about it either.

I think that’s a good choice- SQLite is a good EMBEDDED database… unfortunately that mean embedded in a program, not just because the overall system is considered “embedded”. What I’m getting at is that although FactorySQL uses SQLite internally, nobody has actually used it to log data to. First thing, it’s not a server. It’s a library. So you can’t just connect over TCP like other database “servers”. So, you have to figure out some way to get around this- either a third party server wrapper for it (which I’m sure exist) or maybe using ODBC.

Firebird is the same situation, it’s just that they have much better support for the server situation out of the box. That’s what they mean about scaling… you can use it like an embedded api, or use the included server daemon to make it accessible to many clients.

For your “HostPC”… I’d recommend MySQL. But then, why can’t you use SQL Server Express on the hostpc? It’s free, and my understanding is that your resource requirements are only on the “HMIPC”.

Regards,

[quote=“Colby.Clegg”]I think that’s a good choice- SQLite is a good EMBEDDED database… unfortunately that mean embedded in a program, not just because the overall system is considered “embedded”. What I’m getting at is that although FactorySQL uses SQLite internally, nobody has actually used it to log data to. First thing, it’s not a server. It’s a library. So you can’t just connect over TCP like other database “servers”. So, you have to figure out some way to get around this- either a third party server wrapper for it (which I’m sure exist) or maybe using ODBC.

Firebird is the same situation, it’s just that they have much better support for the server situation out of the box. That’s what they mean about scaling… you can use it like an embedded api, or use the included server daemon to make it accessible to many clients.

For your “HostPC”… I’d recommend MySQL. But then, why can’t you use SQL Server Express on the hostpc? It’s free, and my understanding is that your resource requirements are only on the “HMIPC”.

Regards,[/quote]

Colby,
We don’t used the SQL Server Express because of the 4GB Database size limitation. After we deploy the system we don’t have any control on the maintenance of the database and we don’t to want to be worry about the size limit. I look again at MySQL during my readings on Firebird there were couple of people who had switch from MySQL to firebird. I know MySql will have more support and a bigger install base than Firebird.

Yes, I think ultimately MySQL is going to be a much better choice than firebird, for the HostPC at least. Much much more support and resources.

Regards,