Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.java.programmer > #35915 > unrolled thread
| Started by | Knute Johnson <nospam@rabbitbrush.frazmtn.com> |
|---|---|
| First post | 2016-11-21 11:06 -0600 |
| Last post | 2016-11-27 16:42 -0800 |
| Articles | 20 on this page of 92 — 13 participants |
Back to article view | Back to comp.lang.java.programmer
Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-21 11:06 -0600
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-11-21 19:48 +0000
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-21 12:33 -0800
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-21 18:23 -0600
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-21 21:22 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-21 21:25 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-21 22:29 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-21 22:41 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-22 10:51 -0500
Re: Scaling java app a.laforgia@gmail.com - 2016-11-23 04:58 -0800
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-23 05:18 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 09:52 -0500
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-23 07:51 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 09:46 -0500
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-23 08:07 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 12:10 -0500
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-23 10:39 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 14:57 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-23 15:04 -0500
Re: Scaling java app a.laforgia@gmail.com - 2016-11-24 02:01 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-24 09:10 -0500
Re: Scaling java app a.laforgia@gmail.com - 2016-11-25 03:48 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-25 09:58 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-25 10:22 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-25 10:34 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-26 00:01 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-26 09:05 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-26 09:52 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-21 19:46 -0500
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-21 21:12 -0600
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-21 22:44 -0500
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-22 09:56 -0600
Re: Scaling java app Silvio <silvio@internet.com> - 2016-11-22 20:07 +0100
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-22 18:00 -0600
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-22 19:35 -0600
Re: Scaling java app Silvio <silvio@internet.com> - 2016-11-24 00:51 +0100
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 10:26 -0500
Re: Scaling java app Eric Sosman <esosman@comcast-dot-net.invalid> - 2016-11-23 15:54 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 20:46 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 12:00 -0500
Re: Scaling java app Knute Johnson <nospam@rabbitbrush.frazmtn.com> - 2016-11-23 19:53 -0600
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 21:21 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-23 21:34 -0500
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-28 05:06 -0800
Re: Scaling java app Richard Maher <maher_rjSPAMLESS@hotmail.com> - 2016-11-24 20:45 +0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-24 09:12 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-25 10:32 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-26 00:05 -0500
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-11-26 15:24 +0000
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-11-28 05:08 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-28 19:51 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-28 21:18 -0500
Re: Scaling java app lewbloch@gmail.com - 2016-11-29 13:50 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-11-29 16:53 -0500
Re: Scaling java app lewbloch@gmail.com - 2016-12-01 13:27 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-01 16:45 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-01 22:07 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 10:35 -0500
Re: Scaling java app Gene Wirchenko <genew@telus.net> - 2016-12-02 08:53 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 15:05 -0500
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-12-02 20:25 +0000
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 16:20 -0500
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-12-02 18:33 +0000
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 15:16 -0500
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-12-02 22:57 +0000
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 19:25 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:18 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 22:28 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:36 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-03 11:27 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:05 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 22:30 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:41 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-03 11:29 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:00 -0500
Re: Scaling java app lewbloch@gmail.com - 2016-12-02 11:40 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 15:17 -0500
Re: Scaling java app lewbloch@gmail.com - 2016-12-02 16:26 -0800
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 20:46 -0500
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-12-02 12:44 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 21:57 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 22:40 -0500
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 22:51 -0500
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-03 11:42 -0500
Re: Scaling java app Andreas Leitgeb <avl@logic.at> - 2016-12-02 15:43 +0000
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-12-02 21:19 -0500
Re: Scaling java app Martin Gregorie <martin@address-in-sig.invalid> - 2016-12-02 10:28 +0000
Re: Scaling java app Jerry Stuckle <jstucklex@attglobal.net> - 2016-12-02 10:37 -0500
Re: Scaling java app Gene Wirchenko <genew@telus.net> - 2016-12-02 09:00 -0800
Re: Scaling java app Eric Douglas <e.d.programmer@gmail.com> - 2016-12-01 13:50 -0800
Re: Scaling java app Arne Vajhøj <arne@vajhoej.dk> - 2016-11-25 10:30 -0500
Re: Scaling java app Roedy Green <inquiry@mindprod.com> - 2016-11-27 16:42 -0800
Page 4 of 5 — ← Prev page 1 2 3 [4] 5 Next page →
| From | Martin Gregorie <martin@address-in-sig.invalid> |
|---|---|
| Date | 2016-12-02 20:25 +0000 |
| Message-ID | <o1slbm$dft$1@dont-email.me> |
| In reply to | #36025 |
On Fri, 02 Dec 2016 15:05:30 -0500, Jerry Stuckle wrote: > RUNSTATS updates the current statistics on the database, and REBIND > rebinds all applications, creating new access plans for each. > You've got to be kidding! RUNSTATS is fine, but old fashioned: but something like REBIND was outdated by the mid 1990s: Even Sybase 10 didn't need any application rebinding malarky. By contrast PostgreSQL does all that invisibly and automatically and has been that way since I started using it around 2005. It updates statistics and tidies indexes periodically via internal threads. Execution plans are set up the first time a program runs each query, so are always current. -- martin@ | Martin Gregorie gregorie. | Essex, UK org |
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 16:20 -0500 |
| Message-ID | <o1sog7$uhb$1@jstuckle.eternal-september.org> |
| In reply to | #36028 |
On 12/2/2016 3:25 PM, Martin Gregorie wrote: > On Fri, 02 Dec 2016 15:05:30 -0500, Jerry Stuckle wrote: > >> RUNSTATS updates the current statistics on the database, and REBIND >> rebinds all applications, creating new access plans for each. >> > You've got to be kidding! > > RUNSTATS is fine, but old fashioned: but something like REBIND was > outdated by the mid 1990s: Even Sybase 10 didn't need any application > rebinding malarky. > > By contrast PostgreSQL does all that invisibly and automatically and has > been that way since I started using it around 2005. It updates statistics > and tidies indexes periodically via internal threads. Execution plans are > set up the first time a program runs each query, so are always current. > > No, I'm not kidding. Access plans can change as data is inserted and deleted, and when the database is reorganized. RUNSTATS updates statistics on the database, and REBIND ensures the most efficient means of accessing the data is being used. And it doesn't have to be unnecessarily created the first time a program runs - only when the program is compiled and the access plans rebound. These operations do not need to be performed very often, and continually updating them unnecessarily is just an additional load on the computer. These operations are generally run on databases during light load times. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Martin Gregorie <martin@address-in-sig.invalid> |
|---|---|
| Date | 2016-12-02 18:33 +0000 |
| Message-ID | <o1sepg$jt7$1@dont-email.me> |
| In reply to | #36017 |
On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: > And exactly how does Java interface efficiently with databases like DB2? > Java only has the equivalent of dynamic SQL, where every SQL statement > must be interpreted by the database. Additionally, the connection to > the database is relatively slow. > Java usually uses JDBC. > COBOL has libraries which interface directly with the database, and, > using DB2 as an example (only because it's the one I'm most familiar > with), there is a preprocessor which interprets the SQL statements > *once* at compile time, and stores the resulting access plan in the > database. This saves significant time interpreting the SQL and building > the access plan. > That depends on which compiler and OS you're running the COBOL program on: - I've seen and used the preprocessor used with IDMSX, which converted IDMSX data access statements into COBOL which were compiled by the same compiler that was used for the calling program, and am not surprised that IBM mainframes use a similar process. - DEC VAXen used their own database interface definition language to generate an interface module that was linked to the program using the database, which might be written in COBOL among other languages. - I think all compiled languages I've needed to talk to an RDBMS on *NIX operating systems used the ODBC interface - its efficiency depends entirely on the capabilities of 'ODBC driver', i.e. the DB-specific part of ODBC that's typically supplied by the DBMS vendor/author. - Java generally uses JDBC, which is closely modelled on ODBC and, like it, the calling efficiency depends on the DB-specific 'JDBC driver' module. Personally, I favour the ODBC/JDBC approach because: - once a developer knows how to use it they know how to access any DBMS that supports ODBC/JDBC - IME the drivers I've seen have been quite efficient, didn't interfere with DBMS query optimisation, and have often allowed access to DBMS stored procedures - possibly the biggest performance enhancement of the lot. -- martin@ | Martin Gregorie gregorie. | Essex, UK org |
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 15:16 -0500 |
| Message-ID | <o1skn9$fqi$1@jstuckle.eternal-september.org> |
| In reply to | #36023 |
On 12/2/2016 1:33 PM, Martin Gregorie wrote: > On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: > >> And exactly how does Java interface efficiently with databases like DB2? >> Java only has the equivalent of dynamic SQL, where every SQL statement >> must be interpreted by the database. Additionally, the connection to >> the database is relatively slow. >> > Java usually uses JDBC. > Yes, which is all dynamic SQL. It also has an extra layer of code between the application and the database-specific libraries. Something COBOL doesn't have. >> COBOL has libraries which interface directly with the database, and, >> using DB2 as an example (only because it's the one I'm most familiar >> with), there is a preprocessor which interprets the SQL statements >> *once* at compile time, and stores the resulting access plan in the >> database. This saves significant time interpreting the SQL and building >> the access plan. >> > That depends on which compiler and OS you're running the COBOL program on: > > - I've seen and used the preprocessor used with IDMSX, which converted > IDMSX data access statements into COBOL which were compiled by the same > compiler that was used for the calling program, and am not surprised that > IBM mainframes use a similar process. > IBM mainframes use the preprocessor which comes with DB2 and compile with any standards-compliant COBOL compiler. > - DEC VAXen used their own database interface definition language to > generate an interface module that was linked to the program using the > database, which might be written in COBOL among other languages. > > - I think all compiled languages I've needed to talk to an RDBMS on *NIX > operating systems used the ODBC interface - its efficiency depends > entirely on the capabilities of 'ODBC driver', i.e. the DB-specific part > of ODBC that's typically supplied by the DBMS vendor/author. > Once again, ODBC adds another layer between the application and the database, slowing access. > - Java generally uses JDBC, which is closely modelled on ODBC and, like > it, the calling efficiency depends on the DB-specific 'JDBC driver' > module. > > Personally, I favour the ODBC/JDBC approach because: > > - once a developer knows how to use it they know how to access any DBMS > that supports ODBC/JDBC > But you also are limited by the least capable database. If one database doesn't support it, then ODBC/JDBC doesn't support it. And performance isn't as critical to you as it is on mainframes. If you had to spend $10-20M (or more) to upgrade your system because it's running out of steam, you'd be more concerned about performance. > - IME the drivers I've seen have been quite efficient, didn't interfere > with DBMS query optimisation, and have often allowed access to DBMS > stored procedures - possibly the biggest performance enhancement of > the lot. > > There is no query optimization of static SQL at runtime. It's all done when the access plan is bound to the database (part of the application build process). Static SQL performs better than stored procedures for single SQL statements because the SP doesn't have to be loaded. They are still quite useful, though, where you need the same logic (more than just a single SQL statement) repeated from multiple applications. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Martin Gregorie <martin@address-in-sig.invalid> |
|---|---|
| Date | 2016-12-02 22:57 +0000 |
| Message-ID | <o1su8q$5v6$1@dont-email.me> |
| In reply to | #36026 |
On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: > On 12/2/2016 1:33 PM, Martin Gregorie wrote: > Yes, which is all dynamic SQL. It also has an extra layer of code > between the application and the database-specific libraries. Something > COBOL doesn't have. > Not necessarily: in a modern RDBMS the optimisation layer is inside the RDBMS, so the ODBC driver may be little more than an API translator and, in some cases a field content reformatter. Pushing the optimisation up the the REBIND level where schema changes require the interface code to be recompiled is just primitive. Yes, I had to do that with IDMS, but that predated relational databases. No RDBMS I've used has ever required that. Last but not least, if you use prepared SQL statements you guarantee that the statement has its optimised strategy calculated and stored when the prepared statement is created (usually during program initialisation) and so the per-query overheads incurred when your queries are submitted as a string containing SQL syntax plus the data values vanish: the prepared query was optimised once at declaration and can be reused many times without further optimisation. Finally, prepared statements provide protection against SQL exploits because the prepared SQL statement only contains place-holders for data values. This means that spurious statement terminators and other attempts to inject injurious SQL statements will be treated purely as data: the prepared statement may reject the 'value strings' given to it but can't and won't try to interpret them as SQL commands. > IBM mainframes use the preprocessor which comes with DB2 and compile > with any standards-compliant COBOL compiler. > Yep, expected. So did IDMS and IMS. >> Personally, I favour the ODBC/JDBC approach because: >> >> - once a developer knows how to use it they know how to access any DBMS >> that supports ODBC/JDBC >> >> > But you also are limited by the least capable database. If one database > doesn't support it, then ODBC/JDBC doesn't support it. > I don't think you understand ODBC or have looked at the list of supported databases. ODBC and JDBC (which can also use ODBC DB drivers) support almost any database you're likely to use as well as a selection of 4GL filing systems and indexed files - if anybody has written and published a driver for the DB or filing system it is supported: you can download it and its documentation and used it to access its target data store. > There is no query optimization of static SQL at runtime. It's all done > when the access plan is bound to the database (part of the application > build process). > Quite wrong. See 'prepared statements' above. OTOH if you're prepared to tolerate SQL injection attacks or are writing a DBA tool you can still submit strings containing SQL queries if you need that degree of flexibility, though of course doing this will have overheads. However, a production system should be using prepared statements: if they don't then the developers must expect SQL injection attacks and poor performance. > Static SQL performs better than stored procedures for single SQL > statements because the SP doesn't have to be loaded. They are still > quite useful, though, where you need the same logic (more than just a > single SQL statement) repeated from multiple applications. > I think that depends on what the stored procedure is doing. If its just a wrapper around a simple query or join then I agree, but if it uses several SQL statements to carry out even a moderately complex operation on several tables as a single transaction, then it will probably be faster. -- martin@ | Martin Gregorie gregorie. | Essex, UK org |
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 19:25 -0500 |
| Message-ID | <o1t3bk$4h3$1@jstuckle.eternal-september.org> |
| In reply to | #36031 |
On 12/2/2016 5:57 PM, Martin Gregorie wrote: > On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: > >> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >> Yes, which is all dynamic SQL. It also has an extra layer of code >> between the application and the database-specific libraries. Something >> COBOL doesn't have. >> > Not necessarily: in a modern RDBMS the optimisation layer is inside the > RDBMS, so the ODBC driver may be little more than an API translator and, > in some cases a field content reformatter. > Yes, and the optimization must be performed with every call (perhaps it can be cached for a connection, but that's all). Static SQL needs to be optimized only once, and occasionally when the data changes enough. > Pushing the optimisation up the the REBIND level where schema changes > require the interface code to be recompiled is just primitive. Yes, I had > to do that with IDMS, but that predated relational databases. No RDBMS > I've used has ever required that. > Schemas don't change very often on mainframes. They plan the database before creating anything. Far unlike most programmers here, who design on the fly. Once created, a database schema may never be changed, even though it's 25 or more years old. The REBIND is there when data changes significantly that the current access plan is no longer optimal. For instance, different indicies may provide better access to the data now than they did before. > Last but not least, if you use prepared SQL statements you guarantee that > the statement has its optimised strategy calculated and stored when the > prepared statement is created (usually during program initialisation) and > so the per-query overheads incurred when your queries are submitted as a > string containing SQL syntax plus the data values vanish: the prepared > query was optimised once at declaration and can be reused many times > without further optimisation. Finally, prepared statements provide > protection against SQL exploits because the prepared SQL statement only > contains place-holders for data values. This means that spurious > statement terminators and other attempts to inject injurious SQL > statements will be treated purely as data: the prepared statement may > reject the 'value strings' given to it but can't and won't try to > interpret them as SQL commands. > Yes, and this has to be done with *EVERY* call. For a large database, that is a significant overhead. But even then you may not get the optimal access. The cost of optimizing further may exceed the gain from the optimization itself. Binding the database means the optimization only has to be performed once, and more time can be spent finding the best optimization. Additionally, prepared statements are only one way of protecting against SQL exploits. Proper handling of the data will do it, even without prepared statements. > >> IBM mainframes use the preprocessor which comes with DB2 and compile >> with any standards-compliant COBOL compiler. >> > Yep, expected. So did IDMS and IMS. > >>> Personally, I favour the ODBC/JDBC approach because: >>> >>> - once a developer knows how to use it they know how to access any DBMS >>> that supports ODBC/JDBC >>> >>> >> But you also are limited by the least capable database. If one database >> doesn't support it, then ODBC/JDBC doesn't support it. >> > I don't think you understand ODBC or have looked at the list of supported > databases. ODBC and JDBC (which can also use ODBC DB drivers) support > almost any database you're likely to use as well as a selection of 4GL > filing systems and indexed files - if anybody has written and published a > driver for the DB or filing system it is supported: you can download it > and its documentation and used it to access its target data store. > I understand it, all right. I've been using it since it came out in what - Windows 3.1? Something like that, anyway. And JDBC since early days of Java. But I've also used static SQL, and understand the difference and the advantages and disadvantages of each. Both ODBC and JDBC add an extra layer of processing, slowing down access. >> There is no query optimization of static SQL at runtime. It's all done >> when the access plan is bound to the database (part of the application >> build process). >> > Quite wrong. See 'prepared statements' above. > Prepared statements are not static SQL. > OTOH if you're prepared to tolerate SQL injection attacks or are writing > a DBA tool you can still submit strings containing SQL queries if you > need that degree of flexibility, though of course doing this will have > overheads. However, a production system should be using prepared > statements: if they don't then the developers must expect SQL injection > attacks and poor performance. > Prepared statements are not the only way to prevent SQL injection. And no one is talking about a DBA tool. We're talking applications, here. Don't change the subject. >> Static SQL performs better than stored procedures for single SQL >> statements because the SP doesn't have to be loaded. They are still >> quite useful, though, where you need the same logic (more than just a >> single SQL statement) repeated from multiple applications. >> > I think that depends on what the stored procedure is doing. If its just a > wrapper around a simple query or join then I agree, but if it uses > several SQL statements to carry out even a moderately complex operation > on several tables as a single transaction, then it will probably be > faster. > > Nope, still not faster. The prepared statements in the SP will end up with exactly the same calls as from the native application. The difference being the additional call to the SP. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Date | 2016-12-02 22:18 -0500 |
| Message-ID | <o1tdhg$l65$1@gioia.aioe.org> |
| In reply to | #36032 |
On 12/2/2016 7:25 PM, Jerry Stuckle wrote: > On 12/2/2016 5:57 PM, Martin Gregorie wrote: >> On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: >>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>> Yes, which is all dynamic SQL. It also has an extra layer of code >>> between the application and the database-specific libraries. Something >>> COBOL doesn't have. >>> >> Not necessarily: in a modern RDBMS the optimisation layer is inside the >> RDBMS, so the ODBC driver may be little more than an API translator and, >> in some cases a field content reformatter. > > Yes, and the optimization must be performed with every call (perhaps it > can be cached for a connection, but that's all). Some databases cache across connections. But even for those that don't it does not matter in most cases. Most heavy database usage in Java uses a connection that also pools prepared statements. >> Last but not least, if you use prepared SQL statements you guarantee that >> the statement has its optimised strategy calculated and stored when the >> prepared statement is created (usually during program initialisation) and >> so the per-query overheads incurred when your queries are submitted as a >> string containing SQL syntax plus the data values vanish: the prepared >> query was optimised once at declaration and can be reused many times >> without further optimisation. Finally, prepared statements provide >> protection against SQL exploits because the prepared SQL statement only >> contains place-holders for data values. This means that spurious >> statement terminators and other attempts to inject injurious SQL >> statements will be treated purely as data: the prepared statement may >> reject the 'value strings' given to it but can't and won't try to >> interpret them as SQL commands. > > Yes, and this has to be done with *EVERY* call. No. Typical execution plans get cached also with parameters. > Additionally, prepared statements are only one way of protecting against > SQL exploits. Proper handling of the data will do it, even without > prepared statements. That usually leads to security disasters. >> I don't think you understand ODBC or have looked at the list of supported >> databases. ODBC and JDBC (which can also use ODBC DB drivers) support >> almost any database you're likely to use as well as a selection of 4GL >> filing systems and indexed files - if anybody has written and published a >> driver for the DB or filing system it is supported: you can download it >> and its documentation and used it to access its target data store. > > I understand it, all right. I've been using it since it came out in > what - Windows 3.1? Something like that, anyway. And JDBC since early > days of Java. > > But I've also used static SQL, and understand the difference and the > advantages and disadvantages of each. Both ODBC and JDBC add an extra > layer of processing, slowing down access. Hmmm. If you really had been using them then you should know that ODBC is indeed dispatching, but that JDBC is based on polymorphism, so no extra layer - or maybe more correctly: not necessarily an extra layer - type 2 JDBC drivers can be considered an extra layer. But not so important as most database vendors have type 4 JDBC drivers available today. Arne
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 22:28 -0500 |
| Message-ID | <o1te19$m62$1@jstuckle.eternal-september.org> |
| In reply to | #36040 |
On 12/2/2016 10:18 PM, Arne Vajhøj wrote: > On 12/2/2016 7:25 PM, Jerry Stuckle wrote: >> On 12/2/2016 5:57 PM, Martin Gregorie wrote: >>> On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: >>>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>>> Yes, which is all dynamic SQL. It also has an extra layer of code >>>> between the application and the database-specific libraries. Something >>>> COBOL doesn't have. >>>> >>> Not necessarily: in a modern RDBMS the optimisation layer is inside the >>> RDBMS, so the ODBC driver may be little more than an API translator and, >>> in some cases a field content reformatter. >> >> Yes, and the optimization must be performed with every call (perhaps it >> can be cached for a connection, but that's all). > > Some databases cache across connections. > > But even for those that don't it does not matter in most cases. > You're right. It doesn't matter when you're the only thing running on a PC. When you have hundreds of programs running on a mainframe, it matters very much. > Most heavy database usage in Java uses a connection that also pools > prepared statements. > So? Prepared statements are not static SQL. >>> Last but not least, if you use prepared SQL statements you guarantee >>> that >>> the statement has its optimised strategy calculated and stored when the >>> prepared statement is created (usually during program initialisation) >>> and >>> so the per-query overheads incurred when your queries are submitted as a >>> string containing SQL syntax plus the data values vanish: the prepared >>> query was optimised once at declaration and can be reused many times >>> without further optimisation. Finally, prepared statements provide >>> protection against SQL exploits because the prepared SQL statement only >>> contains place-holders for data values. This means that spurious >>> statement terminators and other attempts to inject injurious SQL >>> statements will be treated purely as data: the prepared statement may >>> reject the 'value strings' given to it but can't and won't try to >>> interpret them as SQL commands. >> >> Yes, and this has to be done with *EVERY* call. > > No. Typical execution plans get cached also with parameters. > But are not stored permanently in the database, and cannot be updated on command. >> Additionally, prepared statements are only one way of protecting against >> SQL exploits. Proper handling of the data will do it, even without >> prepared statements. > > That usually leads to security disasters. > Not when you know what you're doing, it doesn't. But it is a security disaster for incompetent programmers. >>> I don't think you understand ODBC or have looked at the list of >>> supported >>> databases. ODBC and JDBC (which can also use ODBC DB drivers) support >>> almost any database you're likely to use as well as a selection of 4GL >>> filing systems and indexed files - if anybody has written and >>> published a >>> driver for the DB or filing system it is supported: you can download it >>> and its documentation and used it to access its target data store. >> >> I understand it, all right. I've been using it since it came out in >> what - Windows 3.1? Something like that, anyway. And JDBC since early >> days of Java. >> >> But I've also used static SQL, and understand the difference and the >> advantages and disadvantages of each. Both ODBC and JDBC add an extra >> layer of processing, slowing down access. > > Hmmm. > > If you really had been using them then you should know that ODBC > is indeed dispatching, but that JDBC is based on polymorphism, so > no extra layer - or maybe more correctly: not necessarily > an extra layer - type 2 JDBC drivers can be considered an extra > layer. But not so important as most database vendors have > type 4 JDBC drivers available today. > > Arne > Still another layer involved, and not as efficient as static SQL. But of course, you're an expert on static SQL also, even though you've never used it - and obviously don't even understand it. ROFLMAO! -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Date | 2016-12-02 22:36 -0500 |
| Message-ID | <o1tej3$mal$1@gioia.aioe.org> |
| In reply to | #36041 |
On 12/2/2016 10:28 PM, Jerry Stuckle wrote: > On 12/2/2016 10:18 PM, Arne Vajhøj wrote: >> On 12/2/2016 7:25 PM, Jerry Stuckle wrote: >>> On 12/2/2016 5:57 PM, Martin Gregorie wrote: >>>> On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: >>>>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>>>> Yes, which is all dynamic SQL. It also has an extra layer of code >>>>> between the application and the database-specific libraries. Something >>>>> COBOL doesn't have. >>>>> >>>> Not necessarily: in a modern RDBMS the optimisation layer is inside the >>>> RDBMS, so the ODBC driver may be little more than an API translator and, >>>> in some cases a field content reformatter. >>> >>> Yes, and the optimization must be performed with every call (perhaps it >>> can be cached for a connection, but that's all). >> >> Some databases cache across connections. >> >> But even for those that don't it does not matter in most cases. > > You're right. It doesn't matter when you're the only thing running on a > PC. When you have hundreds of programs running on a mainframe, it > matters very much. No see below. >> Most heavy database usage in Java uses a connection that also pools >> prepared statements. > > So? Prepared statements are not static SQL. True. With a certain definition of static. But it means that databases that purge execution plans when connection is closed is not a problem, because neither the connection nor the statement get closed. >>>> Last but not least, if you use prepared SQL statements you guarantee >>>> that >>>> the statement has its optimised strategy calculated and stored when the >>>> prepared statement is created (usually during program initialisation) >>>> and >>>> so the per-query overheads incurred when your queries are submitted as a >>>> string containing SQL syntax plus the data values vanish: the prepared >>>> query was optimised once at declaration and can be reused many times >>>> without further optimisation. Finally, prepared statements provide >>>> protection against SQL exploits because the prepared SQL statement only >>>> contains place-holders for data values. This means that spurious >>>> statement terminators and other attempts to inject injurious SQL >>>> statements will be treated purely as data: the prepared statement may >>>> reject the 'value strings' given to it but can't and won't try to >>>> interpret them as SQL commands. >>> >>> Yes, and this has to be done with *EVERY* call. >> >> No. Typical execution plans get cached also with parameters. > > But are not stored permanently in the database, and cannot be updated on > command. They are typical not persisted, so they only exist for whatever number of weeks things are kept running. >>> Additionally, prepared statements are only one way of protecting against >>> SQL exploits. Proper handling of the data will do it, even without >>> prepared statements. >> >> That usually leads to security disasters. > > Not when you know what you're doing, it doesn't. But it is a security > disaster for incompetent programmers. The approach to security that developers just need to know what they are doing does not work. Not as long as it is people doing the development. You mandate the use of prepared statements / parameters and you use tools to verify. >>>> I don't think you understand ODBC or have looked at the list of >>>> supported >>>> databases. ODBC and JDBC (which can also use ODBC DB drivers) support >>>> almost any database you're likely to use as well as a selection of 4GL >>>> filing systems and indexed files - if anybody has written and >>>> published a >>>> driver for the DB or filing system it is supported: you can download it >>>> and its documentation and used it to access its target data store. >>> >>> I understand it, all right. I've been using it since it came out in >>> what - Windows 3.1? Something like that, anyway. And JDBC since early >>> days of Java. >>> >>> But I've also used static SQL, and understand the difference and the >>> advantages and disadvantages of each. Both ODBC and JDBC add an extra >>> layer of processing, slowing down access. >> >> Hmmm. >> >> If you really had been using them then you should know that ODBC >> is indeed dispatching, but that JDBC is based on polymorphism, so >> no extra layer - or maybe more correctly: not necessarily >> an extra layer - type 2 JDBC drivers can be considered an extra >> layer. But not so important as most database vendors have >> type 4 JDBC drivers available today. > > Still another layer involved, No. Polymorphism does not introduce an additional layer. Arne
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-03 11:27 -0500 |
| Message-ID | <o1urm4$n1b$1@jstuckle.eternal-september.org> |
| In reply to | #36043 |
On 12/2/2016 10:36 PM, Arne Vajhøj wrote: > On 12/2/2016 10:28 PM, Jerry Stuckle wrote: >> On 12/2/2016 10:18 PM, Arne Vajhøj wrote: >>> On 12/2/2016 7:25 PM, Jerry Stuckle wrote: >>>> On 12/2/2016 5:57 PM, Martin Gregorie wrote: >>>>> On Fri, 02 Dec 2016 15:16:08 -0500, Jerry Stuckle wrote: >>>>>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>>>>> Yes, which is all dynamic SQL. It also has an extra layer of code >>>>>> between the application and the database-specific libraries. >>>>>> Something >>>>>> COBOL doesn't have. >>>>>> >>>>> Not necessarily: in a modern RDBMS the optimisation layer is inside >>>>> the >>>>> RDBMS, so the ODBC driver may be little more than an API translator >>>>> and, >>>>> in some cases a field content reformatter. >>>> >>>> Yes, and the optimization must be performed with every call (perhaps it >>>> can be cached for a connection, but that's all). >>> >>> Some databases cache across connections. >>> >>> But even for those that don't it does not matter in most cases. >> >> You're right. It doesn't matter when you're the only thing running on a >> PC. When you have hundreds of programs running on a mainframe, it >> matters very much. > > No see below. > ROFLMAO! >>> Most heavy database usage in Java uses a connection that also pools >>> prepared statements. >> >> So? Prepared statements are not static SQL. > > True. With a certain definition of static. > The ONLY definition of static SQL. > But it means that databases that purge execution plans when connection > is closed is not a problem, because neither the connection nor the > statement get closed. > Connections don't stay open forever. And neither do statements. The system would quickly run out of resources. But just another thing you don't understand. >>>>> Last but not least, if you use prepared SQL statements you guarantee >>>>> that >>>>> the statement has its optimised strategy calculated and stored when >>>>> the >>>>> prepared statement is created (usually during program initialisation) >>>>> and >>>>> so the per-query overheads incurred when your queries are submitted >>>>> as a >>>>> string containing SQL syntax plus the data values vanish: the prepared >>>>> query was optimised once at declaration and can be reused many times >>>>> without further optimisation. Finally, prepared statements provide >>>>> protection against SQL exploits because the prepared SQL statement >>>>> only >>>>> contains place-holders for data values. This means that spurious >>>>> statement terminators and other attempts to inject injurious SQL >>>>> statements will be treated purely as data: the prepared statement may >>>>> reject the 'value strings' given to it but can't and won't try to >>>>> interpret them as SQL commands. >>>> >>>> Yes, and this has to be done with *EVERY* call. >>> >>> No. Typical execution plans get cached also with parameters. >> >> But are not stored permanently in the database, and cannot be updated on >> command. > > They are typical not persisted, so they only exist for whatever number > of weeks things are kept running. > Or milliseconds. Maybe you keep connections and statements open on your system for weeks. However, wasting resources like that is not done by people who know what they are doing. Every open connection and every open statement requires resources to maintain it. Even mainframes don't have the resources to keep thousands of connections and hundreds of thousands (or millions) of statements open. >>>> Additionally, prepared statements are only one way of protecting >>>> against >>>> SQL exploits. Proper handling of the data will do it, even without >>>> prepared statements. >>> >>> That usually leads to security disasters. >> >> Not when you know what you're doing, it doesn't. But it is a security >> disaster for incompetent programmers. > > The approach to security that developers just need to know what they are > doing does not work. > It works for developers who know what they are doing - and has for millions of programmers for decades. I understand it doesn't work for you. > Not as long as it is people doing the development. You mandate the > use of prepared statements / parameters and you use tools to verify. > Wrong again, as millions of developers have proven for decades. But I understand why YOU need to use prepared statements. >>>>> I don't think you understand ODBC or have looked at the list of >>>>> supported >>>>> databases. ODBC and JDBC (which can also use ODBC DB drivers) support >>>>> almost any database you're likely to use as well as a selection of 4GL >>>>> filing systems and indexed files - if anybody has written and >>>>> published a >>>>> driver for the DB or filing system it is supported: you can >>>>> download it >>>>> and its documentation and used it to access its target data store. >>>> >>>> I understand it, all right. I've been using it since it came out in >>>> what - Windows 3.1? Something like that, anyway. And JDBC since early >>>> days of Java. >>>> >>>> But I've also used static SQL, and understand the difference and the >>>> advantages and disadvantages of each. Both ODBC and JDBC add an extra >>>> layer of processing, slowing down access. >>> >>> Hmmm. >>> >>> If you really had been using them then you should know that ODBC >>> is indeed dispatching, but that JDBC is based on polymorphism, so >>> no extra layer - or maybe more correctly: not necessarily >>> an extra layer - type 2 JDBC drivers can be considered an extra >>> layer. But not so important as most database vendors have >>> type 4 JDBC drivers available today. >> >> Still another layer involved, > > No. Polymorphism does not introduce an additional layer. > > Arne > Once again you have no idea what you are talking about. You should have quit quite a while ago, Arno. All you're doing is making a bigger fool of yourself. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Date | 2016-12-02 22:05 -0500 |
| Message-ID | <o1tcpe$k39$1@gioia.aioe.org> |
| In reply to | #36026 |
On 12/2/2016 3:16 PM, Jerry Stuckle wrote: > On 12/2/2016 1:33 PM, Martin Gregorie wrote: >> On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: >> >>> And exactly how does Java interface efficiently with databases like DB2? >>> Java only has the equivalent of dynamic SQL, where every SQL statement >>> must be interpreted by the database. Additionally, the connection to >>> the database is relatively slow. >>> >> Java usually uses JDBC. > > Yes, which is all dynamic SQL. It also has an extra layer of code > between the application and the database-specific libraries. Not correct. JDBC is not a layer but an API. >> Personally, I favour the ODBC/JDBC approach because: >> >> - once a developer knows how to use it they know how to access any DBMS >> that supports ODBC/JDBC > > But you also are limited by the least capable database. If one database > doesn't support it, then ODBC/JDBC doesn't support it. Not correct. You can use database specific SQL if you want. JDBC even have a nifty API for testing what features are available in a databse. >> - IME the drivers I've seen have been quite efficient, didn't interfere >> with DBMS query optimisation, and have often allowed access to DBMS >> stored procedures - possibly the biggest performance enhancement of >> the lot. > > There is no query optimization of static SQL at runtime. It's all done > when the access plan is bound to the database (part of the application > build process). On DB2. Different databases have very different models in this regard. Arne
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 22:30 -0500 |
| Message-ID | <o1te6f$m62$2@jstuckle.eternal-september.org> |
| In reply to | #36039 |
On 12/2/2016 10:05 PM, Arne Vajhøj wrote: > On 12/2/2016 3:16 PM, Jerry Stuckle wrote: >> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>> On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: >>> >>>> And exactly how does Java interface efficiently with databases like >>>> DB2? >>>> Java only has the equivalent of dynamic SQL, where every SQL statement >>>> must be interpreted by the database. Additionally, the connection to >>>> the database is relatively slow. >>>> >>> Java usually uses JDBC. >> >> Yes, which is all dynamic SQL. It also has an extra layer of code >> between the application and the database-specific libraries. > > Not correct. > > JDBC is not a layer but an API. > Yes, and it has an additional layer that static SQL does not have. But you obviously do not understand static SQL. >>> Personally, I favour the ODBC/JDBC approach because: >>> >>> - once a developer knows how to use it they know how to access any DBMS >>> that supports ODBC/JDBC >> >> But you also are limited by the least capable database. If one database >> doesn't support it, then ODBC/JDBC doesn't support it. > > Not correct. > > You can use database specific SQL if you want. > > JDBC even have a nifty API for testing what features are available > in a databse. > Yes, but even so, all features of an RDBMS are not necessarily available. Only those allowed by JDBC. Database specific interfaces are not portable - but they support all of the features of the database. >>> - IME the drivers I've seen have been quite efficient, didn't interfere >>> with DBMS query optimisation, and have often allowed access to DBMS >>> stored procedures - possibly the biggest performance enhancement of >>> the lot. >> >> There is no query optimization of static SQL at runtime. It's all done >> when the access plan is bound to the database (part of the application >> build process). > > On DB2. > > Different databases have very different models in this regard. > > Arne > As I said - I was talking about DB2, because that's what I'm most familiar with, having used it for over 25 years, on multiple platforms. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Date | 2016-12-02 22:41 -0500 |
| Message-ID | <o1tett$mm6$1@gioia.aioe.org> |
| In reply to | #36042 |
On 12/2/2016 10:30 PM, Jerry Stuckle wrote: > On 12/2/2016 10:05 PM, Arne Vajhøj wrote: >> On 12/2/2016 3:16 PM, Jerry Stuckle wrote: >>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>>> On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: >>>>> And exactly how does Java interface efficiently with databases like >>>>> DB2? >>>>> Java only has the equivalent of dynamic SQL, where every SQL statement >>>>> must be interpreted by the database. Additionally, the connection to >>>>> the database is relatively slow. >>>>> >>>> Java usually uses JDBC. >>> >>> Yes, which is all dynamic SQL. It also has an extra layer of code >>> between the application and the database-specific libraries. >> >> Not correct. >> >> JDBC is not a layer but an API. > > Yes, and it has an additional layer that static SQL does not have. But > you obviously do not understand static SQL. Both JDBC (type 4) and embedded SQL has both exactly one layer between the application and the network. >>>> Personally, I favour the ODBC/JDBC approach because: >>>> >>>> - once a developer knows how to use it they know how to access any DBMS >>>> that supports ODBC/JDBC >>> >>> But you also are limited by the least capable database. If one database >>> doesn't support it, then ODBC/JDBC doesn't support it. >> >> Not correct. >> >> You can use database specific SQL if you want. >> >> JDBC even have a nifty API for testing what features are available >> in a databse. >> > > Yes, but even so, all features of an RDBMS are not necessarily > available. Only those allowed by JDBC. JDBC does not block features. If if can be done as SQL command then JDBC should be able to do it. Arne
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-03 11:29 -0500 |
| Message-ID | <o1urqh$n1b$2@jstuckle.eternal-september.org> |
| In reply to | #36045 |
On 12/2/2016 10:41 PM, Arne Vajhøj wrote: > On 12/2/2016 10:30 PM, Jerry Stuckle wrote: >> On 12/2/2016 10:05 PM, Arne Vajhøj wrote: >>> On 12/2/2016 3:16 PM, Jerry Stuckle wrote: >>>> On 12/2/2016 1:33 PM, Martin Gregorie wrote: >>>>> On Fri, 02 Dec 2016 10:35:36 -0500, Jerry Stuckle wrote: >>>>>> And exactly how does Java interface efficiently with databases like >>>>>> DB2? >>>>>> Java only has the equivalent of dynamic SQL, where every SQL >>>>>> statement >>>>>> must be interpreted by the database. Additionally, the connection to >>>>>> the database is relatively slow. >>>>>> >>>>> Java usually uses JDBC. >>>> >>>> Yes, which is all dynamic SQL. It also has an extra layer of code >>>> between the application and the database-specific libraries. >>> >>> Not correct. >>> >>> JDBC is not a layer but an API. >> >> Yes, and it has an additional layer that static SQL does not have. But >> you obviously do not understand static SQL. > > Both JDBC (type 4) and embedded SQL has both exactly one layer between > the application and the network. > Which is at least one more layer than is between a compiled program and the database. >>>>> Personally, I favour the ODBC/JDBC approach because: >>>>> >>>>> - once a developer knows how to use it they know how to access any >>>>> DBMS >>>>> that supports ODBC/JDBC >>>> >>>> But you also are limited by the least capable database. If one >>>> database >>>> doesn't support it, then ODBC/JDBC doesn't support it. >>> >>> Not correct. >>> >>> You can use database specific SQL if you want. >>> >>> JDBC even have a nifty API for testing what features are available >>> in a databse. >>> >> >> Yes, but even so, all features of an RDBMS are not necessarily >> available. Only those allowed by JDBC. > > JDBC does not block features. If if can be done as SQL command > then JDBC should be able to do it. > > Arne > Yes - IF IT CAN BE DONE AS A SQL COMMAND. There are many operations (typically administrative) which are NOT SQL commands. Rather, they are library routines in the database itself, callable from applications. But you wouldn't understand those, either. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Date | 2016-12-02 22:00 -0500 |
| Message-ID | <o1tcgt$jo0$2@gioia.aioe.org> |
| In reply to | #36023 |
On 12/2/2016 1:33 PM, Martin Gregorie wrote: > - DEC VAXen used their own database interface definition language to > generate an interface module that was linked to the program using the > database, which might be written in COBOL among other languages. DECRDB And more traditional embedded SQL is also supported. Arne
[toc] | [prev] | [next] | [standalone]
| From | lewbloch@gmail.com |
|---|---|
| Date | 2016-12-02 11:40 -0800 |
| Message-ID | <debea5af-9d8f-4173-92d9-e2102ebdc3e3@googlegroups.com> |
| In reply to | #36017 |
"Java only has the equivalent of dynamic SQL, where every SQL statement must be interpreted by the database." That depends on the driver and is not generally true. "Additionally, the connection to the database is relatively slow." Yet fast enough to process tens of millions of gigabyte data structures per hour. You set a pretty high bar for fast. Relative to what, may I ask? -- Lew
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 15:17 -0500 |
| Message-ID | <o1skq9$fqi$2@jstuckle.eternal-september.org> |
| In reply to | #36024 |
On 12/2/2016 2:40 PM, lewbloch@gmail.com wrote: > "Java only has the equivalent of dynamic SQL, where every SQL statement > must be interpreted by the database." > That depends on the driver and is not generally true. > > "Additionally, the connection to > the database is relatively slow." > Yet fast enough to process tens of millions of gigabyte data structures per hour. You set a pretty high bar for fast. Relative to what, may I ask? > > -- > Lew > Which system processes "tens of millions of gigabyte data structures per hour"? And what does it do with those data structures? -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | lewbloch@gmail.com |
|---|---|
| Date | 2016-12-02 16:26 -0800 |
| Message-ID | <fa3652af-a89f-4d17-859d-11a0ca8b50f7@googlegroups.com> |
| In reply to | #36027 |
On Friday, December 2, 2016 at 12:17:36 PM UTC-8, Jerry Stuckle wrote: > On 12/2/2016 2:40 PM, lewbloch wrote: > > "Java only has the equivalent of dynamic SQL, where every SQL statement > > must be interpreted by the database." > > That depends on the driver and is not generally true. > > > > "Additionally, the connection to > > the database is relatively slow." > > Yet fast enough to process tens of millions of gigabyte data structures per hour. You set a pretty high bar for fast. Relative to what, may I ask? > Which system processes "tens of millions of gigabyte data structures per > hour"? And what does it do with those data structures? The IRS Modernized eFile (MeF) system, which processes the United States' income tax returns. It uses Java and Hibernate over JDBC and a DB2 database. You keep talking about how "slow" JDBC is, but give no data, no evidence, no numbers, and use the term "relatively" without specifying relative to what. I/O is a larger bottleneck than JDBC, I wager. Have you any evidence to the contrary? Have you any evidence for any of your assertions? I have also seen drivers documented to work with the underlying database's precompilation of queries, so your statement that it is all dynamic SQL is not relevant. Since the statements are parsed and cached in the database, you get significantly improved performance. http://docs.oracle.com/cd/B25221_04/web.1013/b13593/optimiz010.htm#i1124456 Parameterized SQL (Binding) and Prepared Statement Caching "Using parameterized SQL and prepared statement caching, you can improve performance by reducing the number of times the database SQL engine parses and prepares SQL for a frequently called query." I think your criticisms of JDBC performance do not have much data to support them. -- Lew
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2016-12-02 20:46 -0500 |
| Message-ID | <o1t81u$fui$1@jstuckle.eternal-september.org> |
| In reply to | #36033 |
On 12/2/2016 7:26 PM, lewbloch@gmail.com wrote: > On Friday, December 2, 2016 at 12:17:36 PM UTC-8, Jerry Stuckle wrote: >> On 12/2/2016 2:40 PM, lewbloch wrote: >>> "Java only has the equivalent of dynamic SQL, where every SQL statement >>> must be interpreted by the database." >>> That depends on the driver and is not generally true. >>> >>> "Additionally, the connection to >>> the database is relatively slow." >>> Yet fast enough to process tens of millions of gigabyte data structures per hour. You set a pretty high bar for fast. Relative to what, may I ask? > >> Which system processes "tens of millions of gigabyte data structures per >> hour"? And what does it do with those data structures? > > The IRS Modernized eFile (MeF) system, which processes the United States' income tax returns. It uses Java and Hibernate over JDBC and a DB2 database. > Yes, the eFile system uses Java. But their batch processing - virtually all of their work - still uses COBOL, with a little bit of C. > You keep talking about how "slow" JDBC is, but give no data, no evidence, no numbers, and use the term "relatively" without specifying relative to what. > Look for your own numbers. Java and JDBC is slower than compiled languages with native input. > I/O is a larger bottleneck than JDBC, I wager. Have you any evidence to the contrary? Have you any evidence for any of your assertions? > It is on a PC. But mainframes have multiple fiber optic channels, each of which supports higher speeds than on any PC. And those channels can operate concurrently. Additionally, data is spread across multiple disks, each of with has higher transfer rates than any hard disk on a PC, with huge (i.e. multiple terabyte) buffers. The result is quite fast I/O. > I have also seen drivers documented to work with the underlying database's precompilation of queries, so your statement that it is all dynamic SQL is not relevant. Since the statements are parsed and cached in the database, you get significantly improved performance. > > http://docs.oracle.com/cd/B25221_04/web.1013/b13593/optimiz010.htm#i1124456 > Parameterized SQL (Binding) and Prepared Statement Caching > "Using parameterized SQL and prepared statement caching, you can improve performance by reducing the number of times the database SQL engine parses and prepares SQL for a frequently called query." > > I think your criticisms of JDBC performance do not have much data to support them. > Yes, they are cached and stored in the database. But they are only cached for the connection. A new connection (i.e. a new run of the same program) requires the SQL statements to be parsed and cached again. And no, I don't have hard figures for current JDBC implementations. But unlike you, I have experience in both. Your claim that JDBC is just as good as a native interface has nothing to support it - including any experience on your end. -- ================== Remove the "x" from my email address Jerry Stuckle jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Eric Douglas <e.d.programmer@gmail.com> |
|---|---|
| Date | 2016-12-02 12:44 -0800 |
| Message-ID | <20964714-bac1-43d2-a0e7-f85fab73b337@googlegroups.com> |
| In reply to | #36024 |
On Friday, December 2, 2016 at 2:40:48 PM UTC-5, lewb...@gmail.com wrote: > "Additionally, the connection to > the database is relatively slow." > Yet fast enough to process tens of millions of gigabyte data structures per hour. You set a pretty high bar for fast. Relative to what, may I ask? > > -- > Lew I would guess Java has only gotten better over the years and should be fine for large data sets assuming appropriate hardware. Currently it seems really fast but I am only working with relatively small data now. I have worked with big data and it was sufficient some 17 years ago. If Foot Locker could make it work then, practically anyone should be able to use it today.
[toc] | [prev] | [next] | [standalone]
Page 4 of 5 — ← Prev page 1 2 3 [4] 5 Next page →
Back to top | Article view | comp.lang.java.programmer
csiph-web