CODEWITHSUNDEEP
sqlsql-server
David Stetler
David Stetler

Reputation: 1453

how to join top 1 quickly

I have a table with devices, and a second table with performance counters. So I have device A which has lets say 1000 entries in the performance counters table. I am trying to get all the devices, 2500 of them, and their most recent performance counter. I have used an Outer Apply, which does accomplish the desired results, but the query takes 5 minutes to execute.

Is this the best way to get the info I am looking for or is there a better way? Is the performance of this query normal, and I am just asking too much? I am thinking of creating a third 'temp' table nightly that will hold this data so I can display and query the info quickly. What are the best practices for something like this?

Here is my current query:

   select * from Device D
      OUTER APPLY
          ( select top 1 *
              FROM PerformanceCounterValues
              where instance_id = D.pkid
              order by collection_time desc
           ) as tbl2

----- Solved ----

Here is my final actual query, and it works in 0 seconds. 

SELECT pkid, D.IDDevice, D.IDHardware, H.IDRecorder, D.Name, D.Description, H.Name as HardwareName, H.URI, R.HostName, R.Name as RecorderInstance, tbl2.collection_time, tbl2.raw_value, tbl2.calculated_value
  FROM [Surveillance].[dbo].[PerformanceCounterInstance] PCI
  JOIN [Surveillance].[dbo].[Devices] D on D.IDDevice = (SELECT REPLACE(STUFF([instance_name],1,CHARINDEX('[',[instance_name]),''),']',''))
  JOIN [Surveillance].[dbo].[Hardware] H on H.IDHardware = D.IDHardware
  JOIN [Surveillance].[dbo].[Recorders] R on R.IDRecorder = H.IDRecorder
    OUTER APPLY
    ( select top 1 *
        FROM [Surveillance].[dbo].[PerformanceCounterValue]
        where instance_id = PCI.pkid
        order by collection_time desc
        ) as tbl2
  where category_name = 'VideoOS Recording Server Device Storage'
  and D.Enabled = 1

It was just setting up the index that made the speed increase.

Upvotes: 0

Views: 178

Answers (4)

David Stetler
David Stetler

Reputation: 1453

Enabled Index and used this query:

SELECT pkid, D.IDDevice, D.IDHardware, H.IDRecorder, D.Name, D.Description, H.Name as HardwareName, H.URI, R.HostName, R.Name as RecorderInstance, tbl2.collection_time, tbl2.raw_value, tbl2.calculated_value
  FROM [Surveillance].[dbo].[PerformanceCounterInstance] PCI
  JOIN [Surveillance].[dbo].[Devices] D on D.IDDevice = (SELECT REPLACE(STUFF([instance_name],1,CHARINDEX('[',[instance_name]),''),']',''))
  JOIN [Surveillance].[dbo].[Hardware] H on H.IDHardware = D.IDHardware
  JOIN [Surveillance].[dbo].[Recorders] R on R.IDRecorder = H.IDRecorder
    OUTER APPLY
    ( select top 1 *
        FROM [Surveillance].[dbo].[PerformanceCounterValue]
        where instance_id = PCI.pkid
        order by collection_time desc
        ) as tbl2
  where category_name = 'VideoOS Recording Server Device Storage'
  and D.Enabled = 1

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I'm not sure whaty indexing would be best for the outer apply query, but you can try adding an index on (instance_id, collection_time) for this one:

   SELECT 
        d.*                --- whatever columns you need from Device 
      , p.*                --- and from PerformanceCounterValues
   FROM Device AS d
     JOIN
       ( SELECT instance_id, 
                MAX(collection_time) AS max_collection_time
         FROM PerformanceCounterValues
         GROUP BY instance_id
       ) AS grp
       ON  grp.instance_id = d.pkid
     JOIN PerformanceCounterValues AS p
       ON  p.instance_id = grp.instance_id 
       AND p.collection_time = grp.max_collection_time ;

It would also help if you added the execution plan of your query in your question.

Upvotes: 1

Apurav
Apurav

Reputation: 198

Try to select top one directly on the join, i have about 20,000 corresponding entries in one talbe for which i want to fetch the first entry time from a Logs table. my query executes in less than 2 sec(MsSql 2008)

select D.pkid, pvc.*
from Device D
join PerformanceCounterValues as pcv on pcv.PrimaryKey = (SELECT top(1) PrimaryKey From      PerformanceCounterValues WHERE instance_id = D.pkid ORDER BY collection_time )

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

try 

  select D.pkid, max(PerformanceCounterValues.collection_time)
  from Device D
  join PerformanceCounterValues
  on  PerformanceCounterValues.instance_id = D.pkid 
  group by D.pkid

Upvotes: 1

Related Questions