Hi there,
I have switched my data set to an incremental refresh, however from what I can see the time it takes to refresh the dataset despite it only ingesting 24 rows takes the same amount of time as it did on a full refresh. Is there anyway to speed the duration of the refresh or is it more of an issue with the query via aurora.
Thanks,
Joe
Hi @JSharman
For a dataset this small it appears to be taking a lot of time.
You should look at this from the database side to see why the underlying query is taking so long. It seems as if the field that you are using for the incremental is not driving any change in the execution path in the database; may be it does not have an index on this column?
QuickSight is not the problem here.
Regards,
Giri
Hi Giri,
My understanding of incremental refresh might be incorrect, when I refresh the dataset on an incremental level I though it to basically run the query of the dataset based on the date fields set in the incremental. Regardless of what date filters I have in the query.
So if my dataset had a where filter of
purchase_date BETWEEN ‘2023-04-13 00:00:00’ AND NOW()
And the refresh window looking back 1 day on purchase date.
I thought my query would now run quickly as it is only looking back one day.
Is this the wrong way to think about it?
Hi @JSharman
Logically, you are right. It will try to query for the refresh window.
Technically though the query executed at the database level will be somewhat like this
SELECT * FROM
(
SELECT *
FROM X
WHERE purchase_date between ‘2023-04-13 00:00:00’ AND NOW()
)
WHERE purchase_date > CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1716521445603 as char(10)) as decimal) SECOND), @@session.time_zone,‘UTC’)
The outer query is introduced by QuickSight.
You need to look at the explain plan for the QuickSight refresh query that is executed at the database to see what is happening.
I had experienced something similar in the past where my query worked fine; but when the QuickSight runs the refresh it was taking long due to the above mentioned change that QuickSight has to do to the query to query for the refresh window. It cause a change in the explain plan. I had to simplify the query to improve the performance.
Regards,
Giri
