Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples for a particular piece of equipment. There are many pieces of equipment being monitored so the database sample tables end up looking like this:

Equipment1_SampleData_1
Equipment1_SampleData_2
Equipment1_SampleData_3
...
Equipment1_SampleData_31

So those 31 tables contain sample data for one piece of equipment over the most recent rolling 31 day period.

There is a set of those tables for each equipment type. This makes it difficult to query for a specific sample value, given a specific date/time.

I was thinking of creating a view which unions all the equipment sample tables into one big view so that I can write a function which returns a particular sample given an equipment name and date/timestamp. Is that a bad way to do this? Can you suggest other or better ways of doing this? I do not have any control over the way the 3rd party data logging/sample tool stores data.

The end result of this is that I want to be able to say: "Give me the sample value recorded for equipment N, nearest (>=) the specified date/time" and get a value back.

This is on SQL Server 2008.

share|improve this question
Yes, I would say your view approach is probably the best in this case where you have no control over the underlying base tables. – Thomas Stringer Aug 13 '12 at 14:53
"approximately one day's" -- from this language, are we to assume that the data is not partitioned into these tables by date? In other words, both tables 1 and 2 may contain data from a single date? – Jon Seigel Aug 13 '12 at 16:41
You could also generate a dynamic query, but since you have a specific number of tables you will get a better performance if you stick to UNION. – MiNT Aug 13 '12 at 18:51
@Jon Seigel - Yes, the table roll over time may fall within a day so the first part of August 20th may be in one table and then it might roll over and add data from August 21 in that same table. – Shane Wealti Aug 14 '12 at 13:30

1 Answer

up vote 1 down vote

Personally, I'd consider using triggers to load a proper single table and query that. Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...

share|improve this answer
That is an interesting idea. So I would put row insert triggers on all the tables and insert added rows into the consolidated table which can be queried in a more standard way. I think I'm going to give this a try, assuming I have enough storage space to have two copies of all data. – Shane Wealti Aug 14 '12 at 13:32
Since this is a 3rd-party app, check with the vendor if this is allowed first (assuming you continue to have a support contract). – Jon Seigel Aug 14 '12 at 13:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.