I am in the process of trying to collect high speed historical data to an MSSQL database. We would like to collect data to set up a predictive maintenance system. We would like to collect information like temperature, vibration, voltage, current, etc. on CNC machines on a per machine cycle/part cycle basis at a 100mS sample rate. We would then like to compare the charts based on machine/part cycle to set thresholds and detect abnormalities before they become breakdowns.
We are collecting all of our values to multiple arrays in a PLC and then copying the information to the server. I have a python script set up to copy 200 blocks of the array in a single SLQ query to a “long” table.
It has been suggested to me to have the information written to the table in a wide format to cut down on network traffic. Would it be a more efficient write if it is still a single query with the same amount of information?
Are there benefits to writing to a wide table? I just see more work with pivots and unpivots down the road.
I am still in the planning phase, so I am very open to suggestions if someone has had any experience with a similar project and is willing to share.
You can do a bulk insert for a tall table with all of the values you want to write that is one message to the database server so network traffic shouldn’t be an issue.
INSERT INTO dataTable
VALUES
(1, value1),
(2, value2),
(3, value3)
I am a fan of tall tables but they can both work if implemented correctly.
Worrying about the database or the network itself as the limiting factor here reeks of premature optimization, to me. Databases are specifically built to handle rapid data ingestion, and your latency to your database should be in the single milliseconds, over a wide enough pipe that it makes no difference how your data is structured.
Much more likely limiting factors are:
Getting data out of your PLC - 100ms is not a lot of time to extract data, and 200 * X pieces of information on each machine adds up fast in terms of time needed for the PLC to actually read that data and return it to Ignition.
Actually processing that data meaningfully within Ignition. There is a bare minimum overhead to executing a Python (Jython) script within Ignition that’s a meaningful fraction of your 100ms budget. You may want to look into transaction groups instead of scripting - they’re going to be more maintainable and probably more performant than running your own code through our environment.
In general: don’t worry about the database (beyond getting your data into it, structured in the way that’s best for you to work with) or the network - focus on the PLC and Ignition first, because they’re much more likely to be your bottleneck.
As the saying goes - Make it Work, Make it Right, Make it Fast - in that order.
I generally make tables wide enough to permit related values to share a row in a table with a single timestamp column. This is the classic transaction group. If multiple entities share that specific structure, I include a column to indicate which entity to minimize the proliferation of tables. That would be the block transaction group, though I don’t usually use it – I prefer to trigger individual rows as needed. Both of these approaches are extremely friendly to external data analysis, but need to be charted with DB pens.
Any value that is truly independent goes into the regular tag historian (tall tables). This is brain-dead easy to chart.
Note that I decide based on the use of the data, and make my infrastructure conform.
I tried to look into transaction groups and every time I try to do a Block group it fails. Every other type works. This is what the message reads:
Unable to start group. java.lang.NullPointerException: null at com.inductiveautomation.ignition.common.tags.upgrade.LegacyTagPropUpgrader.processTagProps(LegacyTagPropUpgrader.java:117) at com.inductiveautomation.ignition.common.tags.upgrade.LegacyTagPropUpgrader.upgradeLegacyDefinition(LegacyTagPropUpgrader.java:107) at com.inductiveautomation.factorysql.groups.blockgroup.BlockGroup.initializeBlockItem(BlockGroup.java:218) at com.inductiveautomation.factorysql.groups.blockgroup.BlockGroup.instantiateItemsForExecution(BlockGroup.java:145) at com.inductiveautomation.factorysql.groups.AbstractItemExecutionGroup.configureForExecution(AbstractItemExecutionGroup.java:308) at com.inductiveautomation.factorysql.groups.AbstractTableItemGroup.configureForExecution(AbstractTableItemGroup.java:78) at com.inductiveautomation.factorysql.groups.AbstractGroup.startExecution(AbstractGroup.java:316) at com.inductiveautomation.factorysql.ProjectRunner.loadGroupResource(ProjectRunner.java:281) at com.inductiveautomation.factorysql.ProjectRunner.lambda$onResourcesModified$2(ProjectRunner.java:173) at java.base/java.util.ArrayList.forEach(Unknown Source) at com.inductiveautomation.factorysql.ProjectRunner.onResourcesModified(ProjectRunner.java:173) at com.inductiveautomation.ignition.gateway.project.ProjectLifecycle$LifecycleResourceListener.resourcesModified(ProjectLifecycle.java:176) at com.inductiveautomation.ignition.common.project.AbstractProject.notifyResourceListeners(AbstractProject.java:349) at com.inductiveautomation.ignition.common.project.AbstractProject.updateEffectiveState(AbstractProject.java:151) at com.inductiveautomation.ignition.common.project.RuntimeProject.applyChange(RuntimeProject.java:271) at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.lambda$updateOrStartAffectedProjects$5(ProjectLifecycleFactory.java:181) at java.base/java.util.ArrayList.forEach(Unknown Source) at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.updateOrStartAffectedProjects(ProjectLifecycleFactory.java:161) at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.projectUpdated(ProjectLifecycleFactory.java:139) at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.fireProjectUpdated(BaseProjectManager.java:811) at java.base/java.lang.Iterable.forEach(Unknown Source) at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.push(BaseProjectManager.java:516) at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.Projects.push(Projects.java:218) at jdk.internal.reflect.GeneratedMethodAccessor54.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225) at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:409) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61) at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) at org.eclipse.jetty.server.Server.handle(Server.java:530) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626) at java.base/java.lang.Thread.run(Unknown Source)