-
Environment Details:
- Redash version: Version: 8.0.0+b32245 (a16f551e)
- Browser/OS: Chrome / MacOS Monterey
- Hosting VM Specs:
- Linux (ubuntu 18.04)
- 2 vcpus (last 30 days averaged 3% CPU usage with spikes up to 40%)
- 16 GiB memory (last 30 days averaged 11GB of free RAM with drops down to 9GB)
-
All queries or some: It doesn’t affect all queries, but it nearly always affects the affected queries. Meaning, if a query has the issue, it basically always has the issue. (I have observed some rare situations where the same query works)
-
Reproducible: Because it happens basically every time, it is easy to recreate. I have seen it more often in MSSQL queries that have comments, but that isn’t always true for either success or failure
-
All sources or one: happens mostly on MSSQL sources, but is probably because we have more MSSQL queries and it can happen with any/most
Also, when it fails, it often fails very quickly, which makes me think it isn’t a resources issue, but may be something in how it is parsing the code.
I am providing the current query below in case there is something in the code that is incompatible with Redash.
NOTE: We use Temp Tables to keep the data free and reduce processing time
DECLARE @ReportId varchar(100) = 'ShopCQ_UserDemandLinesSentBySupplierandDate'
DECLARE @last datetime
SELECT @last = MAX(ReportDate) FROM dbo.CommonQueries WHERE ReportId = @ReportId
IF OBJECT_ID('tempdb..#SendHistoryTemp') IS NOT NULL DROP TABLE #SendHistoryTemp
SELECT ReportDate,ServerName,Instance
,CAST(col1 as date) Period
,DATEPART(day, CAST(col2 as date)) dayOfMonth
,col3 Supplier
,col4 UserNAme
,CAST(col5 as int) DemandSentCount
,ROW_NUMBER() OVER (PARTITION BY Instance,CAST(col1 as date) ORDER BY DATEPART(day,CAST(col2 as date))) SendOrder
INTO #SendHistoryTemp
FROM dbo.CommonQueries
WHERE reportid = @ReportId
AND ReportDate = @last
AND ServerName <>'_Header'
AND CAST(col2 as date) >= '2022-01-01'
IF OBJECT_ID('tempdb..#dailyTemp') IS NOT NULL DROP TABLE #dailyTemp
SELECT Period,
d.dayOfMonth
,SUM(DemandSentCount) TotalSent
,COUNT(DISTINCT Instance) NewInstances
,SUM(COUNT(Instance)) OVER (PARTITION BY Period ORDER BY d.dayOfMonth) MnthProgress
,SUM(SUM(DemandSentCount)) OVER (PARTITION BY Period ORDER BY d.dayOfMonth) MnthLinesProgress
INTO #dailyTemp
FROM (SELECT dayOfMonth FROM #SendHistoryTemp WHERE SendOrder = 1 GROUP BY dayOfMonth) d
LEFT JOIN #SendHistoryTemp st ON d.dayOfMonth = st.dayOfMonth
WHERE SendOrder = 1
GROUP BY d.dayOfMonth,Period
SELECT a.dayOfMonth
,COALESCE(c.TotalSent,0) TotalSent
,AvgSent
,SUM(COALESCE(c.TotalSent,0)) OVER (ORDER BY a.dayOfMonth) MonthSent
,a.NewInstances AvgNewInstances
,COALESCE(c.NewInstances,0) NewInstances
,AveMonthEngaged
,SUM(COALESCE(c.NewInstances,0)) OVER (ORDER BY a.dayOfMonth) MonthEngaged
FROM (SELECT dayOfMonth
,AVG(MnthLinesProgress) AvgSent
,AVG(NewInstances) NewInstances
,AVG(MnthProgress) AveMonthEngaged
FROM #dailyTemp
GROUP BY dayOfMonth
)a
LEFT JOIN (SELECT dayOfMonth,COUNT(DISTINCT Instance) NewInstances, SUM(DemandSentCount) TotalSent FROM #SendHistoryTemp WHERE SendOrder = 1 AND Period = DATEADD(d,1,EOMONTH(GETDATE(),-1)) GROUP BY dayOfMonth) c ON a.dayOfMonth = c.dayOfMonth
IF OBJECT_ID('tempdb..#dailyTemp') IS NOT NULL DROP TABLE #dailyTemp
IF OBJECT_ID('tempdb..#SendHistoryTemp') IS NOT NULL DROP TABLE #SendHistoryTemp
