Java Open Client - Mapping temp-table to java.sql.ResultSet

Hi Shashi,

With regards to your questions.

1. Will Open Client run slower than JDBC?
   
   
   I can't think of a scenario under which the Open Client would be faster than JDBC for data retrieval.  Our JDBC driver is optimized for data retrieval.  Our AppServer assumes you have some business logic which is filtering and processing the records.  If all you are going to do is query the database and return those records, then most likely using the JDBC access mechanism is the way to go.  Of course, there are good reason to go to the AppServer even for data retrieval as long as performance is acceptable due to things like security concerns, consistency of architecture, etc., and many of our customers had made that trade-off for their environments based on their particular requirements.
   
   
2. Why isn't there a significant improvement in performance for the second run for the State-Free Open Client program?
   
   Well without looking at your test procedures, it's hard to say if there is a way to get a more significant improvement on the second run.  Certainly, on the 2nd run the data and the r-code is cached (somewhat depending on your test procedures).  However, you are still paying the cost of:
   
   
   • 2 context switches: one from the client to the broker, and one from the broker to the AppServer agent,
     
   • copying the data from the database to the temp table, and from the temp table to the wire format, and
     
   • the cost of connecting from the Open Client to the AppServer.
     
     

Also, keep in mind when comparing the JDBC driver mechanism to the AppServer Open Client mechanism although they both return SQL resultsets, they are totally different implementations with very different design centers.  For example, with the AppServer we have to deal with implmentation issues like returning multiple SQL resultsets on a single connection, etc.

Ken

Ken,

Thank you for replying to my queries. It was very informative. I now understand that for data retreival JDBC would be faster than the Open Client architecture. I have later discovered that there could be exceptions. For example,consider the following two tables.

photoset {photosetnum, title}

photo {photonum, photosetnum, filename}

Now, if you want to retreive all the photosets along with number of photos per photoset. Since, OpenEdge supports only SQL-92, we would need one SQL query to retrieve all the photosets. And another SQL query that needs to be executed for each photoset, which retreives the number of photos in that photoset. Given that if there are N photosets, we will need to execute N+1 queries. In this scenario, I found that the Open Client comfortably out performs AppServer.

Also thanks for letting me know about the overhead involved even during the second execution of the Open Client program. I now understand why there isn't significant performance improvement during the second run.

There was one question that was left unanswered. I am hoping that someone can answer it:

The Java Open Client manual says that mapping temp-tables to SQL ResultSets is provided for backward compatibility. Does that mean this feature would not be supported in future? (Currently mapping temp-tables and datasets to ProDataGraph is the standard. Understandably, it performs poorly when compared to SQL ResultSets).

Thanks for your answers Ken.

Regards,

Shashi

Sashi,

I think your example is a great example of where Open Client will be better.  In this case, by using the Open Client you can run several queries on the server, and do other filtering.  This significantly reduces the number of records returned to the client.  This is exactly the kind of scenario where I would expect Open Client to out perform straight JDBC.

Relative to support for SQL resultsets within the Open Client for Java feature, we have no plans to remove that feature.  I think the intent of the statement was to indicate that from a programmatic point of view SDO's are preferable because they fit better with the RPC style of programming that the Open Client supports.  There are some (possibly non-obvious) limitations with using SQL Resultsets with Open Client.

For example, for performance reasons we stream the SQL resultsets across the AppSever connection in an asynchronous fashion.  They are not cached on the client.  When the RPC call to the AppServer completes, the SQL resultset object is created, but no data is actually fetched.  The net affect is, that if you return multiple resultsets from an single AppServer request, you much fetch and close each one in the order they are returned in the parameter list.

SDO's are cached on the client, and returned synchronously. When the RPC call to the AppServer finishes, the SDO is completely filled in and cached on the client.  As this fits in better with an RPC style interface, I believe this is the behavior most would prefer even if there are some negative performance implications.

Ken

Ken,

Glad to know that support for SQL ResultSets won't be taken away. I understand how returning ProDataSets is programatically aesthetic. But the performance overhead might mean that a lot of people would probably stick with SQL ResultSets.

Many thanks for your reply!

Regards,

Shashi

Sashi,

I don't have any statistical data to back it up, but my guess based on talking to lots and lots of customers is due to the extreme popularity of Prodatasets it would be rare to file someone using SQL ResultSets.  The ones that would be using it are those applications that will built with V9 before we had Prodatasets which have not been updated, and maybe a handful of cases for issues like what you are raising here.

