3 Replies Latest reply on Apr 26, 2016 2:51 PM by wlipford

    Long query execution time

    wlipford

      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

        • Re: Long query execution time
          wlipford

          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.

          • Re: Long query execution time
            RichardLetts

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

            2 of 2 people found this helpful
              • Re: Long query execution time
                wlipford

                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.