Is there a way to improve the performance of Hibernate-generated queries when stored functions are involved?

88
February 27, 2019, at 12:50 PM

I have a page which shows some records in a grid in a paged manner. I have the table my_table and the entity MyTable linked to it:

@SuppressWarnings("serial")
@Entity
@Table(name="my_table")
@Inheritance(strategy=InheritanceType.JOINED)
public class MyTable extends BaseEntity implements Auditable, Serializable {
    //...
    private Integer myAttribute; //This does not exist in the table
    //...
    @Formula(value = "(myFunction(attr1, attr2))")
    public Integer getMyAttribute() {
        return myAttribute;
    }
    public void setMyAttribute(Integer myAttribute) {
        this.myAttribute = myAttribute;
    }
    //...
}

When I intend to query by "normal" fields, everything works greatly, but when I attempt to filter by myAttribute, like:

queryInput.addAndCriterion(Restrictions.eq("myAttribute", v));

where for instance v is an Integer with the value of 123, the search will time out. If I run the stored function in MySQL directly, then it is executed instantly. I think this code sends a separate request on each item, which could explain the problem. Is there a way to ensure that I can filter by my stored function in a performant way (maybe the call for the stored function will be generated into the query)? I would need to define a criteria which specifies that for each record a certain stored function needs to be called and attr1 and attr2 are passed, which are fields of the records?

Answer 1

As noted in the comment below, this answer doesn't fix the problem of troubleshooting Hibernate, but the OP liked it anyway.

Answer follows...

Querying on any function, a stored function or a builtin function, is always a table-scan.

For example this would not be able to use an index on create_date, even if one existed:

SELECT * FROM MyTable WHERE MONTH(create_date) = 2

The same is true anytime you use an indexed column as arguments to a function.

The workaround for MySQL 5.7 and later is to use a generated column for that expression, and then index the generated column.

ALTER TABLE MyTable
  ADD COLUMN created_month INT AS (MONTH(create_date)),
  ADD INDEX (created_month);

Once you do that, you can query for created_month = 2 or you can even query the original expression MONTH(create_date) = 2 and it will use the index.

Unfortunately, you can use this feature only with builtin MySQL functions.

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html says:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

  • Stored functions and user-defined functions are not permitted.

An alternative solution would be for you to create a new concrete column to store the result of the stored function, supposing the value is deterministic from its arguments and doesn't depend on the state of data in other tables.

ALTER TABLE MyTable
  ADD COLUMN myAttribute INT,
  ADD INDEX (myAttribute);
CREATE TRIGGER att_ins BEFORE INSERT ON MyTable 
FOR EACH ROW SET NEW.myAttribute = MyFunction(NEW.attr1, NEW.attr2);
CREATE TRIGGER att_upd BEFORE UPDATE ON MyTable 
FOR EACH ROW SET NEW.myAttribute = MyFunction(NEW.attr1, NEW.attr2);

Then you would query the new column instead of the expression.

That's kind of a hassle, but it's the only way to get an indexed lookup against the result of your stored function.

READ ALSO
Select rows in one table based on an id that appears in a column on a different table AND have multiple matches based on another column

Select rows in one table based on an id that appears in a column on a different table AND have multiple matches based on another column

This is a very difficult question for me to formulate so the title may need reviewBut here's my situation:

76
MySQL sequentially split results based on 2 columns

MySQL sequentially split results based on 2 columns

I'm displaying results from a MySQL table based on matching values in one columnI then need to separate these results based on the data in another column, when that data is different

122
Can I use MySQL community edition for an company for free? [on hold]

Can I use MySQL community edition for an company for free? [on hold]

I am currently working as a freelancerI got a job for a small company that needs an Android app and a desktop app made

63
How to fix “Warning: illegal string offset”

How to fix “Warning: illegal string offset”

Previously I tried translating the php pdo code to my php version, but I got a problem like this"Warning: Illegal string offset 'tagName' in

113