SQL query taking longer time to execute than expected time using Hibernate SQL Query

Question

SQL Queries sometimes become a heck to handle. So I have a SQL Query consisting multiple OR operations and REGEX. While running through JAVA code execution time is more than the expected time on the huge entries of data.

Below is the query:

SELECT * FROM tableName WHERE (col1 REGEXP ('xyz'|'abc') OR (col2 = 15 AND (col1 REGEXP ('xyz'|'abc')))) AND col3>='2017-08-28' AND col3<='2017-08-30';

Hibernate Code:

public List<MyModel> getAllMatchedEntries() {
        long lStartTime = new Date().getTime();
        Query query = ((SQLQuery) getSession().createSQLQuery( "SELECT * FROM tableName WHERE (col1 REGEXP :list OR (col2 = 15 AND (col1 REGEXP :list))) AND col3>=:sd AND col3<=:ed"). setResultTransformer(Transformers.aliasToBean(LeadGenViewTable.class)).setParameter("list", regexlist).setParameter("mid", merchant_id).setParameter("todayDate", sd).setParameter("ed", ed));
        List<MyModel> list = query.list();
        long lEndTime = new Date().getTime();
        long output = lEndTime - lStartTime;
        list= list.stream().filter(distinctByKey(t -> t.getSomeCol())).collect(Collectors.toList());
        System.out.println("Time Taken "+output);
        return list;
    }

And Surprisingly Output in logs:

Time Taken 45616

I'm facing a hard time figuring out the time lag happening either because of REGEXP / OR operations. Help would be appreciated.

Run a `EXPLAIN` and it may provide some inputs on the data filtering done by the query. You can also try using `BETWEEN` for the `col3` condition. — Aniket V, Aug 30 '17 at 10:35
how did you calculate the expected time? have you run the exact same query directly against the database? if you use hibernate, you should never have to write a plain sql query — XtremeBaumer, Aug 30 '17 at 10:36
@XtremeBaumer Please check the updated question. And yes running the same query is giving me result in 2 secs on the same data. Reason to write plain sql query was REGEX as I couldnt find the best way to implement regex using criterias or hql. — Ankita Goyal, Aug 30 '17 at 10:41
@AnkitaGoyal [this is a solution](https://stackoverflow.com/questions/25747770/regular-expression-with-criteria) which might work. [another example](https://stackoverflow.com/questions/4337122/how-to-use-regular-expressions-with-hibernate-oracle) or you extend you dialect like [this](https://stackoverflow.com/questions/17702544/hibernate-regexp-mysql/17702545#17702545) — XtremeBaumer, Aug 30 '17 at 10:47

score 0 · Answer 1 · answered Aug 30 '17 at 10:43

(col1 REGEXP :list OR (col2 = 15 AND (col1 REGEXP :list)))

is the same as

(col1 REGEXP :list)

Have an index on col3

And a regex can often better be rewritten - then utilising index:

col1 REGEXP ('xyz'|'abc')
col1 IN ('xyz', 'abc')
(col1 = 'xyz' OR col1 = 'abc')

For the IN version one can use a java.sql.Array as parameter.

SQL query taking longer time to execute than expected time using Hibernate SQL Query

1 Answers1