informix - performance - view generation - subselect

Liquibase Development
1

In the class CreateViewGeneratorInformix in the generateSql method there is the statement


  1. String createClause = "CREATE VIEW  " + viewName + " AS SELECT * FROM (" + statement.getSelectQuery() + ") AS v";

The problem is that the select query is inserted within the ". So that first the hole table is loaded to execute the view.


Can anyone confirm this? Or am I wrong?

2

If you want to take a look at it, the general function is the class MissingViewChangeGenerator. This in turn uses CreateViewChange, CreateViewStatement, and CreateViewGenerator. The CreateViewGenerator seems to be where most of the SQL is generated. There is a special CreateViewGeneratorInformix that is just for Informix. 


Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

3

There is also a problem with the naming of the views. Informix has a special syntax to name views:


http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls396.htm

4

Hi,


I think there is a problem with the genration of views in informix (maybe also other dialects). I don’t have any problems yet but after creating a view on a table with millions of rows a got performance problems. It seems, that on each call all entries on the database are fetched cause of an additional select within the view which is not needed.


Which code is responsible to generate the views?



Greets

Meleagros


5

I created https://liquibase.jira.com/browse/CORE-1972 to track work on a fix.

Nathan