Quicksight Parameters in Custom SQL taking too long

Hi Quicksight team, I am working on a dashboard migration from tableau, and it requires using two parameters in the custom sql. I am running into an issue because SPICE will not allow me to import with parameters, but direct query gives an issue saying the visualization is taking too long to process. I have also added a distribution key on the id, and sortkeys on calendar day and marketplace to optimize this, but I believe the query is still too complex to load. For reference each table has around 2 billion rows. The ids for both tables are used as a text field in the visualization so that users can compare the two revenues and other metrics.

WITH table1 AS (
    SELECT *
    FROM table1
    WHERE id = <<$ID1>>
),
table2 AS (
    SELECT *
    FROM table2
    WHERE id = <<$ID2>>
)

SELECT 
    nvl(s.id, t.id) as id,
    nvl(s.marketplace_id,t.marketplace_id) as marketplace_id,
    nvl(s.entity,t.entity) as entity_id,
    nvl(s.calendar_day,t.calendar_day) as calendar_day,
    child_account,
    case when t.id is null then 'Y' else 'N' end as flag1,
    case when s.id is null then 'Y' else 'N' end as flag2,
    sum(nvl(s.revenue_usd,0)) as revenue1,
    sum(nvl(t.revenue_usd,0)) as revenue2
FROM table1 s
FULL OUTER JOIN table2 t
    ON s.id = t.id
    AND s.marketplace_id = t.marketplace_id
    AND s.entity = t.entity
    AND s.calendar_day = t.calendar_day
group by 1,2,3,4,5,6,7```

@johndude ,

direct query gives an issue saying the visualization is taking too long to process

If your database requires more than 2minutes to execute a query, then it will timeout in QuickSight. here is a similar post and possibilities to resolve ( Direct Query with Parameters Timeout )

Run the same query in your database and check the time required and then look at optimization either by scaling the datawarehouse , maybe define a timeframe in the where clause additionally to reduce scanning of data .

Kind regards,
Koushik

1 Like

Hi @johndude,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @johndude,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!