Why is my static SQL dramatically faster than dynamic?

[=?ISO-8859-1?Q?Tim_B=FCthe?=]

Hi,

we are building a Java webapplication using JSF, running on websphere,
querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and
PreparedStateme nts only (aka dynamic SQL). Every night, there is a ETL
which deletes most of the data in the database and fills it all new.

We observed very bad performance for some statements that ran for
minutes (The queried table is about 100,000 records and the result is
about 500 rows). Executing the same SQL in the control center took
round about 30 ms. After a while we realized that the difference
between our app and the control center, and some other tools we used
to execute the same query, is the use of dynamic and static SQL. We
change the source code to static SQL by removing all the wildcards and
putting the parameters right in the SQL-String and viola the
performance was good.

That all seems to me, like there is a problem with the execution plans
or something like this and by using static sql, we force it to
recalculate the plans for the given statement. I already tried to
clear the package cache by invoking "flush package cache dynamic" but
had no luck.

My questions are:
1. Does my theory sounds plausible?
2. How to get rid off the bad execution plans?
3. Why there are bad execution plans? Is this because we reimport all
the data every night?

Thanks for your help!

Cheers,
Tim

[Ian]

Tim Büthe wrote:

Hi,

we are building a Java webapplication using JSF, running on websphere,
querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and
PreparedStateme nts only (aka dynamic SQL). Every night, there is a ETL
which deletes most of the data in the database and fills it all new.

We observed very bad performance for some statements that ran for
minutes (The queried table is about 100,000 records and the result is
about 500 rows). Executing the same SQL in the control center took
round about 30 ms. After a while we realized that the difference
between our app and the control center, and some other tools we used
to execute the same query, is the use of dynamic and static SQL. We
change the source code to static SQL by removing all the wildcards and
putting the parameters right in the SQL-String and viola the
performance was good.

First of all, you're not describing static SQL. You're describing
dynamic SQL, where one statement uses parameter markers, and the
other does not.

select * from table where c1 = ? <= parameter marker
select * from table where c1 = 1

Both of these statements can be either dynamic SQL or static SQL,
and that depends on the application. PreparedStateme nts in java
are always dynamic SQL.

Anyway, this sounds like a clear case of distribution statistics
coming in to play.

When your query includes the values for each column, DB2 can use
distribution statistics that you may have collected to make a
better plan.

With a prepared statement that uses parameter markers, DB2 has to
create the executable plan with no knowledge of what value the
parameter marker will have. (i.e., it can't take advantage of
distribution statistics).

This latter case is why IBM added the "REOPT" ability to DB2. But
I am not quite sure if it's possible to take advantage of this
with dynamically prepared SQL statements in Java.

[Henry J.]

On Sep 29, 3:17*pm, Ian <ianb...@mobile audio.comwrote:

Tim Büthe wrote:

Hi,

we are building a Java webapplication using JSF, running on websphere,
querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and
PreparedStateme nts only (aka dynamic SQL). Every night, there is a ETL
which deletes most of the data in the database and fills it all new.

We observed very bad performance for some statements that ran for
minutes (The queried table is about 100,000 records and the result is
about 500 rows). Executing the same SQL in the control center took
round about 30 ms. After a while we realized that the difference
between our app and the control center, and some other tools we used
to execute the same query, is the use of dynamic and static SQL. We
change the source code to static SQL by removing all the wildcards and
putting the parameters right in the SQL-String and viola the
performance was good.

First of all, you're not describing static SQL. *You're describing
dynamic SQL, where one statement uses parameter markers, and the
other does not.

* * select * from table where c1 = ? * <= parameter marker
* * select * from table where c1 = 1

Both of these statements can be either dynamic SQL or static SQL,
and that depends on the application. *PreparedStatem ents in java
are always dynamic SQL.

Anyway, this sounds like a clear case of distribution statistics
coming in to play.

When your query includes the values for each column, DB2 can use
distribution statistics that you may have collected to make a
better plan.

With a prepared statement that uses parameter markers, DB2 has to
create the executable plan with no knowledge of what value the
parameter marker will have. *(i.e., it can't take advantage of
distribution statistics).

This latter case is why IBM added the "REOPT" ability to DB2. *But
I am not quite sure if it's possible to take advantage of this
with dynamically prepared SQL statements in Java.- Hide quoted text -

- Show quoted text -

Good thread. As a DB2 novice, here is my 2c. I had a similar case.
I managed to improve the performance by making the table volatile and
only having one index (which is the primary key in which c1 is the
leading column).

