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
– Aniket V, Commented Aug 30, 2017 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
– XtremeBaumer, Commented Aug 30, 2017 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
– Ankita Goyal, Commented Aug 30, 2017 at 10:41
@AnkitaGoyal this is a solution which might work. another example or you extend you dialect like this — XtremeBaumer
– XtremeBaumer, Commented Aug 30, 2017 at 10:47

Joop Eggen · Accepted Answer · 2017-08-30 10:43:52Z

0

(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.

answered Aug 30, 2017 at 10:43

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related