Table Design Recomendation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Table Design Recomendation

Author

Topic

jdelgado
Starting Member

5 Posts

Posted - 2010-08-16 : 10:12:05

Hi,

I have an application with where we are logging process data from different machines at a rate every 6 secs. So every 6 secs a record is logged from a machine and there are 8 machines. This is use on a report for trending. The application has been running for 3 years we have a total of 28,570,112 records the application is timing out and it is taken around 40 secs to get the data when I manually run the query. I am getting the data from a stored procedure which calls a view where I join two tables. I filter the data by the ProcsId field. Is 40 secs to much ? The table design are the following are my keys selection good should I do anything different on the designed. I had reindex the table and updates the stats. Should I ad an Index to the view.

Trend Table
dtTimeStamp datetime (PK)
ProcsId varchar(16)(PK, FK)
SegId tinyint
PhaseId tinyint (FK)
SegTime smallint
SVTemp smallint
SVTemp2 smallint
SVPress smallint
SVLvl smallint
PVTemp smallint
PVTemp2 smallint
CircTemp smallint
PVPress smallint
PVLvl smallint
RotrSpd smallint
RotrPos smallint
Flow smallint

Phase is the other table I join in the view
PhaseId tinyint (PK)
PhaseDesc varchar(50)
PhaseDescEsp varchar(50)
ProcsMode tinyint
PhaseEnabled tinyint

MichaelHLutz
Starting Member

19 Posts

Posted - 2010-08-18 : 21:25:11

Sounds like you are summarizing the data in your report. A better performing solution if this is feasible is to store the summarized historical data in another table by ProcsId and then only query the more recent data from your Trend Table each time the report runs, and append that data to the pre-summarized data. Then you could delete out the old data as well if this reporting is the only purpose of the data. In other words, pre-summarize (pre-aggregate) the data only once and use it as opposed to using the raw data. It will take some work but then hopefully your reports will run lightening quick, not to mention disk space savings.

Make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources