Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Mango SQL database trouble
-
Hi, I have some trouble with Mango. When I make this SQL query in Mango 'SELECT * FROM events WHERE alarmlevel > 0 ORDER BY activets DESC LIMIT 10' I get a server timeout that lasts for some half an hour or so, and then it completes the query and gets it done in an instant from then on till I restart Mango. The 'EVENTS' table has following columns: ID, TYPENAME, SUBTYPENAME, TYPEREF1, TYPEREF2, ACTIVETS, RTNAPPLICABLE, RTNTS, RTNCAUSE, ALARMLEVEL, MESSAGE, ACKTS, ACKUSERID, ALTERNATEACKSOURCE'. I've deduced that, when I leave out the 'MESSAGE' column from the query, It gets completed in an instant. Also, when I leave out the 'ORDER BY' clause and keep the 'MESSAGE' column in the query, it gets done in an instant too. Has anyone had the same trouble?
Thanks, Stefan.
-
Hi Stefan, How many events do you have in the table?
-
Two thousand or so, I think.
-
Hi Joel, Have I been too vague with my question? I've tried to solve this problem since, and haven't been able to. The only thing occurring to me as the reason for this was the size of the table and message field, but my message fields don't contain too many characters, and I think 2000 inputs in a table ain't too much data to order. Have you got any ideas?
Thx, Stefan.
-
I'm not sure what is going on here but it does seem odd, maybe someone else will have some ideas but this is not something that we would normally attempt to do.
-
Well I don't think it's a too strange thing to do. I just wanna display latest alarms on a dashboard so a user could see it in DGLux instead of switching between mango alarms page and DGLux dashboard. After all, this was the solution phil or you gave me.
Do you have any other suggestions on how to display latest alarm messages in DGLux?
Thx, Stefan.
-
Hi Stefan,
I did a little googling as I'm not personally a master of fancier database queries, but I think you could rewrite your query to be a join and perhaps get it faster by simplifying the query that has to order the timestamps, as such:
select e1.* from events e1 inner join (select id from events where alarmLevel>0 order by activeTs desc limit 10) e2 on e1.id = e2.id;I'm not overly optimistic, though, as often joins are slower than direct queries.
But both queries ran quite quickly for me from the SQL console page on an events table with 32000 records. Can you try running both queries in your SQL console page, in case the slowdown is somewhere in the DGLux SQL table linkage?
-
This new query you gave me, phil, completed instantly, the old one still takes ages to complete. I've tried them both in the Mango's SQL console.
-
Glad to hear it!
