Dear Eric, thank you for your advice. I have tried both ANALYZE and EXPLAIN commands to debug the script (although I did not tried EXPLAIN on the inner slow query in the ALIAS yet). Anyway, this is not the reason I post this issue -- I just wonder why there is such a drammatic speedup (for the whole SQL script from 2hours to 1 hour) if I change the syntax of generating indexes (dropping and recreating instead of CREATE INDEX IF NOT EXISTS). My personal guess is that the indices are (for some reason) sometimes not visible from the ALIAS, or that there is some delay.
Best regards David On 14 čvn, 19:23, Eric Faulhaber <[email protected]> wrote: > 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.
