Timeline for Use CTE to fetch most recent row for each ID
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 21, 2021 at 19:33 | comment | added | Austin Joachim |
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
|
|
| Apr 20, 2021 at 21:31 | history | edited | J.D. | CC BY-SA 4.0 |
deleted 191 characters in body
|
| Apr 20, 2021 at 21:30 | comment | added | J.D. | @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? | |
| Apr 20, 2021 at 21:19 | vote | accept | Austin Joachim | ||
| Apr 20, 2021 at 21:19 | comment | added | Austin Joachim |
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.
|
|
| Apr 20, 2021 at 20:51 | comment | added | J.D. |
@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?
|
|
| Apr 20, 2021 at 20:42 | comment | added | Austin Joachim | 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 | |
| Apr 20, 2021 at 18:57 | history | edited | J.D. | CC BY-SA 4.0 |
added 235 characters in body
|
| Apr 20, 2021 at 18:51 | history | answered | J.D. | CC BY-SA 4.0 |