Hi guys,
for the last few days, I have been solving a strange issue. I am
suspicious (but not sure :)) that CREATE INDEX IF NOT EXISTS is not
working well in some cases. Detailed description is given below.
I have quite a long SQL script, which uses several tables, e.g.
TABLE1
TABLE2
TABLE3
TABLE4
The tables have been created, filled with data, some indexes have been
added, then something was updated, more data added, etc. (as I say, a
long script :)).
At a certain stage, I have the following java alias, which runs an
inner prepared sql statement like this:
SELECT
t2.c, t1.col6, t3.col5
FROM TABLE1 t1
LEFT JOIN TABLE2 t2
ON t1.col1 = t2.col1 and t1.col2 = t2.col2
LEFT JOIN TABLE3 t3
ON t1.col3=t3.col1 and t1.col2=t3.col2 and t2.col3 = t3.col4
WHERE t1.col3=?
ORDER BY t2.col5
The whole SQL code is as follows:
-- index all important columns
CREATE INDEX IF NOT EXISTS TABLE1_col1 ON TABLE1(col1);
-- some more indexes ...
-- btw., some of the indexes may already exist at this time
-- initialize the alias
DROP ALIAS myAlias IF EXISTS;
CREATE ALIAS myAlias AS ' String query(Connection conn, String param)
throws SQLException {
PreparedStatement ps =
conn.prepareStatement("___MY_SQL_CODE_ABOVE___");
ps.setString(1,param);
ps.execute();
ResultSet rs = ps.getResultSet();
// ... some data manipulation, not interesting
return something;
}
';
-- use the alias to update table, !!! VERY SLOW !!!
UPDATE TABLE4
SET col2 = myAlias(col1)
WHERE col3 = 'foo' and col4='bar'
;
Strange thing is that the called alias runs very slow, a single call
to the inner prepared statement runs approx. 5 seconds (called for
each row of table4). Seems like the indexes are not used in the joins.
However, if I create the indexes, stop the database and start it
again, the very same script runs smoothly (the inner prepared
statement call reduces to several milliseconds). Is the alias-part
running in a different context/transaction/thread or am I missing
something? Adding a COMMIT after the indexes are made did not help.
What is even more strange, is that I finally solved the issue by
creating the indexes in a different way, by replacing
CREATE INDEX IF NOT EXISTS TABLE1_col1 ON TABLE1(col1);
with
DROP INDEX TABLE1_col1 IF EXISTS;
CREATE INDEX TABLE1_col1 ON TABLE1(col1);
Now the whole script runs smoothly -- I am kinda confused with the
solution. As I say, I managed to create a workaround, but I feel that
there may be some issue in the way the indexes are made. Another
explanation which comes into my mind is that the alias-part sees
different state of the database. I am sorry, but I was unable to
create a runnable example, and the real database is quite big and
contains confidential data :(
Thanks for any hints :)
David
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
