These aren't really UniDirect questions per se, but it's related, so this seems to be the best forum. I have a ton of cross-database platform related questions...
I'm building a quite large enterprise product (in C#) that will require using a customer provided provided database license on 3 databases (potentially 3 different servers), and need to support:
Oracle
SQL Server
MySQL
Postgres
DB2
I want to build my database layer to be as portable as possible to reduce development effort and maintenance costs. However, this time savings can not trump performance. One of the datastores in particular needs absolutely best possible performance.
I'm leaning against UniDirect for several reasons:
- there appears to be no version that supports latest dotConnect providers
- there appears to be no new version recently at all (11 months)
- there are a lot of posts complaining about performance
From a performance standpoint, is it indeed true that I would be better off using the various dotConnect providers as well?
Is it true that in order to maximize cross-platform portability, I'll be best off avoiding any "customizations" in the pro versions of the various dotConnect products, and in fact if I stick with core System.Data base abstract classes and interfaces, things will in general be much more portable?
Is there a performance difference between dotConnect for SQL Server and dotConnect for Oracle vs. what MS provides natively in the .Net Framework, for the *standard* features? If so which performs better, and under what scenarios?
Using the EF is going to clearly save me time, and I believe it will also increase portability. However, I am concerned about the performance overhead. Can anybody share their experiences here? It may be that for a few of our data stores for fringe functionality we use EF, but for the core datastore that has to scream we use ADO.net providers directly.
I'm still not 100% sold that ODBC wouldn't be my best route. For each of the database vendors listed above, is it indeed true that going with the dotConnect provider will always provide superior performance over using the System.Data.Odbc and just relying on the underlying vendor-provided Odbc diver? Most ADO.Net articles I've read show that it is usually faster than ODBC, but these invariably compare SQL Server only. They also use the "stock" ODBC drivers, and not wire-protocol drivers from 3rd party vendors which have significantly better performance and scalability.
Why is there no DB2 dotConnect product? Am I missing something?!?
Finally, are any/all dotConnect provider implementations "wire protocol" implementations, or do they go throught he client libraries provided by the particular database vendor?
Thanks in advance for answering these questions...
- Rhino
cross-database support: performance vs. dev effort
-
TheRhinoDude
- Posts: 6
- Joined: Thu 30 Apr 2009 22:59
The recent version of UniDirect.NET does not support latest dotConnects yet, but provides a simple way to integrate the old Core Lab providers.
We plan to make a dotConnect-compatible version of UniDirect soon.
The performance of UniDirect.NET is close to the one of dotConnects for it is based on them.
If you use standalone ADO.NET providers you should write your own data access layer (DAL) or use existing implementations.
We can recommend you two ways for solving your problem.
Enterprise Library Data Access Application Block. It is a solution that provides tools for creating data access layers independent from database. More information is available here:
http://msdn.microsoft.com/en-us/library/cc309168.aspx
Entity Framework. Of course, it has the disadvantage of any ORM - it is often slower than the direct ADO.NET code. But there are some articles on its performance tuning, like the following one:
http://blogs.msdn.com/adonet/archive/20 ... art-1.aspx
We have providers with full support for both Data Access Application Block and Entity Framework for Oracle, MySQL and PostgreSQL.
We do not provide the dotConnect for DB2, but IBM offers an ADO.NET driver with Entity Framework support.
dotConnects for Oracle, MySQL and PostgreSQL use the native APIs of these databases ("wire protocol").
dotConnect for Oracle increases the performance significantly comparing to both System.Data.Oracle and ODP.
As for dotConnect for SQL Server, it is built on ADO.NET technology and SqlClient and doesn't increase performance.
We plan to make a dotConnect-compatible version of UniDirect soon.
The performance of UniDirect.NET is close to the one of dotConnects for it is based on them.
If you use standalone ADO.NET providers you should write your own data access layer (DAL) or use existing implementations.
We can recommend you two ways for solving your problem.
Enterprise Library Data Access Application Block. It is a solution that provides tools for creating data access layers independent from database. More information is available here:
http://msdn.microsoft.com/en-us/library/cc309168.aspx
Entity Framework. Of course, it has the disadvantage of any ORM - it is often slower than the direct ADO.NET code. But there are some articles on its performance tuning, like the following one:
http://blogs.msdn.com/adonet/archive/20 ... art-1.aspx
We have providers with full support for both Data Access Application Block and Entity Framework for Oracle, MySQL and PostgreSQL.
We do not provide the dotConnect for DB2, but IBM offers an ADO.NET driver with Entity Framework support.
dotConnects for Oracle, MySQL and PostgreSQL use the native APIs of these databases ("wire protocol").
dotConnect for Oracle increases the performance significantly comparing to both System.Data.Oracle and ODP.
As for dotConnect for SQL Server, it is built on ADO.NET technology and SqlClient and doesn't increase performance.
-
TheRhinoDude
- Posts: 6
- Joined: Thu 30 Apr 2009 22:59