Hello, I need some help
I am an Intern working on this project where I have to develop a dashboard to read mssql data for plc in node red, I just started learning node red and I'm stuck at this problem, the issue is for this particular flow, there is an error
When I inject the data manually it shows the output is fine, but when I set the interval it shows this error.
I used node-red-contrib-mssql
Welcome to the forum Dar.
Can you share some more info?
What is the interval you are using?
Are you using a prepared SQL statement? How complex is the statement?
Thank you for repying
I don't know if this will help, please tell me if I need to share some more.
[
{
"id": "630bdb1c4f13eafc",
"type": "inject",
"z": "8a3e2aefb4cecb49",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "10",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 150,
"y": 220,
"wires": [
[
"f8dc1780c5b633ec"
]
]
},
{
"id": "51b0c07ad2c67bb5",
"type": "debug",
"z": "8a3e2aefb4cecb49",
"name": "debug 2",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 880,
"y": 220,
"wires": []
},
{
"id": "f8dc1780c5b633ec",
"type": "function",
"z": "8a3e2aefb4cecb49",
"name": "function 10",
"func": "msg.payload = \"SELECT TOP (10) * FROM dbo.SA03 WHERE Tag = 'CH.DV01.MAIN01.Speed' ORDER BY DateTime DESC\";\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 220,
"wires": [
[
"07853a5498b3965f"
]
]
},
{
"id": "5483a2755cbf1c1f",
"type": "ui_table",
"z": "8a3e2aefb4cecb49",
"group": "eb8ac8c960fbfe51",
"name": "",
"order": 1,
"width": 30,
"height": 6,
"columns": [],
"outputs": 0,
"cts": false,
"x": 890,
"y": 320,
"wires": []
},
{
"id": "07853a5498b3965f",
"type": "MSSQL",
"z": "8a3e2aefb4cecb49",
"mssqlCN": "aba6c4089a8cbd85",
"name": "Speed",
"query": "",
"outField": "payload",
"x": 550,
"y": 220,
"wires": [
[
"51b0c07ad2c67bb5",
"5483a2755cbf1c1f"
]
]
},
{
"id": "eb8ac8c960fbfe51",
"type": "ui_group",
"name": "Speed",
"tab": "959e32e81beb4ff7",
"order": 2,
"disp": true,
"width": 30,
"collapse": false,
"className": ""
},
{
"id": "aba6c4089a8cbd85",
"type": "MSSQL-CN",
"name": "i4.0",
"server": "Delta`Preformatted text`",
"encyption": true,
"database": "i4.0"
},
{
"id": "959e32e81beb4ff7",
"type": "ui_tab",
"name": "Delta SA 03",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
So you are asking the db server every 10 seconds(?) to dynamically calculate the "top 10" entries from the db and return all of the fields?
I suspect that you are hitting some kind of processing limit.
If "top 10" means the newest 10 entries, you've already sorted by date/time so you only need to "LIMIT 10". I assume that the DateTime field is indexed though because if it isn't, that is still a fair load on the server, especially if the table is large.
You should also list the fields you want rather than making the server work it out each time.
Better still, you should use a prepared statement with the above optimisations so that the server hasn't got to compile the query each time.
My SQL is rather rusty so I think I've got this right and I don't know if it will actually fix the issue but it should at least help.
If it doesn't, you will have to slow down the interval until you stop getting the errors.
BTW, using a straight SQL table for processing timeseries data is sub-optimal. Not sure if MS SQL has a timeseries optimised table format, I believe Postgres does. Most people use a dedicated timeseries db server though.
How many records are in the SA03 table?
What indexes are defined for this table?
I'll give it a try but for the interval, I already adjusted it but it is not working
Top 10 doesn't seem to make much sense looking at the data. Too many entries with the same timestamp.
As JBudd say's you really need to understand what indexes have been applied. With any db, indexes are make-or-break for performance.
I don't use MS SQL - does the driver have a connection pooling option?
How big did you make the interval? Did you try a few minutes for example? Just to see if you are hitting db server limits.
I would strongly recommend you do not use that node.
I would suggest you switch to node-red-contrib-mssql-plus
Here is why:
The OP is using the (very) old contrib. node-red-contrib-mssql-plus does support pooling (and stored procs and prepared statements)
Thanks Steve, sage advice 
I'm not an mssql user so i don't really know how to interpret the picture you posted.
To find out how many records are in the table you could run (from Node-red)
SELECT COUNT(*) FROM dbo.SA03
BUT (according to https://codingsight.com/how-to-count-number-of-rows-in-sql-server-table/ this will lock the table until it returns (surely only a millisecond or two?) so not ideal on a production database.
Instead, they suggest (option 4) you can get the row count from SQL Server Management Studio, which may be the app you posted a screenshot from.
The link also discusses how to see the number of logical reads used by a query. This is essential information for tuning your query.
The screenshot does show on the left a category "Indexes" which will show you how the table is indexed.
I am pretty sure that you do need the ORDER BY clause, otherwise the 10 records returned cannot be relied on as the most recent.
The example top 1000 statement in your picture though omits this clause. Seems bad form to me but as I said, I don't use mssql.
And @TotallyInformation's LIMIT 10 is invalid syntax for mssql (?)
A minor point - you show that the query was run at 3:57:09, 3:57:10 and 3:57:20.
If you are running it every 10 seconds, you don't also need "Inject once after 0.1 seconds" ticked.
Thank you for the reply steve, I did tried to use the node-red-contrib-mssql-plus node a few times but I failed to get the node to even make a connection to the MSSQL database, I probably made a mistake somewhere when filling in the information. The tutorial from youtube don't have much about the plus version, I might need some guidance on how to convert from old node to the new one, If you can give some guidance that would be very much appreciated.
@jbudd Thank you for the information, I want to ask If I made a separate table in mssql solely for speed tag, do you think it will help?
@TotallyInformation thank you, I'll give it a try
No I don't but as I said before, I'm not an mssql user.
Did you find out record counts and indexes?
It was my off day before so I don't get the chance, but I will be meeting the admin today and I will get back to you later
So I tried doing this and it worked but once in a while the debug output shows "ConnectionError: Connection is closed." it was probably still the issue with the database, I will try to find out about the record counts and index.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
