So, for reference/fyi, just finished pulling some data to our db, here’s the two queries I ran..
SELECT DateTime, InterfaceID, NodeID, Availability, Weight FROM InterfaceAvailabilityByDays
SELECT NodeID, DateTime, Availability FROM DailyNodeAvailability
And bulk inserted the data to our db (autocommit off, etc).
Took about 30 minutes to complete those 4 queries/inserts, totaling 35mil rows. which IMO is not enough to take 30 mins.
2 of 2 people found this helpful
that is a 'view', so the underlying query is pretty horrendous as it aggregates data for all nodes across all partitions of the ResponseTime Table for all times. this is what you actually executed:
SELECT top 1000 NodeID, DateTime, AVG(Availability) AS Availability FROM ( SELECT NodeID, FLOOR(DateTime / 24) AS DateTime, AVG(Availability) AS Availability FROM ( SELECT NodeID, FLOOR(CAST(DateTime AS float) * 24) AS DateTime, AVG(Availability) AS Availability FROM ResponseTime WHERE (Archive = 0) GROUP BY NodeID, FLOOR(CAST(DateTime AS float) * 24) UNION SELECT NodeID, CAST(DateTime AS float) * 24 AS DateTime, Availability FROM ResponseTime WHERE Archive = 1 ) AS T1 GROUP BY NodeID, FLOOR(DateTime / 24) UNION SELECT NodeID, DateTime, Availability FROM ResponseTime WHERE Archive = 2 ) AS T2 GROUP BY NodeID, DateTime
By comparison, if you run it for a specific nodeid and date it returns really quickly...
I did see the underlying mess in the view of underlying view source table query with the unions.
So I guess the answer is that nothing is going to speed this up without like you stated using a NodeID or DateTime.
BTW - I just limited this to a WHERE ([DailyNodeAvailability].[DateTime] >= '3/23/2015 00:00:00.000') and still took over 5 mins. I will try doing just one day worth of data to see if that helps.