ERXSQLHelper.FrontBaseSQLHelper Class Reference

Inherits er::extensions::jdbc::ERXSQLHelper.

Collaboration diagram for ERXSQLHelper.FrontBaseSQLHelper:

[legend]

List of all members.

Public Member Functions
String	limitExpressionForSQL (EOSQLExpression expression, EOFetchSpecification fetchSpecification, String sql, long start, long end)
void	prepareConnectionForSchemaChange (EOEditingContext ec, EOModel model)
String	quoteColumnName (String columnName)
boolean	reassignExternalTypeForValueTypeOverride (EOAttribute attribute)
void	restoreConnectionSettingsAfterSchemaChange (EOEditingContext ec, EOModel model)
boolean	shouldExecute (String sql)
String	sqlForCreateIndex (String indexName, String tableName, ColumnIndex...columnIndexes)
String	sqlForCreateUniqueIndex (String indexName, String tableName, ColumnIndex...columnIndexes)
String	sqlForFullTextQuery (ERXFullTextQualifier qualifier, EOSQLExpression expression)
Protected Member Functions
Pattern	commentPattern ()
int	maximumElementPerInClause (EOEntity entity)
String	sqlForGetNextValFromSequencedNamed (String sequenceName)
Static Private Attributes
static final String	PREFIX_ISOLATION_LEVEL = "isolation="
static final String	PREFIX_LOCKING = "locking="

---

Member Function Documentation

Pattern commentPattern

(

)

[protected]

Returns a pattern than matches lines that start with "--".

Returns:

regex pattern that indicates this line is an SQL comment

Reimplemented from ERXSQLHelper.

String limitExpressionForSQL	(	EOSQLExpression	expression,
		EOFetchSpecification	fetchSpecification,
		String	sql,
		long	start,
		long	end
	)

Reimplemented from ERXSQLHelper.

int maximumElementPerInClause

(

EOEntity

entity

)

[protected]

FrontBase is exceedingly inefficient in processing OR clauses. A query like this:
SELECT * FROM "Foo" t0 WHERE ( t0."oid" IN (431, 437, ...) OR t0."oid" IN (1479, 1480, 1481,...)...
Completely KILLS FrontBase (30+ seconds of 100+ CPU usage). The same query rendered as:
SELECT * FROM "Foo" t0 WHERE t0."oid" IN (431, 437, ...) UNION SELECT * FROM "Foo" t0 WHERE t0."oid" IN (1479, 1480, 1481, ...)... executes in less than a tenth of the time with less high CPU load. Collapse all the ORs and INs into one and it is faster still. This has been tested with over 17,000 elements, so 15,000 seemed like a safe maximum. I don't know what the actual theoretical maximum is.

But... It looks to like the query optimizer will choose to NOT use an index if the number of elements in the IN gets close to, or exceeds, the number of rows (as in the case of a select based on FK with a large number of keys that don't match any rows). In this case it seems to fall back to table scanning (or something dreadfully slow). This only seems to have an impact when the number of elements in the IN is greater than 1,000. For larger sizes, the correct number for this method to return seems to depend on the number of rows in the tables. 1/5th of the table size may be a good place to start looking for the upper bound.

See also:

ERXSQLHelper.maximumElementPerInClause()

Parameters:

entity

EOEntity that can be used to fine-tune the result

Returns:

database specific limit, or or most efficient number, of elements in an IN clause in a statement

Reimplemented from ERXSQLHelper.

void prepareConnectionForSchemaChange	(	EOEditingContext	ec,
		EOModel	model
	)

Reimplemented from ERXSQLHelper.

String quoteColumnName

(

String

columnName

)

Reimplemented from ERXSQLHelper.

boolean reassignExternalTypeForValueTypeOverride

(

EOAttribute

attribute

)

Reimplemented from ERXSQLHelper.

void restoreConnectionSettingsAfterSchemaChange	(	EOEditingContext	ec,
		EOModel	model
	)

Reimplemented from ERXSQLHelper.

boolean shouldExecute

(

String

sql

)

Reimplemented from ERXSQLHelper.

String sqlForCreateIndex	(	String	indexName,
		String	tableName,
		ColumnIndex...	columnIndexes
	)

Returns the SQL expression for creating an index on the given set of columns

Parameters:

	indexName	the name of the index to create
	tableName	the name of the containing table
	columnIndexes	the list of columns to index on

Returns:

a SQL expression

Reimplemented from ERXSQLHelper.

String sqlForCreateUniqueIndex	(	String	indexName,
		String	tableName,
		ColumnIndex...	columnIndexes
	)

Returns the SQL expression for creating a unique index on the given set of columns

Parameters:

	indexName	the name of the index to create
	tableName	the name of the containing table
	columnIndexes	the list of columns to index on

Returns:

a SQL expression

Reimplemented from ERXSQLHelper.

String sqlForFullTextQuery	(	ERXFullTextQualifier	qualifier,
		EOSQLExpression	expression
	)

Returns the SQL expression for a full text search query.

Parameters:

	qualifier	the full text qualifier
	expression	the EOSQLExpression context

Returns:

a SQL expression

Reimplemented from ERXSQLHelper.

String sqlForGetNextValFromSequencedNamed

(

String

sequenceName

)

[protected]

Returns the SQL required to select the next value from the given sequence. This should return a single row with a single column.

Parameters:

sequenceName

the name of the sequence

Returns:

the next sequence value

Reimplemented from ERXSQLHelper.

---

Member Data Documentation

final String PREFIX_ISOLATION_LEVEL = "isolation=" [static, private]

final String PREFIX_LOCKING = "locking=" [static, private]

---

The documentation for this class was generated from the following file:

• /opt/source/Wonder/Frameworks/Core/ERExtensions/Sources/er/extensions/jdbc/ERXSQLHelper.java