In retrospect I should note that our design center for Prodatasets is not the example that your give in your original message.  Typically, people are not using to pass a simple table with lots of data.  Rather they are sending moderate amounts of data spread across multiple tables with lots of columns with relationship between those tables that they want to preserve, e.g. Customer contains Orders contains Orderlines, etc., and where that data will be updated on the client and sent back to the server. 

Now there are a broad spectrum  use cases where Prodatasets are actually used, but the primary use case is something close to what I describe above, and I suspect the relative performance profile will look different for that type of scenario.

Let me know if you have anymore questions.

Ken

Ken

While I think it is very clever for PSC to have provided these automated interfaces between ProDataSets or temp-tables and native structures in Java or C#, another approach you might want to consider is marshalling and demarshalling to XML.  It might take you a little extra work to provide the code up front, but it means you have complete control over what is in the transmission and the transmission is technology neutral, i.e., you can elect to change either end of the pipeline without having to change the other.  E.g., suppost that which is currently being consumed by Java is something that you find a use for a browser-based WUI application next year.  With the data in XML, the server side can remain unchanged and service both types of clients.

I'd rather use the specialized interfaces and go with passing the ProDataset accross the wire.

At least for the .NET proxy there is a performance benefit in using the native ProDataset transmission over XML serialization and I have no doubt that it will be similar with with the Java proxy.

Also I would never consider to use XML data in the middle of business logic - it should always be a matter of an interface on top of the business services. And I see no problen in using a different or additional interface layer on top of a ProDataset based API and to perform the custom XML serialization for those consumers that do require it.

Have you tested the performance?  Greg Higgins earlier tests gave the performance benefit to XML, even counting the time for serialization.

And, who said anything about using it in the middle ... client to server is a natural boundary and one where modern design encourages technology neutral solutions.  Not just a browser, but a Sonic interface would also mandate XML.  Why paint yourself into a corner?

tamhas schrieb:
Have you tested the performance?  Greg Higgins earlier tests gave the performance benefit to XML, even counting the time for serialization.

Tested performance and counted bytes on the wire. Not in the latest version of OE, probably some 10.0 release.

Its a test that I would like to see done systematically and completely for all options and for different types of datasets.  No reason it couldn't be set up so that it was easily repeated on new versions.  Greg's, as I recall was testing ABL to ABL and I think round trip.  Come to that, I haven't even seen any comparision between WRITE-XML and hand-written SAX.

And, of course, there is the question of how much difference ... if it is only a small edge, i.e., network congestion would be a bigger factor than the difference, then I think it would be hard not to argue for the technology-neutral solution as being more future proof.

Relative to the performance number when comparing sending Prodatsets in native format to sending the equivalent data in XML, since Prodatasets are sent in a binary and native to OE format they should be much faster, and the XML may be even 1 to 2 orders of magnitude larger depending on the nature of the data.  If you or someone has performance data that shows otherwise, I would love to see it.

In terms of looking for a technology neutral approach, we have programmatic mechanisms for converting Prodatasets to XML, and automate the conversion when exposing Prodatasets to Web Services or Sonic.

Architecturally, there is no reason you can't have expose Prodatasets as parameters on an AppServer, and have those same Prodatasets converted to Ado.Net datasets when using the Open Client for .NET, SDOs when using Java, and XML when using Web Services on the OpenEdge Adapter for Sonic ESB.

Ken

I understand that the performance data from Greg is surprising.  It certainly wasn't intuititive that one could marshal to XML, transmit what would seem like bulky XML, and then de-marshal the XML at the other end faster than sending the temp-table ... but those were his results.  Apparently, the testing was done for a specific customer and it isn't something he can publish in any detail.  And, he is quick to point out that it was not an extensive test with varying levels of size and complexity in the transmitted object.

I wish someone would do a really exhaustive test so that we would have some real data.

Understood that PSC has done some good work in largely transparent conversion of forms, but there is still an aspect that bothers me about the server even knowing the technology of the client or recipient.  Without that, how does it know what to send back?

Plus, there seem to have been a number of posts over the years about compatability issues among the various kinds of data objects ... not surprising, really.  XML provides a form acceptable to any consumer ... as demonstrated by Sonic.