Question Locked

SQL Date Filtering

0 helpful
15 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a set of sensors on a production line that are logging data to an SQL 2005 server every second. There are 154 ssensors. To date, there are 306 million records. I'd like to create a view that shows only the data recorded every 15 minutes. I have no control over the sensors. Any thoughts on a view? A simple Select query was added but it takes too long to load (almost 80 million records).

SELECT dbo.FloatData.DateAndTime, dbo.FloatData.Millitm, dbo.FloatData.Val, dbo.FloatData.Status, dbo.FloatData.Marker, dbo.TagData.TagName
FROM dbo.FloatData LEFT OUTER JOIN dbo.TagData ON dbo.FloatData.TagIndex = dbo.TagData.TagIndex
WHERE dbo.FloatData.TagIndex <= 21)

15 replies

by thumbsup2

You have a working query, but your complaint is that it takes too long to load? I would say that ANY query which is looking at a database that large is going to take a while to run.

Have you thought about the fact that you're querying a live database which is being updated by the sensors every second WHILE your query is running? The file is growing.

Do you have an option to write something that would extract just the 15 minute records to another database and use that one to display your 15 minute updates? Once loaded, it could query only the records for a particular day/hour to add to the 2nd database based on what was last loaded into it.

by shellbot

thats a LOT of records..

temp tables to hold those 15 minutes worth?

by znb74

Is there a way to write a script that auto runs every 10-15 minutes that could append data from the huge table to a more useable table or set of tables? I'm still fairly new to SQL.

by shellbot

Is TagIndex indexed?
Is this a 1 to 1 or 1 to many, do you need that left join or could you use an inner join (its quicker).

Locking could be contributing to the slow performance of the query..let me think on that.

I'll help you with a script in the morning..we can just set up a task that runs every 15 minutes or whatever..but ya, let me think and i'll post tomorrow if no one else has.

by znb74

TagIndex is "indexable". I changed to an Inner Join and that did speed it up a little. It's still having to deal with a TON of records, though.

I created a view that uses the DatePart function to pull every 15 minutes but it seems to actually miss some data. I'm going to verify that the sensors are indeed logging every second.

by shellbot

sorry dude, completly forgot about ya today..was busy..

eh, so..TagINdex is NOT indexed then????
Ok, my first thing I would do is index that baby...both of them (on each table), because your linking the two tables together on that field, and its going to slow it down big time as its doing a full table scan rather than going straight to where it needs to go.

Ideally you'd want to do a clustered index if possible, but as you can only have one of those per table, you may already have one..so no worry. Thing with that would be that it would slow down your inserts..so you'd have to monitor performance carefully.

ok, sometimes you have to play around with datepart a bit..

let me know how you get on..i'll try and remember to send some stuff ya if i can in the morning.

don;t worry about the number of rows..i know your pulling back a lot, but SQL can handle it, we just gotta tweak it..

if all else fails..may have to use temp tables..it can just get messy sometimes depending on what yer doing with em..

what are you oping to do with this data at the end of the day. will it be stord/archived or just looked at the once?

by znb74

Apparently the FloatData table is only indexed on the DateAndTime column as non-unique. I've shot an email to the vendor that set all this up to ask about indexing on TagIndex. I want to make sure the PLCs aren't relying on data from SQL to operate the lines.

by shellbot

good call to contact the vendor..
its probbaly not an issue..but sometimes better to check first!

did you get the datepart working any better?

by znb74

Success! Pulled 58,000 records for one TagIndex since 6/1/2009 in about 10 seconds. I'm still working through the DatePart function. Even with the Index modifications it will skip hours worth of data.

by shellbot

so was that just by indexing it?

post or send me your query with the datepart and we have a look

by znb74

After browsing through 500,000 original records, it appears to be a result of the fact the lasers are appending every second exactly. We're actually losing about 40% of what it would be if it were every second. That part I can't explain. I have an Access application running on every line. I'm thinking of using it to pull data from the sensors and populate another table on a more report-friendly manor like every 5 or 10 minutes. Some of the sensors I can get away with readings every hour or mayby 30 min. The lasers are reading diameters so every minute might do. I'll just pull the latest value and append to a new table what I need.

by znb74

What would happen if I indexed a view? Is there any advantage - like speeding up the query when pulling the records into Access? I'd like to display the diameter data to operators on the lines as it's pulled from the main table to a new table. I tried a DLookup on the view and it takes a bit long to run.

by shellbot

it should go a bit quicker..
I've never had to bother, so can't really say for sure.

give it a go :)

This discussion is closed to new replies.