SQL Data

[slpefanis]

Hi All, i am not sure if this is the right forum, but i couldnt find a SQL section.

I am writing an application to log data from our PLC system to a SQL database. I would like to write a record every second. I have it all working fine, but i am worried about the potential to "overload" on data and cause the system to become unsable.

Is there anything specific i should be doing on the table in the SQL server to accomedate the ammount of data?

I may be changing to minuite logging, but just wanted to check

[Ancient Dragon]

but i couldnt find a SQL section.

It's under Web Development --> Databases. But here is ok too.

What database server will you be using? I doubt MS Access can handle that much information to rapidly. Can you consider redesigning the project? For example, the program could hold the data for one minute, summarize it, then send summarized data to the database. How userul will that much raw data be to anyone? Will summaried data be just as usful?

[Reverend Jim]

Don't worry too much about the amount of data. I developed and maintained a database that had to store over 24000 records per hour (over half a million per day) and the data had to be kept forever. At the time I retired we were up to ten years and counting. And even that was not considered a large amount of data. What you want is to ensure that the tables are not too large to maintain. If it will help I can tell you what I did.

I set up one database for each month of data. There was also one control database. The control database contained views which combined the various months into useful virtual databases. These views were

• one for the current month
• one for the current+previous months
• one for all months

Regular updates (two per hour of roughly 8000 records each) were done using the first view. Most queries were done using the second view. That was because 99% of the auttomated queries never looked backl more than two days. All other queries used either the third view (where speed was not a factor), or used loops to query specific monts (where speed was a factor). The advantages of partitioning the data this way were

• archived months only had to be backed up once
• backing up the current month (nightly) was fast and required little space
• recovering from a database error was a matter of minutes

Speed of recovery was a primary concern as the database was required 24x7. Ease of maintenance was a secondary, but also important concern.