Обсуждение: nit-pick optimization for findColumn()
Hi,
(I have not submitted this to the patch list, as I do not know if I
should do that, or if there is a jdbc maintainer which gatekeeps
patches)
A very small optimization, but anyway. findColumn() uses
String.equalsIgnoreCase() which is slow. I've made a simple
optimization which first tries a simple ASCII ignorecase loop, and
falls back to equalsIgnoreCase if the simple loop can't find the column
(should only happend if you have non-ascii characters in the name).
Using findColumn from 7.3.3:
Time for test: 1446 msecs.
Time for TEST: 2382 msecs.
Time for TeSt: 1975 msecs.
Time for héllo: 6342 msecs.
Time for HÉLLO: 7511 msecs.
Time for col_1: 556 msecs.
Time for col_2: 1488 msecs. // long common prefix
Time for col_3: 2421 msecs.
Time for col_4: 3286 msecs.
Time for col_5: 4227 msecs.
Using optimized findColumn:
Time for test: 767 msecs.
Time for TEST: 777 msecs.
Time for TeSt: 777 msecs.
Time for héllo: 1443 msecs.
Time for HÉLLO: 8626 msecs.
Time for col_1: 613 msecs.
Time for col_2: 1109 msecs.
Time for col_3: 1651 msecs.
Time for col_4: 2087 msecs.
Time for col_5: 2622 msecs.
(java 1.4.1, PowerPC, Mac OS X 10.2.6)
@@@@@@@@@@@@@ DIFF FOR CVS HEAD @@@@@@@@@@@@@@
Index:
pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
AbstractJdbc1ResultSet.java
===================================================================
RCS file:
/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/
jdbc1/AbstractJdbc1ResultSet.java,v
retrieving revision 1.12
diff -u -d -b -w -r1.12 AbstractJdbc1ResultSet.java
---
pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
AbstractJdbc1ResultSet.java 3 May 2003 20:40:45 -0000 1.12
+++
pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
AbstractJdbc1ResultSet.java 14 Jun 2003 21:50:27 -0000
@@ -607,6 +607,25 @@
int i;
final int flen = fields.length;
+ final int cnlen = columnName.length();
+ for (i = 0 ; i < flen; ++i) {
+ String fieldName = fields[i].getName();
+ if (fieldName.length() != cnlen)
+ continue;
+ int j = 0;
+ while (true) {
+ int ch1 = columnName.charAt(j);
+ int ch2 = fieldName.charAt(j);
+ if (ch1 >= 'A' && ch1 <= 'Z')
+ ch1 += 'a' - 'A';
+ if (ch2 >= 'A' && ch2 <= 'Z')
+ ch2 += 'a' - 'A';
+ if (ch1 != ch2)
+ break;
+ if (++j >= cnlen)
+ return i + 1;
+ }
+ }
for (i = 0 ; i < flen; ++i)
if
(fields[i].getName().equalsIgnoreCase(columnName))
return (i + 1);
@@@@@@@@@@@@@ DIFF FOR 7.3.3 @@@@@@@@@@@@@@
--- org/postgresql/jdbc1/orig-AbstractJdbc1ResultSet.java Tue Jan
14 10:15:35 2003
+++ org/postgresql/jdbc1/AbstractJdbc1ResultSet.java Sat Jun 14
23:24:09 2003
@@ -522,6 +522,25 @@
int i;
final int flen = fields.length;
+ final int cnlen = columnName.length();
+ for (i = 0 ; i < flen; ++i) {
+ String fieldName = fields[i].getName();
+ if (fieldName.length() != cnlen)
+ continue;
+ int j = 0;
+ while (true) {
+ int ch1 = columnName.charAt(j);
+ int ch2 = fieldName.charAt(j);
+ if (ch1 >= 'A' && ch1 <= 'Z')
+ ch1 += 'a' - 'A';
+ if (ch2 >= 'A' && ch2 <= 'Z')
+ ch2 += 'a' - 'A';
+ if (ch1 != ch2)
+ break;
+ if (++j >= cnlen)
+ return i + 1;
+ }
+ }
for (i = 0 ; i < flen; ++i)
if
(fields[i].getName().equalsIgnoreCase(columnName))
return (i + 1);
// TEST APPLICATION.
import java.sql.*;
public class test
{
// must pass connection URL as first argument.
public static void main(String[] argv)
{
try {
DriverManager.registerDriver(new org.postgresql.Driver());
Connection conn = DriverManager.getConnection(argv[0]);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT\n" +
" 1 as col_1,\n" +
" 2 as col_2,\n" +
" 3 as col_3,\n" +
" 4 as col_4,\n" +
" 5 as col_5,\n" +
" 6 as col_6,\n" +
" 7 as col_7,\n" +
" 8 as \"héllo\",\n" + // with accent: e´
" 9 as warm_up,\n" +
" 0 as test\n");
time(rs, 9, "warm_up");
time(rs, 9, "warm_up");
time(rs, 10, "test");
time(rs, 10, "TEST");
time(rs, 10, "TeSt");
time(rs, 8, "héllo");
time(rs, 8, "HÉLLO");
time(rs, 1, "col_1");
time(rs, 2, "col_2");
time(rs, 3, "col_3");
time(rs, 4, "col_4");
time(rs, 5, "col_5");
System.err.println(sb.toString());
}
catch (Exception ex) {
ex.printStackTrace();
}
}
static StringBuffer sb = new StringBuffer();
static void time(ResultSet rs, int wanted, String colName) throws
SQLException
{
if (rs.findColumn(colName) != wanted)
throw new SQLException("Fails for " + colName);
int num = 1000000;
long st0 = System.currentTimeMillis();
for (int i = num; i > 0; i--)
rs.findColumn(colName);
long st1 = System.currentTimeMillis();
sb.append(" Time for " + colName + ": " + (st1 - st0) + " msecs.\n");
}
}
// END OF FILE
----
- Peter Speck
"The difference between theory and practice
is small in theory and large in practice..."
Peter,
Have you identified this as being a performance bottleneck in any
realworld application? The microbenchmark you provide below would seem
to indicate that there really isn't any performance bottleneck here
since in a million calls on the order of .5 seconds is the only
difference between the optimized and unoptimized versions.
Given that this optimization adds some complexity to the code, I would
be inclined not to include this patch, unless you know of some real
world benchmarks or applications where this optimization wouldn't be in
the statistical noise of the benchmark.
It is also interesting to note that when I run your microbenchmark on
jdk1.4.1_03 on Redhat 7.3 I don't get nearly the improvement you see on
mac osx (in fact in 2 of the 10 tests the patch was slower). Here are
my results:
Base:
Time for warm_up: 1534 msecs.
Time for warm_up: 1495 msecs.
Time for test: 1383 msecs.
Time for TEST: 2097 msecs.
Time for TeSt: 1733 msecs.
Time for héllo: 1337 msecs.
Time for HÉLLO: 2215 msecs.
Time for col_1: 583 msecs.
Time for col_2: 1387 msecs.
Time for col_3: 2227 msecs.
Time for col_4: 3022 msecs.
Time for col_5: 3765 msecs.
Patch:
Time for warm_up: 1299 msecs.
Time for warm_up: 1258 msecs.
Time for test: 1001 msecs.
Time for TEST: 1011 msecs.
Time for TeSt: 1006 msecs.
Time for héllo: 1134 msecs.
Time for HÉLLO: 3145 msecs.
Time for col_1: 591 msecs.
Time for col_2: 1190 msecs.
Time for col_3: 1764 msecs.
Time for col_4: 2322 msecs.
Time for col_5: 2885 msecs.
thanks,
--Barry
Peter Speck wrote:
> Hi,
>
> (I have not submitted this to the patch list, as I do not know if I
> should do that, or if there is a jdbc maintainer which gatekeeps patches)
>
> A very small optimization, but anyway. findColumn() uses
> String.equalsIgnoreCase() which is slow. I've made a simple
> optimization which first tries a simple ASCII ignorecase loop, and
> falls back to equalsIgnoreCase if the simple loop can't find the column
> (should only happend if you have non-ascii characters in the name).
>
> Using findColumn from 7.3.3:
> Time for test: 1446 msecs.
> Time for TEST: 2382 msecs.
> Time for TeSt: 1975 msecs.
> Time for héllo: 6342 msecs.
> Time for HÉLLO: 7511 msecs.
> Time for col_1: 556 msecs.
> Time for col_2: 1488 msecs. // long common prefix
> Time for col_3: 2421 msecs.
> Time for col_4: 3286 msecs.
> Time for col_5: 4227 msecs.
>
> Using optimized findColumn:
> Time for test: 767 msecs.
> Time for TEST: 777 msecs.
> Time for TeSt: 777 msecs.
> Time for héllo: 1443 msecs.
> Time for HÉLLO: 8626 msecs.
> Time for col_1: 613 msecs.
> Time for col_2: 1109 msecs.
> Time for col_3: 1651 msecs.
> Time for col_4: 2087 msecs.
> Time for col_5: 2622 msecs.
>
> (java 1.4.1, PowerPC, Mac OS X 10.2.6)
>
>
> @@@@@@@@@@@@@ DIFF FOR CVS HEAD @@@@@@@@@@@@@@
>
> Index: pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
> AbstractJdbc1ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/
> jdbc1/AbstractJdbc1ResultSet.java,v
> retrieving revision 1.12
> diff -u -d -b -w -r1.12 AbstractJdbc1ResultSet.java
> --- pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
> AbstractJdbc1ResultSet.java 3 May 2003 20:40:45 -0000 1.12
> +++ pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/
> AbstractJdbc1ResultSet.java 14 Jun 2003 21:50:27 -0000
> @@ -607,6 +607,25 @@
> int i;
>
> final int flen = fields.length;
> + final int cnlen = columnName.length();
> + for (i = 0 ; i < flen; ++i) {
> + String fieldName = fields[i].getName();
> + if (fieldName.length() != cnlen)
> + continue;
> + int j = 0;
> + while (true) {
> + int ch1 = columnName.charAt(j);
> + int ch2 = fieldName.charAt(j);
> + if (ch1 >= 'A' && ch1 <= 'Z')
> + ch1 += 'a' - 'A';
> + if (ch2 >= 'A' && ch2 <= 'Z')
> + ch2 += 'a' - 'A';
> + if (ch1 != ch2)
> + break;
> + if (++j >= cnlen)
> + return i + 1;
> + }
> + }
> for (i = 0 ; i < flen; ++i)
> if
> (fields[i].getName().equalsIgnoreCase(columnName))
> return (i + 1);
>
>
> @@@@@@@@@@@@@ DIFF FOR 7.3.3 @@@@@@@@@@@@@@
>
> --- org/postgresql/jdbc1/orig-AbstractJdbc1ResultSet.java Tue Jan
> 14 10:15:35 2003
> +++ org/postgresql/jdbc1/AbstractJdbc1ResultSet.java Sat Jun 14
> 23:24:09 2003
> @@ -522,6 +522,25 @@
> int i;
>
> final int flen = fields.length;
> + final int cnlen = columnName.length();
> + for (i = 0 ; i < flen; ++i) {
> + String fieldName = fields[i].getName();
> + if (fieldName.length() != cnlen)
> + continue;
> + int j = 0;
> + while (true) {
> + int ch1 = columnName.charAt(j);
> + int ch2 = fieldName.charAt(j);
> + if (ch1 >= 'A' && ch1 <= 'Z')
> + ch1 += 'a' - 'A';
> + if (ch2 >= 'A' && ch2 <= 'Z')
> + ch2 += 'a' - 'A';
> + if (ch1 != ch2)
> + break;
> + if (++j >= cnlen)
> + return i + 1;
> + }
> + }
> for (i = 0 ; i < flen; ++i)
> if
> (fields[i].getName().equalsIgnoreCase(columnName))
> return (i + 1);
>
>
>
>
>
> // TEST APPLICATION.
>
> import java.sql.*;
>
> public class test
> {
> // must pass connection URL as first argument.
> public static void main(String[] argv)
> {
> try {
> DriverManager.registerDriver(new org.postgresql.Driver());
> Connection conn = DriverManager.getConnection(argv[0]);
> Statement stmt = conn.createStatement();
> ResultSet rs = stmt.executeQuery(
> "SELECT\n" +
> " 1 as col_1,\n" +
> " 2 as col_2,\n" +
> " 3 as col_3,\n" +
> " 4 as col_4,\n" +
> " 5 as col_5,\n" +
> " 6 as col_6,\n" +
> " 7 as col_7,\n" +
> " 8 as \"héllo\",\n" + // with accent: e´
> " 9 as warm_up,\n" +
> " 0 as test\n");
> time(rs, 9, "warm_up");
> time(rs, 9, "warm_up");
> time(rs, 10, "test");
> time(rs, 10, "TEST");
> time(rs, 10, "TeSt");
> time(rs, 8, "héllo");
> time(rs, 8, "HÉLLO");
> time(rs, 1, "col_1");
> time(rs, 2, "col_2");
> time(rs, 3, "col_3");
> time(rs, 4, "col_4");
> time(rs, 5, "col_5");
> System.err.println(sb.toString());
> }
> catch (Exception ex) {
> ex.printStackTrace();
> }
> }
>
> static StringBuffer sb = new StringBuffer();
> static void time(ResultSet rs, int wanted, String colName) throws
> SQLException
> {
> if (rs.findColumn(colName) != wanted)
> throw new SQLException("Fails for " + colName);
> int num = 1000000;
> long st0 = System.currentTimeMillis();
> for (int i = num; i > 0; i--)
> rs.findColumn(colName);
> long st1 = System.currentTimeMillis();
> sb.append(" Time for " + colName + ": " + (st1 - st0) + "
> msecs.\n");
> }
> }
>
> // END OF FILE
> ----
> - Peter Speck
>
> "The difference between theory and practice
> is small in theory and large in practice..."
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
Hi,
> Have you identified this as being a performance bottleneck in any
> realworld application?
Not in the jdbc driver. I had some code from a previous developer where
I had to eliminate calls to String.equalsIgnoreCase() because it was
inside a tight loop (called more than 310 million times). So my
optimization of the jdbc driver was an automatic reaction. I knew the
oracle 8.1 driver does 2 loops, first one with String.equals(), and
then one with String.equalsIgnoreCase(). So you're right, this
doesn't matter for the jdbc driver.
----
- Peter Speck