Production statistics data storage

[Henrik]

Hi all,

I'm working with developing a measurement system for a production
industry.The system will perform measurements on the units produced and grade
them into several grades depending on the measurements.

Production statistics with quality indications is a major part of the
delivery.

I need som advice on how to structure the data storage and application user
interface to avoid performance issues when he amout of data increases.

My plan is to use MS SQL server on a local network for storing information
on each unit produced. Approximately 20 000 units are produced each day. This
table will grow very large very fast.

For production analysis and comparison the units needs to be stored in this
table for at least five years. I will use SQL reporting services for creating
reports.

My concern is with the user interface of the application. The UI should show
the production outcome for the current production day. Showing some simple
statistics over number of produced units and percent produced in each grade.
The UI should update on each new unit. Approximately once every second.

What is a good plan for creating these updates?

Can I query the database for all units on that day and calculate the
statistics? Something like:
SELECT * FROM Units WHERE TimeStamp = Today
Or will this get too slow when the Units table contains millions of rows.

Should I use DataSets to hold cached data? How do I update the dataset with
every new unit?

I'm not that familiar with DataSets and even less familiar with LINQ any
advice?


Thank you

/ Henrik

 

[William Vaughn \(MVP\)]

1) If you're concerned with performance, LINQ should not be considered--not
until they figure out how to get it working.
2) No, I don't think keeping the data local makes sense (as in a client-side
Dataset)
3) SQL Server is fully capable of good performance--even when polled
frequently. As I discuss (at length) in my book, if the indexes are built
correctly, you should be able to help focus the server's query optimizer on
the most recently changed rows.
4) "TimeStamp" does not store the time or date that a row was added. It
maintains an integer that's bumped when the row is changed. I suggest a
DateTime column instead. I would index on this column (possibly coupled with
other columns) to provide enough selectivity. What you want to avoid is a
table-scan (where all the rows in the entire table are read) when you start
searching for rows.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________