Performance Issue using SQLJ

[klh]

Hi. We're looking at using SQLJ to see how we could incorporate it
into our environment and achieve better throughput and cost savings
over using dynamic SQL through JDBC. We currently run DB2 v7.1 for
os/390 and on the client side DB2 NT v7 FP7.

SQLJ was easy enough to run, but then I ran some benchmark tests
between SQLJ and JDBC so we could give some meaningful statistics
about expected savings to management. In the benchmark tests I found
that the Dynamic JDBC SQL was outperforming the SQLJ. The results I
got follow (for retrieving 1 row - multiple times):

Times are in seconds

Tries 1 10 50 100 500 1000
SQLJ 0.231 0.580 2.223 4.186 20.360 42.441
JDBC 0.029 0.259 1.311 2.684 13.829 28.821

When I watched through activity monitor I saw that the problem was
SQLJ set up a cursor for processing my single-row select statement.
So it was incurring cursor for each time it was executed.

I also, however, ran a benchmark test on updating the same row 20
times. Again SQLJ was about twice as slow as Dynamic SQL.

So, I have a couple of questions.

1) Is it possible to get SQLJ to perform a non-cursored select? I've
not been able to find a way (looking at the sample programs and
documentation and news groups).

2) Is there anything else I'm doing wrong? I basically just used the
static.sqlj sample program and changed values to execute in our
environment, then pre-compiled it, then compiled the output java pgm.

Here are the sections of code I used for benchmarking -
For SQLJ -
#sql { SELECT field1 INTO :field1_var FROM dbname.tablename WHERE
row_id = 1 } ;

For JDBC -
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select field1 from
dbname.tablename where row_id = 1");

while (rs.next())
{
String a = rs.getString(1);

System.out.print(" business name= " + a);
System.out.print("\n");
}

rs.close();
stmt.close();

Thanks for any suggestions,
klh

[Knut Stolze]

klh <klharwell@kcc.usda.gov> wrote:

> Hi. We're looking at using SQLJ to see how we could incorporate it
> into our environment and achieve better throughput and cost savings
> over using dynamic SQL through JDBC. We currently run DB2 v7.1 for
> os/390 and on the client side DB2 NT v7 FP7.
>
> SQLJ was easy enough to run, but then I ran some benchmark tests
> between SQLJ and JDBC so we could give some meaningful statistics
> about expected savings to management. In the benchmark tests I found
> that the Dynamic JDBC SQL was outperforming the SQLJ. The results I
> got follow (for retrieving 1 row - multiple times):
>
> Times are in seconds
>
> Tries 1 10 50 100 500 1000
> SQLJ 0.231 0.580 2.223 4.186 20.360 42.441
> JDBC 0.029 0.259 1.311 2.684 13.829 28.821
>
> When I watched through activity monitor I saw that the problem was
> SQLJ set up a cursor for processing my single-row select statement.
> So it was incurring cursor for each time it was executed.
>
> I also, however, ran a benchmark test on updating the same row 20
> times. Again SQLJ was about twice as slow as Dynamic SQL.
>
> So, I have a couple of questions.
>
> 1) Is it possible to get SQLJ to perform a non-cursored select? I've
> not been able to find a way (looking at the sample programs and
> documentation and news groups).

Did you run "db2sqljcustomize" to generate precompiled packages and bind
them to the database you are using?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[klh]

> Did you run "db2sqljcustomize" to generate precompiled packages and bind

> them to the database you are using?

No. I followed the sample from the documentation.
1 - Turned off java compiler "SET JAVA_COMPILER=NONE".
2 - Ran the bldsqlj.bat (as "bdlsqlj TestSQLJ dbname userid pswd")
which created the TestSQLJ.java, TestSQLJ_SJProfile0.ser, and
TestSQLJ_SJProfileKeys.class files, as well as created a package for
the program.
3 - Compiled the java program "javac TestSQLJ.java"
4 - Executed the program.

I didn't see anything about db2sqljcustomize. It's not in my
documentation, nor can I find it in any of my installation
directories.

Thanks,
klh

[klh]

> Have a look here:

> [url]http://www7b.boulder.ibm.com/webapp/dd/ViewServlet.wss?viewType=Library&devDomain=dmdd&so rtBy=Posted&start=1&keyword=&prodfam=0&topic=0&for mat=18&count=0[/url]
>
> and search for "SQLJ". There is a tutorial that talks about performance for
> SQLJ and JDBC which might help you along.

Thanks for the tip, Knut. Ultimately, though, none of the suggestions
helped.

I guess I'll have to accept that SQLJ runs slower in our environment
(at least in version 7.1). However, I still have the question about
the "Select Into" statement. All of the IBM documentation says "The
SELECT INTO statement produces a result table that contains at most
one row". So why would the SQLJ translator turn that into a cursored
select? Since it does create a cursor it invokes all the overhead of
describing, opening, and fetching.

Thanks,
klh