Re: Production statistics data storage
- From: "William Vaughn \(MVP\)" <billva@xxxxxxxxxxxxxxx>
- Date: Sun, 30 Nov 2008 11:44:02 -0500
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)
____________________________________________________________________________________________
"Henrik" <henrik_@xxxxxxxxxxxxxxxx> wrote in message news:77132D0C-FD41-45F4-B3C7-B07BCB858BD9@xxxxxxxxxxxxxxxx
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
- References:
- Production statistics data storage
- From: Henrik
- Production statistics data storage
- Prev by Date: Re: Caching Parameters Performance Issue When Using SQLHELPER
- Next by Date: Re: IDataReader.Read() throws Exception! How can i skip a row?
- Previous by thread: Production statistics data storage
- Next by thread: Caching Parameters Performance Issue When Using SQLHELPER
- Index(es):
Relevant Pages
|
Loading
