Configuring S7-1500 100 Hz Data Acquisition to SQL Server

1. Problem Definition and Throughput Boundary

A typical 100 Hz acquisition task generates a continuous stream of process values from a small number of fast sensors (analog inputs, encoders, position controllers, weighing cells). For a payload in the range of 3.5 to 4 kB/s at 100 Hz, each individual sample set must fit inside a 10 ms window, and the sum of all write attempts must not exceed the available bandwidth on the link between the PLC and the database host.

The decision tree that follows assumes the architecture has not yet been frozen, so the engineer must answer three orthogonal questions before commissioning begins:

1. Which S7-1500 CPU class and which firmware generation can deterministically execute a 10 ms time-of-day or cyclic interrupt?
2. Which I/O topology keeps the scan-to-process-image time well below 10 ms (local I/O, PROFINET IRT, direct slot access with the :P suffix)?
3. Where should the value be timestamped and persisted — in the PLC as a row per cycle, in the PLC as a bulk blob, or in a PC application polling the PLC?

The guidance below prefers deterministic engineering trade-offs over "best-effort" recommendations. Wherever the source material is ambiguous (for example, the exact data type and word count per sample), the alternative interpretations are listed and the consequences are analyzed.

2. S7-1500 CPU Selection and Firmware Constraints

For a 100 Hz cycle, the S7-1500 family is the correct platform. S7-1200 CPUs have a configurable cycle time floor that is generally too coarse for hard real-time at 10 ms while simultaneously running communication, and they do not provide the same range of high-speed I/O modules. The S7-1500 also gives access to the LSql library and to time-stamping blocks (DTL, LTime) that go down to nanosecond resolution.

Use the following selection logic:

Set the PLC to the highest supported firmware in the engineering project, because LSql, the high-speed counter of the technological objects, and the :P direct I/O access all gained performance or capability with later firmware versions. Refer to the Siemens Industry Online Support pages for the precise firmware/KB matrix for your selected CPU.

3. Cyclic Time Interrupt (OB3x) and the 10 ms Time Slice

Configure a Time-of-Day Interrupt (OB10) or a Cyclic Interrupt (OB30..OB38) with a 10 ms period. The choice depends on whether the acquisition must be phase-locked to a wall clock or to a free-running 10 ms tick:

• OB10 (Time-of-Day): use when the customer requires a fixed start time on the absolute clock (for example, aligned with a shift boundary).
• OB30..OB38 (Cyclic): use when the acquisition must be free-running with a fixed period. OB30 = 1 ms, OB35 = 100 ms (default in TIA Portal). To obtain 10 ms, use OB33 and configure the phase offset in the OB properties dialog.

4. Direct I/O Access with the ":P" Suffix

When an OB reads the inputs, the default behavior is to read from the process image (PAA/PAB/PAE/PAW). That image is refreshed at the start of OB1 (or at the configured update cycle). To bypass the image and read the value of the I/O module at the very moment the OB executes, use the :P suffix on the I/O address:

// Direct read of channel 0 of AI 4xU/I HS in slot 4, channel word IW112
// L IW112                  // reads the I/O image word
// L IW112:P                // reads the physical input value of the I/O right now

Benefits at 100 Hz:

• Removes the jitter caused by the OB1 process image update offset.
• Reduces the latency between the analog-to-digital conversion and the OB3x read, which in turn reduces timestamp error on the PC side.
• Allows you to use input modules whose channel conversion time is shorter than the OB1 cycle (HS = High Speed, HF = High Feature).

Restrictions:

• :P access is only valid on local I/O slots and on distributed I/O that supports isochronous read.
• For PROFINET, the field bus must be set to IRT (Isochronous Real-Time) with the slot configured as isochronous. Otherwise the :P read still goes through the bus jitter window.

5. I/O Module Selection: HS vs. HF

Siemens uses two suffixes for analog modules in the ET 200SP / ET 200MP / S7-1500 range:

If a channel is on local I/O, the fastest stable update the OB will see is approximately 1 ms per channel, even on an HS module. This is the floor imposed by the backplane scan. If a single acquisition requires 4 channels, plan for at least 4 ms of analog conversion per OB execution. If that is too slow, migrate the fast channels to a PROFINET IRT island using an ET 200SP HF/HS head module and configure the slot as isochronous.

6. Network Architecture and Timestamp Strategy