Question for Ian. I'm confused by the concept of dynamic vs. static
SQLs in DB2. So any prepared SQLs are dynamic? How about SQLs in
stored procedures? If my memory serves me, the dynamic SQL in Oracle
or Sybase is any SQL that is built on run-time and executed using a
special system stored proc. Seems to make more sense to me. Thanks.

[Ian]

Henry J. wrote:

>
Question for Ian. I'm confused by the concept of dynamic vs. static
SQLs in DB2. So any prepared SQLs are dynamic? How about SQLs in
stored procedures? If my memory serves me, the dynamic SQL in Oracle
or Sybase is any SQL that is built on run-time and executed using a
special system stored proc. Seems to make more sense to me. Thanks.

Static SQL has a fixed query plan that is determined at compile (bind)
time. The plans are stored in the system catalog (as packages). For
Java, static SQL is possible using SQLJ; in C you're writing
standard embedded SQL. You can see examples of this in the sample
application code (look for files named *.sqc or *.sqlj).

In both cases, you run the application code through the DB2 precompiler,
which produces compiler-ready code and the package, which you'll bind
into the database.

SQL Stored procedures can contain both static and dynamic SQL.
Generally any SELECT/INSERT/UPDATE/DELETE statements in the stored
procedure are static unless you're using the PREPARE/EXECUTE
or EXECUTE IMMEDIATE statements.

[Henry J.]

On Sep 30, 1:12*pm, Ian <ianb...@mobile audio.comwrote:

Henry J. wrote:

Question for Ian. *I'm confused by the concept of dynamic vs. static
SQLs in DB2. *So any prepared SQLs are dynamic? *How about SQLs in
stored procedures? *If my memory serves me, the dynamic SQL in Oracle
or Sybase is any SQL that is built on run-time and executed using a
special system stored proc. *Seems to make more sense to me. *Thanks.

Static SQL has a fixed query plan that is determined at compile (bind)
time. *The plans are stored in the system catalog (as packages). *For
Java, static SQL is possible using SQLJ; *in C you're writing
standard embedded SQL. *You can see examples of this in the sample
application code (look for files named *.sqc or *.sqlj).

In both cases, you run the application code through the DB2 precompiler,
which produces compiler-ready code and the package, which you'll bind
into the database.

SQL Stored procedures can contain both static and dynamic SQL.
Generally any SELECT/INSERT/UPDATE/DELETE statements in the stored
procedure are static unless you're using the PREPARE/EXECUTE
or EXECUTE IMMEDIATE statements.

Thanks very much for the detailed explanations, Ian.

[Henry J.]

On Sep 30, 1:12*pm, Ian <ianb...@mobile audio.comwrote:

Henry J. wrote:

Question for Ian. *I'm confused by the concept of dynamic vs. static
SQLs in DB2. *So any prepared SQLs are dynamic? *How about SQLs in
stored procedures? *If my memory serves me, the dynamic SQL in Oracle
or Sybase is any SQL that is built on run-time and executed using a
special system stored proc. *Seems to make more sense to me. *Thanks.

Static SQL has a fixed query plan that is determined at compile (bind)
time. *The plans are stored in the system catalog (as packages). *For
Java, static SQL is possible using SQLJ; *in C you're writing
standard embedded SQL. *You can see examples of this in the sample
application code (look for files named *.sqc or *.sqlj).

In both cases, you run the application code through the DB2 precompiler,
which produces compiler-ready code and the package, which you'll bind
into the database.

SQL Stored procedures can contain both static and dynamic SQL.
Generally any SELECT/INSERT/UPDATE/DELETE statements in the stored
procedure are static unless you're using the PREPARE/EXECUTE
or EXECUTE IMMEDIATE statements.

Thanks very much for the detailed explanations, Ian.

[=?ISO-8859-1?Q?Tim_B=FCthe?=]

Hi,

first of all, thanks for your reply.

Ian schrieb:

First of all, you're not describing static SQL. You're describing
dynamic SQL, where one statement uses parameter markers, and the
other does not.

I see, got that wrong.

This latter case is why IBM added the "REOPT" ability to DB2. But
I am not quite sure if it's possible to take advantage of this
with dynamically prepared SQL statements in Java.

Yes, I googled a lot on that topic and found that REOPT option. From
my understanding, setting REOPT=always is nearly the same as using
Statements instead of PreparedStateme nts or just don't use parameter
markers. Since we've wrapped the PreparedStateme nt class, I can build
an option in, that will replace all markers before sending the SQL.
Maybe that's a workaround...

I still don't understand why DB2 does so bad, even with bad execution
plans and full index scans, execution time of four minutes is a mess.

Tanks again,
Tim