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.
| Author |
Topic |
|
Jacmac
Starting Member
11 Posts |
 Posted - 2005-04-19 : 13:32:16
|
| I'm a novice at SQL, I'm having trouble figuring out how to store historical trending data for a variable number of instruments efficiently. The historical trend needs to consist of this data for a variable number of instruments:HTC_DataTime smalldatetime,INS_Temp decimal(9,1),INS_RH decimal(9,1),INS_Door bitMy initial idea was to store the data in individual tables with the table name being variable (The "INS ID" would be the table name, parsed out from another table). This would be perfect because the table would consist of only the data associated with a specific instrument trend. I then discovered that trying to create a table with a variable table name is a no-go (at least the way I'm trying to do it). Because the amount of data being saved is approximately 40 chambers at once a minute and the fact that a new chamber with a new INS ID can appear at any time, I'm at a loss as to how I should procede. Without being able to create a table using a variable name, I can't think of a good way to store the data. Is there a way around this problem? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-04-19 : 13:40:14
|
quote:
Originally posted by Jacmac
My initial idea was to store the data in individual tables with the table name being variable (The "INS ID" would be the table name, parsed out from another table). This would be perfect because the table would consist of only the data associated with a specific instrument trend.
No it would not.I not sure what you're asking, but wipe that off the drawing board.What's an instrument and what do you need to track?Brett8-) |
 |
|
|
Jacmac
Starting Member
11 Posts |
Posted - 2005-04-19 : 13:54:50
|
| I know it doesn't sound good, but I've thought about it quite a bit. The instruments are chambers which record or can record Temperature, Humidity, and the state of the Door. The raw data for all chambers is inserted into a table called Tag_Status by another program. I'm trying to write a stored procedure that will be kicked off once a minute to parse all data in the Tag_Status table and create historical trending.If there is no way to store the trend data in individual tables, then I need to be able to add columns to a single table with variable column names. If the number of chambers and their ID's comming into the Tag_Status table were constants, then I could simply create a table for each chamber. Unfortunately, a new chamber ID can be created at any time and I need to write a procedure that will save it's historical trending data in a new table or a new set of columns.Can you Exec a variable in T-SQL? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-04-20 : 05:58:27
|
| "Can you Exec a variable in T-SQL"...yes...EXEC (@SQL)Can you post sample data (in the form of insert statements)...and expected results? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-04-20 : 09:52:41
|
| It does not sound like you understand how to store the data. Each "chamber" should not need its own table. Perhaps a "Chamber" table, with as many columns as you need? You do not have to put data in EVERY column, just those that correspond to each particular chamber.http://www.sqlservercentral.com/columnists/jgonzalez/bestpracticesfordatabasedesign.asp |
|
|
|
Jacmac
Starting Member
11 Posts |
Posted - 2005-04-25 : 13:38:29
|
quote:
Originally posted by AndrewMurphy
"Can you Exec a variable in T-SQL"...yes...EXEC (@SQL)Can you post sample data (in the form of insert statements)...and expected results?
I'm not sure what you mean by posting sample data in the form of insert statements. I can give you a sample insert statement to the table I'm attepting to procedding with a stored procedure:Insert into TAG_STATUS (Tag_ID,Tag_Description,Tag_Current_Value,Tag_Limit_High,Tag_Limit_Low,Tag_Alarm_High_Delay,Tag_Alarm_Low_Delay,Tag_Alarm_Enabled,Tag_Date_Update,Tag_Time_Update) Values ('A011','C0001-A-STC',45.0,47.0,40.0,20,20,1,'04/08/2005','14:20:35')This isn't how the data actually gets into the table, this is merely a manual representation of how it could be inserted. I'm not sure if that's what you were asking to see.The purpose of the stored procedure is to process the data from the TAG_STATUS table. Some of the data has to be parsed out of the description and the date and time has to be converted from char data into a smalldatetime field. Most of the data is simply updated to a chamber status table, however, a few fields (a the top of the post) need to be saved for each individual chamber. I could create one table that holds the historical data for all chambers, but then I will be repeating the Chamber_ID. What I would rather do is store the historical data for each chamber in it's own table to keep the size down (elimination of the Chamber_ID column). This requres the ability to create a new table on the fly if a new chamber suddenly shows up. The rate of historical growth per chamber is essentially one insert per minute. Once I get done with the design, I have to import existing historical data from a different database. That is about 3 years worth of data and they stored it effeciently (albiet not in SQL server). Since I'm a novice at SQL, maybe I'm not thinking about the problem from the right perspective and attacking it the wrong way. Maybe saving one column and trying to create tables automatically is just the wrong way to go.I've got to the point where I can EXEC a statement to create the tables I need on the fly, but the new problem is executing an UPDATE or INSERT statment because there are variables involved. The data in the variables cause the EXEC'ed statmemnt in the stored procedure to end prematurely with a syntax error, for example:IF @t='TC' BEGIN EXEC('INSERT INTO '+@HTC_INS_Number+'_HTC(HTC_DateTime,Tag_TC) VALUES('+@Cast_Time+','+@Cast_CV+')') ENDproduces:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '25'.The variable probably contained '25.3' or something similar. EXECing is not safe the way I'm trying to use it, which is probably not how it was meant to be used. I wish you could 'INSERT variable' instead of having to specify the table name. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-25 : 17:24:26
|
| I think that you would be better off with a single table. With the amount of data you described, 57,000 rows/day * 365 days * 3 years = 63,072,000 rows for 3 years. With a four byte integer ID for the chamber, that would only be about 240 MB for three years of data, and you could cut that in half with a 2 byte smallint ID for the chamber ID.I am guessing that you might want to run queries for all chambers, or multiple chambers, and it will be a lot less work to do if all the data is in one table. It will also make your inserts a lot less complex, and you will not have to worry about creating tables on the fly.I think the only reason for differenct tables for each chamber would be if you have differents sets of measurements being taken on each chamber.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Historical Trend Data- HELP!