The 100 Hz data must traverse the link from the PLC to the database host. There are two practical physical arrangements:

1. Shared plant network: the PLC and the SQL PC are both connected to the same managed switch fabric as the rest of the plant. The packet from the PLC must compete with HMI traffic, peer-to-peer I/O, and external IT traffic.
2. Point-to-point link: the PLC and the SQL PC are connected by a dedicated cable (or a small dedicated switch with QoS). No other device is on that segment.

The point-to-point arrangement is the engineering default for high-speed logging because it removes the collision window that would otherwise force you to use TCP retransmissions to guarantee delivery. Combined with a direct link, the 100 Hz TCP or UDP packets can be timestamped at the receiver with sub-millisecond accuracy relative to the PLC clock.

If the customer refuses a dedicated cable, use an S7-1500 with a separate gigabit PROFINET interface (X2) for the logging traffic, leaving the X1 interface for plant communication. The decision matrix looks like this:

7. Timestamp Strategy Inside the PLC

Do not rely on the SQL server's GETDATE() to give the acquisition instant. Use a 64-bit PLC timestamp and write it explicitly into the row:

// Read the system clock (UTC) as LTime (nanoseconds since 01.01.1970)
#tPLC := RD_SYS_T_LTIME;   // 64-bit nanosecond timestamp
// Convert LTime to a 64-bit integer (DInt) or to a high-resolution DTL
// Store as:  - INTEGER8 nanoseconds
//             - INTEGER2 microseconds
//             - INTEGER4 seconds since epoch (Unix time)
// Or compose a single 8-byte array and write it as VARBINARY

Use the IEC standard LTime (64-bit nanoseconds) for high precision. When LSql or OPC UA is used, this timestamp must be packed into a string or a byte array, which is the source of the throughput penalty discussed in the next section.

8. Data Transfer Strategies Compared

There are three realistic data-transfer strategies. The choice is dictated by the data volume, the tolerance for timestamp jitter, and the available PC-side software.

For the 3.5 to 4 kB/s envelope, Strategy A (LSql) is theoretically possible but inefficient. LSql uses SQL text queries, so the wire payload is at least three times the raw binary payload once values are converted to ASCII. Strategy B (PC polls, writes one row at a time) keeps the timestamp on the receiver and is straightforward to debug. Strategy C (PC reads a buffer every 1-2 s and inserts 100 to 200 rows at once) is the best engineering compromise because it shifts the timestamp source back to the PLC clock and keeps the SQL insert load at one transaction per second.

9. LSql Library: Where It Works and Where It Hurts

The LSql library is part of the standard TIA Portal installation and provides function blocks that build SQL statements inside the PLC and send them via the integrated PROFINET interface. Typical blocks include LSql_Connect, LSql_Execute, LSql_Fetch, and LSql_Disconnect. The library is documented under the "Siemens SIMATIC S7-1500 / ET 200SP" library set on the Siemens Industry Online Support portal.

LSql characteristics relevant at 100 Hz:

• String conversion: every numeric value is converted to a string before the SQL statement is sent. A single float (4 bytes) becomes up to 14 bytes on the wire, plus delimiters.
• One statement per call: you cannot send a batched INSERT in one call. You call LSql_Execute 100 times per second, which loads the connection with 100 round trips.
• Synchronous over TCON: the connection is TCP-based and the block waits for an acknowledgment from the server, which means that any SQL server latency directly adds to the OB3x execution time.

10. PC-Side Stack: InfluxDB + Grafana + Node-RED

When the customer only needs a "SQL database", an honest conversation must take place about what they will do with the rows afterwards. If the rows are intended for trending, dashboards, or alarming, a time-series stack is technically superior to a relational SQL server. The well-trodden combination in industrial PC environments is:

This stack is mentioned in the source as a proven combination that "just works" for PLC-to-PC logging. It removes the LSql string conversion overhead because Node-RED reads from the PLC via native S7 / OPC UA / Modbus TCP nodes and pushes the parsed values into InfluxDB's line protocol, which is text but very compact. A typical line of 8 values, each as a 32-bit float, occupies around 200 bytes in line-protocol form, including the timestamp, which keeps the wire load well below the 4 kB/s envelope even with per-cycle inserts.

11. Throughput Calculation Worksheet

