Incremental dataset refresh taking too long

Question & Answer
,
1

Hi All,

We have a dataset using Custom SQL. There is an incremental update scheduled everyday with a look back window of 2 days.

The underlying databse is mySQL.

The incremental refresh generally takes 1 to 2 minutes to complete. A database upgrade was done in our AWS instance and post that update this incremental refresh has started taking 56 minutes with no change in the dataset.

We captured the query that QuickSight triggers. The filter condition for the look back window seems to confuse the optimizer and though the end result is that we only get the incremetal records but one of the main tables involved in the sql is going through a full table scan

This is the filter criteria and this causes the query to run 56 minutes
WHERE flight_date > CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1716521445603 as char(10)) as decimal) SECOND), @@session.time_zone,‘UTC’)

When I produce the expression for look back window with the literal value the query returns in 90 seconds
WHERE flight_date > > ‘2024-05-24 03:30:45’

Has anyone encountered a similar issue in the past? Any workarounds/solution to overcome this issue?

2

Hi,

I was faced the same issue. My views are going to take more than hour and before MYSql upgrade it was taking only 1/2 minutes.

I drill down it and find out that sub queries were not supported are the upgrade at MySql level which was the cause to incrust the incremental time.

In my case I was using

Select id, name, (select address from address table where empid = abc.empit) as address
,city
from abc.

to fix this I create the join in query. After that my refresh time goes back to 1 to 2 min.

Quicksight has no role in increase time its about performance running of database.

After upgrade some function goes absolute that why customer face the issues.

Any further support required let me know.

Regards
Naveed Ali

1 Like
3

Thanks Naveed for your inputs

1 Like
4

Hi @Giridhar.Prabhu,
It’s been awhile since we last heard from you; checking back in to see if you have any additional questions regarding your original topic or was Naveed’s response helpful?

If we do not hear back from you within the next 3 business days, I’ll mark the solution.

Thank you!

5

Thanks @duncan for following up. I re-arranged my query to resolve the issue.

1 Like