How to use string to number conversion in optimal way in redshift?
When integrating Bold BI with RedShift, users often experience a significant increase in performance compared to other combinations such as MySQL and dashboard. This article explains how to overcome this problem.
The Challenge
The challenge presents itself when a filter is applied on the SiteID column. The executed query includes a statement that results in the query timing out. The statement is as follows:
CASE
WHEN
(
"5ce906172b43eebffa19ceb4c2f4ff"."siteid"::VARCHAR ~ '^-?[0-9]+\.?[0-9]*$'
)
THEN
CAST(regexp_replace(COALESCE("5ce906172b43eebffa19ceb4c2f4ff"."siteid"::VARCHAR, '0'), '-[^0-9]+', '') AS DECIMAL)
ELSE
NULL
END
Eliminating this statement allows the query to render in under 4 seconds. However, with the statement, the query times out.
The Resolution
The performance issue is associated with the use of the COALESCE function in Bold BI. To address this, one of the following expressions can be used for converting the varchar to integer in the Expression window:
TO_NUMBER("siteid", 'FM9G999D99S' ) or CAST( "siteid" AS DECIMAL )
or
These expressions convert the varchar to integer for the ID Column, which should help to enhance the performance of your query.
Conclusion
While the COALESCE function can cause performance delays in Bold BI when connected to RedShift, there are alternative methods for converting varchar to integer that can help to improve query performance. By using these alternatives, you can prevent query time-outs and ensure your dashboard renders quickly and efficiently.
References