To size the link, compute the wire payload of the chosen strategy. The formulas below assume one timestamp (LTime) and N 32-bit float values per cycle. The 100 Hz cycle gives 100 cycles per second.

// Strategy A: LSql text-based INSERT
//   Wire payload per row  =  N * (max 14 chars + 2 delimiters) + 32 overhead
//   Total per second      =  100 * Wire payload
// Example: N = 8, payload = 8 * 16 + 32 = 160 bytes/row -> 16 kB/s

// Strategy C: Binary blob, 100 rows per bulk write
//   Wire payload per blob =  100 * (8 bytes timestamp + N * 4 bytes value)
//   Total per second      =  payload / write_period
// Example: N = 8, write every 1 s, payload = 100 * 40 = 4 kB/s

The 3.5 to 4 kB/s target stated by the customer is consistent with Strategy C at N = 8 values per sample and a 1 second bulk interval. If the customer ever requests a higher N, the bulk interval must be reduced or the link upgraded to gigabit.

12. Sample Implementation Skeleton (TIA Portal)

The skeleton below shows the cyclic OB reading eight fast inputs with direct access, packing the values and a 64-bit timestamp into a byte array, and exposing that array for a PC application to read via OPC UA or a PUT/GET connection.

// In OB33 (10 ms cyclic)
#tPLC := RD_SYS_T_LTIME;          // 64-bit LTime

// Direct read of 8 input words from local I/O slot 4
#iw0 := IW112:P;                  // Channel 0
#iw1 := IW114:P;                  // Channel 1
#iw2 := IW116:P;                  // Channel 2
#iw3 := IW118:P;                  // Channel 3
#iw4 := IW120:P;                  // Channel 4
#iw5 := IW122:P;                  // Channel 5
#iw6 := IW124:P;                  // Channel 6
#iw7 := IW126:P;                  // Channel 7

// Pack into a 40-byte frame: 8 bytes timestamp + 8 * 4 bytes float
#dbLogging.t[1]  := DWORD(#tPLC AND 16#FFFFFFFF);
#dbLogging.t[5]  := DWORD(SHR(#tPLC, 32));

// Convert each INT to REAL and pack
#dbLogging.v[1]  := REAL(LREAL(#iw0) / 27648.0);   // normalized to 0..1
// ...repeat for the other 7 channels...

// Set a "new sample" boolean and increment a counter for sanity
#dbLogging.bNew  := TRUE;
#dbLogging.cnt   := #dbLogging.cnt + 1;

On the PC side, an OPC UA client or a Node-RED S7 node subscribes to dbLogging, reads the 40-byte frame on the rising edge of bNew, and posts it to InfluxDB or to the SQL staging table. The timestamp in the row is #tPLC, not the time the row was written.

13. Commissioning Procedure

1. Wire the network. Connect the PLC's X2 port to the IPC's NIC using a CAT 6A cross-over or a dedicated small switch. Disable spanning tree on the port pair if a switch is used.
2. Configure the CPU time. Synchronize the PLC to an NTP server (or to the IPC via NTP). Verify that the LTime value remains within 10 ms of the IPC clock.
3. Configure OB33. Set the period to 10 ms and the phase offset to 0. Compile and download. Monitor the OB3x execution time in the online diagnostic view; the maximum must remain below 6 ms with margin.
4. Enable the input modules. For each AI module in the configuration, set the integration time to the fastest available value and the diagnostic interrupt to "none" (to avoid extra load on OB82).
5. Force a square wave on a test channel and verify that the dbLogging.v[*] values toggle at 100 Hz by counting the cnt field on the watch table.
6. Configure the OPC UA server on the CPU with the new data block published under a custom namespace.
7. Set up Node-RED. Install the S7 node, configure the OPC UA subscription, and add a debug node. Confirm that 100 messages per second are arriving.
8. Configure InfluxDB. Create a database plc_100hz and a retention policy of 90 days. Add an InfluxDB write node in Node-RED, point it at measurement=fast_data with the eight values as fields and the timestamp as the line time.
9. Build a Grafana dashboard with eight time-series panels and a refresh rate of 1 s. Verify visually that the square wave is plotted.
10. Run a 24-hour soak test with a counter that must be exactly 8 640 000 at the end. If the counter is low, an OB3x instance was missed; investigate the OB priority and the process image update configuration.

14. Verification Checklist

15. Troubleshooting Matrix

16. Frequently Asked Questions