Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #161 > unrolled thread

Re: LDBC driver

Back to article view | Back to comp.lang.java.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: LDBC driver "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: LDBC driver "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: LDBC driver "Thomas Kellerer" <thomas.kellerer@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
      Re: LDBC driver "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
        Re: LDBC driver "Thomas Kellerer" <thomas.kellerer@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
          Re: LDBC driver "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
        Re: LDBC driver "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
          Re: LDBC driver "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

#161 — Re: LDBC driver

  To: comp.lang.java.databases
blueparty wrote:
> http://ldbc.sourceforge.net/
> 
> Any experiences, opinions, anything to share ?

I don't see the advantage over JDBC.

They say that with JDBC,
> # The SQL is different for each databases
> # Every JDBC driver behaves differently
> # Changes are required for each database
> # No time to study all vendor documentation
> # No time to test with all databases

However, with JDBC, and especially with JPA, much of the difference between 
vendors is already abstracted.  Also, most products are designed to work with 
a single database engine; not as many are meant to plug into virtually any 
RDBMS, and those tend to be written with a generic SQL dialect in the first place.

Where RDBMSes differ the most is in DDL, which is generally outside the scope 
of the Java program and not such an issue there.

With modern data layers like Hibernate and other JPA solutions, the 
differences the program does care about are moved off into deployment 
descriptors.  These guys achieve their "independence" by using the same 
restricted dialect of SQL as you'd use without LDBC if you were to try to be 
portable.

I can see a niche for it in those few products that are meant to plug into any 
database, maybe.  But for my own work, no thank you.  I'll stick with the 
standard approaches.  Last thing I need in my alphabet soup is yet one more 
layer to go wrong on top of the gazillion layers of abstraction already in place.

-- 
Lew

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#162

  To: comp.lang.java.databases
Lew wrote:
> blueparty wrote:
>> http://ldbc.sourceforge.net/
>>
>> Any experiences, opinions, anything to share ?
> 
> I don't see the advantage over JDBC.
> 
> They say that with JDBC,
>> # The SQL is different for each databases
>> # Every JDBC driver behaves differently
>> # Changes are required for each database
>> # No time to study all vendor documentation
>> # No time to test with all databases
> 
> However, with JDBC, and especially with JPA, much of the difference 
> between vendors is already abstracted.  Also, most products are designed 
> to work with a single database engine; not as many are meant to plug 
> into virtually any RDBMS, and those tend to be written with a generic 
> SQL dialect in the first place.
> 
> Where RDBMSes differ the most is in DDL, which is generally outside the 
> scope of the Java program and not such an issue there.
> 
> With modern data layers like Hibernate and other JPA solutions, the 
> differences the program does care about are moved off into deployment 
> descriptors.  These guys achieve their "independence" by using the same 
> restricted dialect of SQL as you'd use without LDBC if you were to try 
> to be portable.
> 
> I can see a niche for it in those few products that are meant to plug 
> into any database, maybe.  But for my own work, no thank you.  I'll 
> stick with the standard approaches.  Last thing I need in my alphabet 
> soup is yet one more layer to go wrong on top of the gazillion layers of 
> abstraction already in place.
> 

I totally agree. My company does supply a system that runs on most major 
databases (we use PostgreSQL ourselves on our ASP platform but we have 
customers with private installations using Oracle, SQLServer and MySQL) 
and as Lew said the relevant differences are in the DDL area mostly.

One extra problem we had to solve was storing UUID values. Some 
databases have direct support for this data type but the JDBC drivers 
expose them differently. Some don't support UUIDs directly and in those 
cases we revert to a binary or plain text representation.

We have solved the differences at the DDL level by generating different 
database create/drop/etc. scripts using vendor specific XSLT 
transformations on a generic XML database descriptor.

The PreparedStatement-parameter and ResultSet-getXXX differences we have 
solved by creating a set of JDBC wrappers for the Connection, 
PreparedStatement and ResultSet classes that add extra methods like 
setUUID(...) and getUUID.
Since we already used such wrappers in combination with our connection 
pool to handle automatic closing of resultsets and statements when 
connections are returned to the pool (and a facility that allows 
specification of -1 as the parameter-index on a PreparedStatement and 
have it do the counting itself) this was a small modification even as an 
afterthought.

