The precondition “IndexExists” is building a full database snapshot for each check.
Sadly, the JDBC api doesn’t have simple getIndex function.
The best solution would be to implement some database specific query for each RDBMS (like http://liquibase.org/forum/index.php?topic=98.msg368#msg368 )
For the generic implementation, may be we can build a reduced snapshot without columns and views ?
Any comment ?
Yes, the indexExists precondition needs some performance improvements. I made sure the precondition APIs would support it, but had to put off implementing it until 2.1 in order to get 2.0 out the door. Like you said, there is no jdbc standard, so it will be a bit of work.
Cutting back on what gets snapshot-ed may be a good idea. We may need use column information to determine if an index is a stand alone index or a PK or FK index, though. Views could definitely be skipped, though.
If you know the SQL for your particular database, you could subclass the snapshot class for your database type (see http:// liquibase.org/extensions). If you could send it along to us as well, that would be great. Otherwise, I plan to look at it for 2.1.
Nathan
I’m using Liquibase 3.2.0 snapshot and also suffering from severely slow performance in the index checks. I did some digging and found the query (Oracle) and stack trace is where the slowness appears to be. In my environment I was finding this particular query was taking between 20 and 30 minutes.
SQL Query:
SELECT NULL AS pktable_cat ,
p.owner AS pktable_schem,
p.table_name AS pktable_name ,
pc.column_name AS pkcolumn_name,
NULL AS fktable_cat ,
f.owner AS fktable_schem,
f.table_name AS fktable_name ,
fc.column_name AS fkcolumn_name,
fc.position AS key_seq ,
NULL AS update_rule ,
DECODE (f.delete_rule, ‘CASCADE’, 0, ‘SET NULL’, 2, 1) AS delete_rule ,
f.constraint_name AS fk_name ,
p.constraint_name AS pk_name ,
DECODE(f.deferrable, ‘DEFERRABLE’, 5, ‘NOT DEFERRABLE’, 7, ‘DEFERRED’, 6) deferrability
FROM all_cons_columns pc,
all_constraints p ,
all_cons_columns fc,
all_constraints f
WHERE 1 = 1
AND p.owner = ‘OPENTWINS_STAGING_SBS’
AND f.constraint_type = ‘R’
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
AND p.constraint_type IN (‘P’, ‘U’)
AND pc.owner = p.owner
AND pc.constraint_name = p.constraint_name
AND pc.table_name = p.table_name
AND fc.owner = f.owner
AND fc.constraint_name = f.constraint_name
AND fc.table_name = f.table_name
AND fc.position = pc.position
ORDER BY fktable_schem,
fktable_name ,
key_seq ;
Stack trace below:
Daemon Thread [[ACTIVE] ExecuteThread: ‘0’ for queue: ‘weblogic.kernel.Default (self-tuning)’] (Suspended)
http://forum.liquibase.org/topic/column-exists-precondition-is-slow appears to also be related to this issue.
Thanks. I created https://liquibase.jira.com/browse/CORE-1904 to track the fix.
It takes that long to run on your system a single time? Or is the query ran multiple times that add up to 20-30 mins?
Nathan
Thanks, I’ll take a look at what you are doing and see what makes sense to move back into the main repo.
Nathan
This query alone took 20-30 minutes on our database for a single invocation. We have a large database and it is copied per developer on the same database, under a different user/schema area.
I began writing some Oracle preconditions as a liquibase plugin to speed things up, they appear to be running much faster for us.
https://github.com/mrswadge/liquibase-oracle-preconditions
