Re: LDBC driver

Thomas Kellerer wrote:
Lew, 04.06.2008 03:08:
Where RDBMSes differ the most is in DDL, which is generally outside the scope of the Java program and not such an issue there.

I think where RDBMs differ most, is the handling of concurrency. There is a huge difference between e.g. SQL Server and Postgres when it comes to concurrent read & writes (e.g. readers blocking writers) and that can cause a lot more trouble than problems with the syntax.

The strategies on how to implement things can vary a lot depending on the constraints imposed due to the concurrency handling of the DBMS. We have a unit test that runs certain stream of concurrent transactions, and it simply locks up with MySQL (and I think SQL Server) but runs perfectly with Oracle and Postgres as they generate a lot less locks between readers and writers.

The different concurrency strategies of the RDMBS vendors usually call for different algorithms in order to make the most efficient use of the system. The typical SQL Server way of doing things by putting stuff into a temp table and then operate on a temp table works pretty well there, but would kill an Oracle server. On the other hand, the same process tailored against a Postgres database would simply kill SQL Server because of locks generated during updates.
Those problems cannot be solved with a common SQL "dialect" and are - in my opinion - much more important than the differences in syntax.

Just my 0.02€

Thomas

Agreed. For that reason we advise large-scale users to stay away from MySQL and SQLServer. The generic database handling we have allows us to implement a database-clone action from one provider to the next and we have used that several times to "upscale" users from MySQL or SQLServer to PostgreSQL or Oracle.

We have had to migrate our own ASP platform from MySQL to PostgreSQL when usage levels began to increase.

I would never implement the workarounds you describe for SQLServer in any system if using a different database system is an option in any way. Since our software is a standard system we do not even offer large-scale performance on poorly-scaling database backends.

Silvio
.

Relevant Pages

  • Re: Database architecture and performance considerations
    ... but you should consider turning on the READ_COMMITTED_SNAPSHOT database ... This might improve concurrency. ... He is running SQL Server 2000.... ...
    (microsoft.public.sqlserver.programming)
  • Re: Linq Over Dataset/SQL Server in terms of Performance, Concurrency
    ... We observed that the data storing in SQL Server 2005, ... we are thinking to use In-memory database for achieving ... Concurrency Issues & Memory Management using LINQ ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Database split & referential integrity
    ... > I'm more familiar with this scenario where the back end is on SQL Server, ... > which does not have the concurrency issues you describe. ... Client/server databases can implement independent locking schemes. ... If the first user connects to the Access database file using ...
    (microsoft.public.access.tablesdbdesign)
  • Linking to SQL Server
    ... The database was upgraded to Access ... Does Access 2002 provide good concurrency protection? ... would exporting the database into SQL Server 2000 ...
    (microsoft.public.access.externaldata)
  • Re: good architecture for reusing datasets
    ... implement optimistic or pessimistic concurrency. ... architecture, and database design. ... Get yourself a begineers level book on software engineering and design ... this is where the "reuse" of the datasets could happen). ...
    (microsoft.public.dotnet.framework.adonet)