2

I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance.

For context, I am automating the process of updating firmware on devices in my company. The devices send UDP data to the server and store the messages into a single table, so the table is quite large

There are 2 modules on each device, high and low frequency and the messages are very similar

Previously, I had temp tables, counters, and a while loop to get the most recent firmware message for each device on each module. I have since used the following CTE to extract the same information (only high frequency code shown, table and column names changed for confidentiality)

;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersion, HFFWMessage, HFFWMessageDate) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
            LEFT(RIGHT(UDPMessage,7),3),
            RIGHT(UDPMessage, 3),
            UDPMessage,
            DateRecorded
        FROM UDPTable udp WITH (NOLOCK)
            INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
        WHERE UDPMessage LIKE '%FWMessage:%' 
            AND UDPMessage NOT LIKE '%FWMessage:LF%' 
            AND fw.Status = 'HF Pending'
)

-- Get the most recent HF Firmware Message for each device
SELECT cte.DIdentifier, 
cte.CachedHFFWVersion, 
cte.CurrentHFFWVersion, 
cte.HFFWMessage, 
cte.HFFWMessageDate
FROM HFCTE cte 
    INNER JOIN (
                SELECT DIdentifier,
                    MAX(HFFWMessageDate) AS MaxDate
                FROM HFCTE
                GROUP BY DIdentifier
               ) AS gcte
        ON cte.DIdentifier= gcte.DIdentifierAND cte.HFFWMessageDate = gcte.MaxDate
ORDER BY cte.DIdentifier DESC

The main problem is that before picking out the most recent firmware message using the last SELECT statement, the CTE has to get all FWMessage records for each device, which could be very bad for performance during peak hours.

Is there any way to only pull the one record I need for each device in my initial WITH CTE query? Would it even optimize the overall performance of the query? Should I be using a different method entirely?

P.S. DIdentifier is a PRIMARY_KEY in the FirmwareInfo table and a FOREIGN_KEY (Not to the Firmware table but to the table that holds devices) in the UDP table. The UDP table also has 2 relevant indexes, both non-clustered & non-unique. One includes DIdentifier and UDPMessage, the other includes DIdentifier and DateRecorded. I cannot create a new index for UDP table due to performance constraints

0
1

You can try using a window function like ROW_NUMBER() or DENSE_RANK() to generate an ID that represents the latest rows per your sort criteria. Here's an example:

;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersionn, HFFWMessage, HFFWMessageDate, SortId) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
        LEFT(RIGHT(UDPMessage,7),3),
         RIGHT(UDPMessage, 3),
         UDPMessage,
         DateRecorded,
         DENSE_RANK() OVER (PARTITION BY udp.DIdentifier ORDER BY DateRecorded DESC) AS SortId
    FROM UDPTable udp WITH (NOLOCK)
        INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
    WHERE UDPMessage LIKE '%FWMessage:%' 
        AND UDPMessage NOT LIKE '%FWMessage:LF%' 
        AND fw.Status = 'HF Pending'
)
     
SELECT cte.DIdentifier, 
    cte.CachedHFFWVersion, 
    cte.CurrentHFFWVersion, 
    cte.HFFWMessage, 
    cte.HFFWMessageDate
FROM HFCTE cte 
WHERE cte.SortId = 1
ORDER BY cte.DIdentifier DESC

This eliminates the need to join your CTE to itself in a subquery and further upstreams the logic for determining the latest records by date. Also the window functions tend to be rather performant for this kind of problem.

5
  • This definitely alleviates the self join problem but doesn't seem to do much for the underlying issue of having to touch every Firmware message for each device. The outputted cte returns the same amount of rows if you omit WHERE cte.SortID = 1. I will have to look into these windows functions to learn about their uses, functionality, and how they effect performance. If there are no other answers that seem for efficient I'll mark this correct at the end of the day. Could you elaborate on your last note please? I declared 5 columns in the initial CTE and SELECT 5 columns in the final – Austin Joachim Apr 20 at 20:42
  • @AustinJoachim I think any approach has to touch each row (roughly) in order to find the latest message by HFFWMessageDate, but using a window function likely will be more efficient than self-joining the CTE. That being said, the WHERE cte.SortId = 1 should return only the latest rows by HFFWMessageDate (which will be multiple per DIdentifier if two dates are exactly the same), so that's interesting you're seeing otherwise. As a quick test can you replace DENSE_RANK with ROW_NUMBER and let me know if the WHERE cte.SortId = 1 clause produces different row counts now? – J.D. Apr 20 at 20:51
  • 1
    You misunderstood, WHERE cte.SortID = 1 does only return 1 row per device (this is good and what we want). If you take out WHERE cte.SortID = 1 you get the same large number of total rows, which makes sense because it would be impossible not to touch every message since I have to get all of them in order to find the most recent! Thanks for all of your help. – Austin Joachim Apr 20 at 21:19
  • @AustinJoachim Ah I gotcha, thought I might've made a mistake for a second lol. Ok cool, no problem! Also regarding my last note about your CTE missing columns, I quickly misread and missed that you aliases them in the CTE definition, so all good there. Just curious, how is the performance difference between this implementation and your previous one with a subquery?...how many records are you dealing with in each table? – J.D. Apr 20 at 21:30
  • Currently the performance difference isn't too noticeable, as we're getting to the last leg of updates and previous changes I made omit completely updated devices from being queried, so as we get closer to finishing updates, the processing intensity goes down. But these changes I'm making now are meant to improve performance during the beginning of our next update, as the WHILE loops were timing out when many non-updated devices were active simultaneously. A few thousand devices, each with > 100k UDP records – Austin Joachim Apr 21 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.