|
I equally do not want to start a Java vs. RPG thing. I do wish to take just a couple minutes to throw out a couple things that should be considered about Java and JDBC performance. If you've not seen stuff like this before, you might find it shocking... 1) First: Its really hard to measure performance of database APIs in Java. Its a balancing act and almost nothing but your real applications written both ways can give you a valid picture of what you can expect. There's a catch though in that you are likely not going to be able to write your applications both ways. First a warning about performance: - Execute a query in JDBC that returns 10 columns by 100 rows of data. Then fetch all the results as strings (pretend you are writing out the results to an html page in a servlet, a sock in XML format, I don't care. Measure the time it takes to do the execute and measure the time it does to fetch all the data. You will likely find that you spent more time fetching the data than running the query (I expect... I didn't run that tonight). I can tell you for sure that you will spend more time creating the string objects and translating the data from the job ccsid to unicode for those object than you will actually fetching the data from the database by a lot. If you solve this problem with RLA, you are still going to create Strings for the data you return, right? I realize this doesn't say anything of RPG vs. Java specifically... its a point about RLA vs. JDBC. Take it from me, we have considered many performance enhancements that look great (fetch is 50% faster) but don't amount to anything in the real world (creating objects took 10x more time than fetch). Now a couple examples: I point these out because of the mention of orders of magnitude below. Here are a couple things even good JDBC programs on our platform often don't know: 1) Connection pooling and statement pooling - people talk about these and say they are important, but many people don't 'get it'. They are not important... the are critical. Here are a couple numbers I got from a customer this afternoon that was working with a DataSource implementation that supported connection and statement pooling: java UDBDataSourceExample4 Start timing the non-pooling DataSource version... Time spent: 57873 Start timing the pooling version... Time spent: 954 java UDBDataSourceExample5 deploying statement pooling data source Start timing the connection pooling only version... Time spent: 122236 Start timing the statement pooling version... Time spent: 10950 2) If you are doing inserts of large blocks of data to tables (batch updates/blocked inserts), the native JDBC driver provides a mechanism to use the system's blocked insert support. Info on it is in the COMMON presentation that can be downloaded at http://www.as400.ibm.com/developer/jdbc/index.html. Here are the numbers I got from that test: TimeBatchUpdate.java - inserts Java objects Without the new flag: 7.978 seconds With the new flag: .845 seconds (> 9x faster) TimeBatchUpdate2.java - inserts using primitive types Without the new flag: 7.527 seconds With the new flag: .448 seconds (16x faster) 3) Data conversion between ebcdic and unicode is expensive. You should avoid that if you can. In your queries, convert character columns to SQL column with SQL column functions instead of making the JDBC driver do it. Here is an example (again from the presentation): Replace: Select charColumn from tablex with: select graphic(charColumn, <col precision>, 13488) from tablex - graphic 13488 means unicode. This just tells the SQL engine to convert the data to unicode. - its cheaper when its lower in the system. - col precision is how big the column is Test Results: Query Table - 20 columns by 10,000 rows of CHAR data. Standard JDBC query: 3.702 seconds to get all the data SQL optimized query: 1.885 seconds Standard query after table was converted to unicode: 1.436 seconds Most people say that can't step up to storing their data in Unicode. How many people could accept writing queries like the example above and getting most of the benefit (a 2x improvement)? Its very often the case that getting someone's JDBC to perform an order of magnitude better is easy. You see, without a careful understanding of exactly what you are trying to test and how you are going about it, an order of magnitude is nothing. I've only talked about a couple of the big hitters here. Now, stop using column names in your ResultSet.getXXX calls and save 25% on them. You say that is bad for reuse but come, on... you don't have that support in other APIs, right? Only return columns in your queries that you intend to use in the app (avoid select *). Get rid of those Packed(5,0) columns and replace them with primitive data times (int and bigint). Now, if you still are not where you want to be with performance, lets start multi-threading the application to more effectively do work in parallel (most people really only use threads to separate users, not to do work of a user more efficiently). I still contend (and again... I'm only try to help people understand Java... not attack any other technology) most application types can be competitively written in Java. You just have to think about the problem domain right. You pay a price on some fronts to use Java. There are few people out there that wouldn't awknowledge that. If RPG is right for you, you should definitely use it. But Java has a great many obvious benefits. We here in iSeries-land have benefited far more than most because of Java. First, we have a beautiful system architecture that allows us to write a JVM that other companies just can't touch for performance. Far more important is the software opportunities that Java opens up for our platform. A couple years ago, it was always a struggle as the software division split their time over what amountted to competing IBM platforms. You might be surprised to hear this but we here working on the iSeries rarely got the lion's share of attention from them. Not to mention the strategic directions so many companies are taking are opened up to us. Most ERP vendors are going to Java in big ways. Its far easier for us to support them. Do you think we could have a project the size and scale of WebSphere and all is components on iSeries if we had to develop the product in any other language? Do you think we could integrate and have access to the rich power of all the open source frameworks (see jakarta projects website) and tools (JDOM, XML parsers, XML and Text extenders, etc) and applications without Java? I know Java doesn't solve all the problems in the world, but we are lucky indeed as iSeries developers and users that Java showed up when it did. That's all just my two cents worth. Thanks for your time. I hope you found some interesting nuggets of information here. Richard D. Dettinger AS/400 Java Data Access Team Democracy's enemies have always underestimated the courage of the American people. It was true at Concord Bridge. It was true at Pearl Harbor. And it was true today. Rochester Post-Bulletin Tuesday September 11, 2001 |---------+----------------------------> | | "Raikov, Lo" | | | <Lo.Raikov@MISYS.| | | COM> | | | Sent by: | | | java400-l-admin@m| | | idrange.com | | | | | | | | | 01/17/2002 09:03 | | | PM | | | Please respond to| | | java400-l | | | | |---------+----------------------------> >----------------------------------------------------------------------------------------------------------------------------| | | | To: "'java400-l@midrange.com'" <java400-l@midrange.com> | | cc: | | Subject: RE: database access - performance | | | | | >----------------------------------------------------------------------------------------------------------------------------| The issue of the performance difference between RLA and JDBC is somewhat similar to the how-long-is-a-piece-of-string discussion. I think one would be lucky to develop a pure AS/400 static SQL OLTP application that would be less than 70% slower than its RLA counterpart (I 'm talking of transaction input only - batch is an altogether different story). JDBC with all its connection pooling and caching is at least 50% slower than static SQL. Now, how significant is all this? It depends. I saw an article quite recently (was it in iSeries News?) on what IBM declared a very positive Java/400 benchmark. Of course benchmark results are always nothing but misleading, but I remember a figure of 1.5M small business transactions per hour (the HW was 840 24-way). Judging by the brief description those were really very small commercial transactions. 1.5M per hour seems quite a lot, but a few months ago I was involved in an RLA benchmark achieving 11M not so simple business transactions per hour. And this benchmark decided a huge bid! Certainly, there is nothing truly scientific about the above, but still... Please don't get me wrong, I would hate to turn this subject into another "what's better, Java or RPG" discussion, but there are situations when the difference in the order of magnitude is important. My advice would be to use RPG stored procedures for data access where possible. If you keep them simple, porting them to another platform is not going to be a big deal. Lo -----Original Message----- From: Dave Wall [mailto:dawall@us.ibm.com] Sent: 18 ?????? 2002 ?. 13:34 To: java400-l@midrange.com Subject: Re: database access - performance For what it is worth I would use JDBC. The performance difference is not as big as it used to be. Work is on going on both the client and server side to make it faster. Little effort is going toward RLA. JDBC/SQL is a standard so what you learn you can apply to other platforms. It is easier to write bad SQL code but with some experience you can make your SQL implementation almost as fast as RLA. David Wall 553-5329 AS/400 Toolbox for Java Michael Knezevic <m.knezevic@porta To: "Java/400 (E-Mail)" <java400-l@midrange.com> .de> cc: Sent by: Subject: database access - performance java400-l-admin@m idrange.com 01/17/02 10:01 AM Please respond to java400-l hello, i try myself on my first real programm for the as400. now i'm thinking about how to best access the files. what method has the best performance: record-level-access or jdbc (data access through resultset)? thanx _______________________________________________ This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) mailing list To post a message email: JAVA400-L@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l or email: JAVA400-L-request@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/java400-l. _______________________________________________ This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) mailing list To post a message email: JAVA400-L@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l or email: JAVA400-L-request@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/java400-l. ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ _______________________________________________ This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) mailing list To post a message email: JAVA400-L@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l or email: JAVA400-L-request@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/java400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
