This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Long query execution time

This query and many others take forever to run, this one in particular takes 10mins. We do not have any fragmentation on the underlying resources. I can't figure out why it takes so long. We also have 16 CPU - 64GB RAM and SW is the only DB running on this platform, we also have Netflow in a separate instance.

SELECT top 1000 DailyNodeAvailability.NodeID,

DailyNodeAvailability.DateTime,

DailyNodeAvailability.Availability

FROM DailyNodeAvailability

  • 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.

  • 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.