Silvio

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#163

  To: comp.lang.java.databases
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.02re4

Thomas

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#164

  To: comp.lang.java.databases
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.02re4
> 
> 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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#165

  To: comp.lang.java.databases
Silvio Bierman, 04.06.2008 11:07:
> 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.

Completely agreed!

Although I have the impression that a SQL Server user wouldn't call the 
concept of using temp tables a workaround but rather a design principle ;)

But then I was lucky to not have to use SQL Server a lot, so I might be 
mistaken with that assumption.

Thomas

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#167

  To: comp.lang.java.databases
Thomas Kellerer wrote:
> Silvio Bierman, 04.06.2008 11:07:
>> 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.
> 
> Completely agreed!
> 
> Although I have the impression that a SQL Server user wouldn't call the 
> concept of using temp tables a workaround but rather a design principle ;)
> 
> But then I was lucky to not have to use SQL Server a lot, so I might be 
> mistaken with that assumption.
> 
> Thomas

I have used a lot of SQLServer in the past. We ran MySQL on the ASP 
environment (Linux boxes) and SQLServer on our development machines 
(laptops running XP).

Some of our larger customers insisted on running the system on their own 
Windows servers using SQLServer as well and we let them. As the 
databases and user bases grew they suffered steadily degrading 
performance for larger data imports and extractions.
In the meantime we had switched both our ASP environment and our 
development machines to PostgreSQL. On the same database content stuff 
that would take 30 minutes on their SQLServer setups (due to lock 
contention) would run in 30-45 seconds on my not-so-up-to-date laptop. 
That brought them around quite quickly. PG did the same job in about 15s 
on their Windows servers.


Silvio

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#169

  To: comp.lang.java.databases
Silvio Bierman wrote:
> 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.

Interestingly, Silvio, your company's product is exactly the use case that 
would ostensibly benefit from something like the LDBC driver, if it didn't 
already have equivalent code in place.

OTOH, from your description it sounds like that level of abstraction was not 
at all difficult to achieve for you.

I didn't see anything in the LDBC site that would account for the sort of 
algorithmic adjustments highlighted by Thomas.  I suspect that that type of 
thing will always require a programmer / analyst to pay attention case by case.

That's why they pay us the big bucks / dinero.

-- 
Lew

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#170

  To: comp.lang.java.databases
Lew wrote:
> Silvio Bierman wrote:
>> 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.
> 
> Interestingly, Silvio, your company's product is exactly the use case 
> that would ostensibly benefit from something like the LDBC driver, if it 
> didn't already have equivalent code in place.
> 
> OTOH, from your description it sounds like that level of abstraction was 
> not at all difficult to achieve for you.
> 
> I didn't see anything in the LDBC site that would account for the sort 
> of algorithmic adjustments highlighted by Thomas.  I suspect that that 
> type of thing will always require a programmer / analyst to pay 
> attention case by case.
> 
> That's why they pay us the big bucks / dinero.
> 

In a way that is true. However, putting a similar abstraction in place 
is a minor effort. That, and the fact that this can be more easily 
tailored to the needs of the application and the surroundings it will 
run in than an external tool which might even bring unknown surprises 
with it makes me prefer the self-written code in this case.

To be honest this is a decision we often make in retrospect as well.

We use a reverse proxy for load-balancing purposes on the ASP platform 
and started with Pound, then moved to yxorp but needed some facilities 
both could not provide and finally decided to code a reverse proxy 
servlet ourselves. This proved quite easy (took me about three hours).
It has every facility we need, performs much better and is pure Java 
(the other ones are written in C and did not run on Windows).

We used JFreeChart for generating graphs in the report-generation part 
of our software. Users kept beating us around for tiny details (and some 
major logic issues) they would like to see changed but where hardly or 
at all feasible with JFreeChart.
Finally (after over two years of hassle) I decided to make our own. Took 
quite some effort (about two weeks of development) but now we have 
something that generates better looking output and that we can control 
at the sub-pixel level.

I must say that we are almost solely working on the standard product we 
provide and support. That makes the development cycle a lot different 
from developing custom products from the bottom up each time. We do that 
as well (be it less and less) and in those cases we do make different 
decisions.

I guess it all depends...

Silvio

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web