David, I don't understand why your workaround would make a difference, either. You may already be well beyond this, but just to cover the basics (since I didn't notice any mention of the following in your post) see:
http://www.h2database.com/html/grammar.html#analyze to generate selectivity statistics, to allow the query planner to do its best job with indices; and http://www.h2database.com/html/grammar.html#explain to understand the execution plan being chosen for your SQL. I would recommend using EXPLAIN with your slow statement both before and after you run the ANALYZE command, to see if the plan changes as a result of generating statistics. Best regards, Eric On Jun 14, 12:01 pm, David Štefka <[email protected]> wrote: > 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.
