optimize the query

Database

optimize the query

The following query takes a long time to execute because the table has lots ( Millions of records)  of data so i would like to optimize the query so  the execution will be faster and i will get results faster.

when i execute the following query then it hangs or takes long time to execute and quit.

Can any one help me to resolve the issue please?

SELECT expired_calls.* from

                   (SELECT distinct c.* FROM laes_calls c, laes_messages m, retention_policies r, recordable_entities e

                    WHERE ((c.id = m.laes_call_id) AND (c.retention_policy_id = r.id) AND (c.case_id = e.id)

                    AND ((TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > r.call_retention_days)

                    AND (r.call_retention_days > 0)

                    AND ((r.eval_retention_rule = 'DELETE_CALLS_AND_EVALS') OR (r.eval_retention_rule = 'DELETE_CALLS_KEEP_EVALS')

                      OR ((r.eval_retention_rule = 'KEEP_CALLS_AND_EVALS')

                       AND (c.id not in (select c2.id from laes_calls c2, performance_evaluations p where c2.id = p.call_id) ) ) )

                    AND (e.legal_hold = 0)

                    AND (c.id >1) )

                    UNION

                  SELECT DISTINCT c.* FROM laes_calls c, laes_messages m

                   WHERE ((c.id >1) AND (c.save = 0) AND (m.laes_call_id = c.id) AND ((TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > 1))

                    UNION

                   SELECT DISTINCT c.* FROM laes_calls c, laes_messages m, release_messages r

                    WHERE ((c.id >1) AND (c.save = 0) AND (m.laes_call_id = c.id) AND  (m.id = r.id))

                    ) AS expired_calls

                   ORDER BY expired_calls.id ASC;

 

 

 

Tags (1)
7 REPLIES
Enthusiast

Re: optimize the query

first, you can try to run the queries separately instead of union and see which one is taking more time. Also, you can try to break the 1st query into simple queries and create temporary tables and use the tables to join with other tables.

Re: optimize the query

I tried to seperate queries. First query runs too slow and timeouts when I have few millions of rows. other queries are fine. Can you suggest any changes in first query to make it faster? I already have indexes, still takes 5500 sec to run.

Junior Contributor

Re: optimize the query

Without DDL and Explain it's hard to tell.

NOT IN in an ORed condition might be bad, you probably don't need the join and can rewrite it with a simple NOT EXISTS:

OR NOT EXISTS (select * from performance_evaluations p where c.id = p.call_id) ) )

What is TO_DAYS, a UDF? You don't need it to calculate the number of days between two dates, this is not MySQL.

Btw, this is not the actual query as TIMESTAMP is a keyword and will return a syntax error.

Teradata Employee

Re: optimize the query

In most of the above joins the tables are related via the .id field. In the first query m is joined to r only on the inequality on the date expression which will force a full product join comparing every row of m to every row of r. If either or both of those are any size, it will take a while.

Re: optimize the query

Thank you Dnoeth, 

I made change as you suggested OR NOT EXISTS (select * from performance_evaluations p where c.id = p.call_id) ) ). which is little better. but still very slow. 

Re: optimize the query

Thanks ToddAWalter, you are correct. I need to go to every tow and calculate days.

(TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > r.call_retention_days)

is there any way I can make it faster? I use this 2 times in one query.

Enthusiast

Re: optimize the query

Same is the case with my query ...where i had used 3 unions....the query get timeout...

Is there any way out of this to handle it