Low and high context mismatch

In her Digital Body Language, Erica Dhawan writes about low and high-context languages. We need a detailed understanding of the culture to process a message in a high-context language. The message has to be interpreted in its social context to be understood. When we communicate, certain things are not said because they are apparent, even if not explicit.

English language and the cultures associated with it are low-context. It doesn’t mean we always say what we mean, but we are used to having things spelt out.

Which one is better? I am not planning on settling this argument here. In the book, the debate is not settled either. Instead, the author offers practical tips on communicating online with people from different backgrounds. However, the distinction could help us understand why we struggle to read and work with “legacy” code.

And yes, I mean “legacy”, not as an obsolete code written in long-forgotten, unsupported technology. That would be legacy code. I mean the code written a couple of months earlier by me, which I cannot read or comprehend anymore. We call it “legacy”, a “tech debt”, and argue for rewrites or at least very extensive refactoring “tickets” to be put on the product backlogs… don’t we?

Refactoring, rewrites and sacrificial architecture.

Rewriting your code can be an essential part of software engineering. This sacrificial approach to software architecture worked well for many. For example, an ex-eBay Chief Engineer and Distinguished Architect, Randy Shoup, often says that if you are not rewriting from time to time, you are over-engineering. But it should not be done because we don’t understand the code or the problems. There are plenty of examples. Last week this showed up in my feed – an excellent and high-profile example of when not to do it.

If you don’t understand the problem how can you be sure that you won’t replicate it when starting from scratch?

So, how about refactoring? For me it’s part of coding. It’s something that should be done all the time as a standard way of working with code. If we practice Test Driven Development (TDD), it happens in every mini cycle red-green-refactor. I often do it when reading code too. When things are not obvious, I refactor to help me and those coming to the code behind me understand the code better. I use refactoring to help me understand things. Unlike rewrites, I think it should be used to help us understand. But it shouldn’t be on backlogs!

Coding is communicating.

And this brings us back to low and high-context languages. When we write computer programmes, we codify our solution to a problem. In some languages, we focus on the shape of the solution – that’s the declarative approach. In imperative languages, we focus on the steps to get us to the solution. What we omit, almost always, is the description of the problem. We forget that the code is not only something machines will execute. We will eventually have to read and try to understand it too.

We code as if programming languages were high-context. At the time, we don’t have to express the problem, the reasons, or the technical and business constraints behind our decisions. They are obvious. We have had to understand them to shape the solution we are crafting. But that high-context communication style works only if the person receiving our message also has access to the context in which it was created.

Digital Body Language explains how it is reasonable to expect others who live in the same culture to have that context. Communications can be very effective even if it appears to us low-contexted English speakers that some things are missing. But in programming, the context is often gone forever. Even if we go back to our own code, we look at it thinking, “what was I thinking?”

Is it engineering or archaeology?

I used to think that we struggled to read other people’s code because we didn’t practice it enough. Programming courses (universities, boot camps, online tutorials, they all) focus on writing code rather than reading it. But it could be because writing code is Software Engineering, and reading it is Software Archaeology. (check the episode of Software Engineering Radio with Dave Thomas on the subject). It is an apt description but only because we create artefacts devoid of context. Our digital products might as well have been created thousands of years ago by unknown cultures. We know what tools they have produced. But why? And why didn’t they do something else?

So perhaps the lack of practice is not the issue here. We struggle to understand what we build because we live in a low-context culture, speak in a low-context language, and yet build our digital services as if we operated in a high-context environment.

What can we do about it?

Should we comment on our code more? Should we document our solutions more? I don’t think so! Instead, we should have a screaming architecture that doesn’t need additional comments or documentation of the solution.

But we should document the problem better!

We can do it with refactoring. We can use design patterns – not as part of solutions but as a common language to hint at how we see the problem we are trying to solve. If working in the open, we can write, blog or create video walkthroughs about the situation we faced and solutions we have chosen not to apply. We need to capture not what is already in the code but what is not there and why it is not there.

Software archaeology problem affects the whole of digital products.

The problem is wider than just code. It affects digitally enhanced services as a whole. We build roadmaps to meet the needs of our users. We prioritise our backlogs by choosing and recording what we will do. But we don’t often record what we decide not to do and why. Our roadmaps look into the future showing us where we want to get to, but they rarely show where we are, what our surroundings are, and how we got here in the first place. Without all of that, our roadmaps look more like marching orders. Without it all, our products, as our code, need archaeologists to unpick the past before every next step we take.

Weeknote 2

It’s Christmas day. I know. But as I grew up in a culture that does most of the celebration on Christmas Eve, today is just a lazy day. No turkey roast, just time to spend with the family, relax, reflect and write. 

This week I had only three days of work. I took Thursday and Friday off to do some full-time first aid volunteering. This is my way of switching off before a couple weeks of leave. You know, do something different, something very absorbing. 

It’s not the first time I have done it. However, it is the first time I have overcooked it. By 21:00 on Thursday, I was sitting at the side of a road, messaging ambulance control that I could not go to the next job they had assigned me as the previous three were still in my head. I wanted to go home, decompress, and get some sleep. But the Welsh Ambulance Service takes wellbeing seriously. So the next thing I knew, I was in an office with a cup of tea in my hand, chatting to somebody who’s been there before. 

Before you ask, I’m fine. By Friday night, I was out again. But it made me reflect on things I do more than ever before, so this weeknote might be tinted by this experience. Now that I have warned you… Let’s do it. 

Contents:

Cross-cultural team collaboration

Talking is important. Not only for the wellbeing but also to build digitally enhanced services. After all, it is a team sport. Too often, we build teams of walled professionals who attend the same standups and work from the same backlog without collaboration. They just happen to work in parallel for the same master or manager.

Diversity is important too. We know it. We form diverse teams hoping to get the promised benefits. But do we get them in full?

It is the end of my fifteenth year working with digital technology in or from Wales. It has been a decade and a half since I uprooted myself and tried to continue my familiar career in a new context. New culture, new languages (both natural and programming), new norms, and new expectations. I thought it didn’t matter. But it does. 

I still struggle to express myself as well as I would like to. Instead of gaining native fluency in Welsh and English, I have lost it in Polish. All three languages I use every day feel equally awkward. There are things like Christmas jumpers, bingo and crackers, which I lost any hope of understanding. At the same time, the traditions I grew up with appear more and more absurd. I am nowhere-native. But that’s OK. Instead, I have a reasonably good understanding of three perspectives and three languages to think in.

This is my personal model of how diverse, cross-functional teams work. It means giving up on excellence in any individual profession to benefit from a synergy of good enough understanding from many more perspectives. I know it requires daily effort to make it work. But I also know that it is worth it.

Digital natives and adopters

When talking about diversity, we often overlook one dimension. Digital natives and adopters. What it even means? I have just finished reading a book by Erica Dhawan – Digital Body Language. The book covers many challenges of modern, remote, asynchronous communication, but the generational differences are prominent. They are often framed as cultural differences between digital natives and adopters and how the technology we grow up with affects how we express ourselves and how we perceive communications of others. 

The author proposes a test to determine if somebody is a native or an adopter, but it is not such a clear-cut for me. Communication technologies change so quickly that we might all be nowhere-natives in our “office” jobs, as I am nowhere-native geographically and linguistically. 

It can be an opportunity, but as Erica Dhawan explains, this is a huge challenge and a problem if not managed. 

Reflecting on the last week and the previous two years, we have missed a massive opportunity. We are now firmly in the post-CoVID-19 hybrid work world. It was an opportunity to intentionally structure our communications channels and interaction habits, which were already in chaos. We could have used information architecture and re-designed how we work. But we didn’t. Instead, we just assumed things would just somehow work out. 

The autonomous team problem

We want to work in user-centric ways. We tend to agree that to achieve it, we have to be agile and Agile™ comes with the mantra of autonomous teams. Surely, given enough time and retrospectives, the teams will figure it out and optimise their work. And they might, although my experience is full of examples of teams optimising for the wrong things and settling in local minima. But even if they could do it well, the question that bothers me is whether they should. 

A delivery team should focus on value delivery. A data engineering team should focus on building better data engineering platforms. They should spend only a little time thinking about in-team communications. The between-team communication is outside of their influence and scope. Organisations should provide better training and guidance on how to communicate, with whom and most importantly, when not to. 

My thinking on the subject is heavily influenced by Team Topologies by Matthew Skelton and Manuel Pais. At least when it comes to inter-team collaboration. I also agree with Cal Newport when in Deep Work he makes a case for organisations to create constraints within which autonomous teams operate. It goes against the mantra that suggests that autonomous means completely independent. But organisational support is essential to help the teams focus on what is really their domain of expertise. They shouldn’t need to figure out how to communicate across generations nor invent how to incorporate diverse team members and reap the benefits.

Talking is important, and we should talk about it, and teach each other how to do it. 

Things I wish I had time to write about

OK, that Cal Newport reference is not an obvious one. He writes mostly about individual productivity, but he mentions the need for organisational support. In fact, after finishing Deep Work last December I started a year-long experiment in my communication habits. I hope to have some time soon to write about it. And about maximising things not done, too!

Erica Dhawan, in her book, writes about high and low-context cultures and languages. It struck me that we live and collaborate in Engish – a very low-context environment, but we often write code in a high-context way. This can be the reason behind many common problems – a thought I would like to explore more. 

Whenever I get a message starting with “I know you are very busy”, my stomach turns. Why do we do it? How many opportunities to collaborate do we miss because of that? 

Have you ever noticed that some people turn job titles into industries? Why do they do it? How do we stop them and redirect that energy to where it is needed?

I keep thinking about continuous professional development and how little we do it. Weeknotes and TLIr (Today I Learnt) type posts help but are these enough? I bought the Cultivating Communities of Practice book to learn more about the subject.

Weeknote 1

It’s time to continue what I started last week – an experiment with “readerless” weeknote blogging. I know it has been done before. But will it work for me? 

TL;DR:

The full version:

I published the first ever weeknote on Sunday late afternoon. On Monday morning, I walked into the office and got instant, in-person feedback (thanks!). What have I done? Aren’t weeknotes readerless? Have I broken the system already?

Luckily in the office was Mr Civil Service Weeknote himself. We talked for a while. He told me about the history of weeknotes, the Government Digital Service, and digital services in the Civil Service. I couldn’t share what I learned in that conversation in a single post, even if I tried. But that’s beside the point. It was the first indication that writing reflections in public as if nobody will ever read them can spark unplanned but valuable interactions. 

That chat, thank you, Mr Weeknote, was what we are yet to replace in the new “hybrid” and “remote” world. Of course, we can schedule calls and virtual meetings, but how do we create opportunities for such spontaneous exchanges of ideas. Writing weeknotes is probably not the answer I’m looking for, but it could help. 

Enough of this meta-writing. Let’s start weeknote 1 proper… 

How much should we listen to the user “needs”?

Last week I missed a few things in my first weeknote as I decided to go and do some community first responding for a few hours. I published what I had and booked on just after 20:00. I wanted to be home by midnight as I had to be in the office early in the morning. It wasn’t to be. As soon as I logged into the system, I was sent to a 90-year-old male who’s fallen and couldn’t get up. He’s been waiting for help since 15:00. The plan (or hope) was to get him up and into his bed. One less patient for the ambulance proper. Not this time! He couldn’t be safely lifted or left as he was, so I kept an eye on him until a professional crew relieved me just after 03:00.

What has it got to do with users and their needs? The initial hour of a response can be busy. There are checks to make, techniques to perform, and things to discuss. But once that is all done, then there is the wait. Then, there is time to think, interrupted by more and more impatient questions, “how long until the ambulance arrives?” 

My “users” want to know when an ambulance will arrive. That’s their “need”. They and their families want to know how long the wait will be here and then at the emergency department. And I must admit, by 01:00, I was with them on that! I had been there already for four hours, and now I had only 5 hours left until my alarm clock went off. Unlike them, I knew nobody was coming just yet. And so a thought was born. Couldn’t we build a digital service that shows current ambulance waits? Couldn’t we apply some machine learning doodah to predict the delay? Couldn’t we show the patient and their family exactly how hopeless their situation is? 

Of course, we could! But should we? Our users want this. We could confirm this with user research, but I was in the field listening to the question for six hours. I felt the urge too. By 03:00 I was half asleep, but my head was full of ideas about how such a system could be built. And then it dawned on me! We all want to know when the help will come, not because it is important to us or it matters in any real sense. We just want the help to be there now. Wouldn’t it be better to fix the service, so the ambulance arrived before this question crossed anybody’s mind? 

I know. I’m opening myself up for criticism that I don’t understand the User Centric Design gospel. A good User Researcher would realise that the need is superficial and uncover the actual “ask”. We could address it with a beautifully designed service by a skilled Service Designer… and so on. But before you get on your high horse, can you honestly say that things like that never happened? How many services are there to address symptoms of some form of pathology? How many digital services have been built to plaster over a gaping hole, lack of this or failure of that? Why?

Things I will not write about

So… yes, I do think about user-centric design and delivery of digital services when treating patients in my volunteering roles. But I also think about the very agile and cross-functional ways of working I see in emergency services when I’m back in my day role. I find it beneficial and inspiring. I have been thinking about ways to write about it for quite some time. I still haven’t figured out how to do it, but I hope to write about it one day. 

It’s just that it takes time. It is Tuesday night, and I’m trying to finish last week’s weeknote! I will stop here, but I wanted to list things I thought a lot about last week which I won’t write about just yet. 

  • Cross-governmental communities of practice and how anaemic, frail and small they tend to be despite people who are assigned “leads”. It is a real problem for me as I’m thinking of starting such a community myself and the only thing that stops me is this image of… I don’t even know what to call it. 
  • The “Future Ways of Working” and how much more we have to figure out there. And that the communication has to be bi-directional. And that wellbeing is essential to consider. 
  • Throughout the week, I also considered and discussed the need for CPD. Why don’t we do it in our professions? Should this be done in the open?
  • I missed my graduation and failed to pick up some award.
  • I remembered the Jolie language and how it could revolutionise how we build our applications. I’d like to try it again soon.
  • We had a Christmas party, dinner and associated meetings, something we hadn’t done in a few years.
  • I have also learnt about the Law of Stretched (cognitive) systems.
  • I have watched an interesting “intellectual riffing” between Matthew Skelton (Team Topologies) and Dave Farley (Continues Delivery). 
  • I have read a fascinating article by Robin Sloan – A Year of New Avenues
  • And the Agile Baristas slowly start to be helpful, perhaps going back to the original point about writing things out in the open. 

Weeknote 0 (zero)

For a few years, I have regularly practised writing notes to summarise weeks for my teams or reflect on my work. In addition, to maintain “current” in my first aid roles, I often write reflective notes as part of my Continuous Professional Development (CPD). I am very open about my source code and convinced about working in the open. Yet, I have only recently noticed the #weeknotes community and practice. 

There is so much content produced! But who has the time to read it all? And without readership, how useful is blogging? Well… let’s try. 

But first, a small disclaimer. I have tried blogging before. A few times. But I always found it challenging to stay within only one area of my diverse interests. I always thought sticking to one subject would make it easier to read and more “useful”. But the fact is, all those elements influence one another. So, if I am to try “weeknoting” it will have to be about all of it: work, civil service, tech, data, people and process. It will include first aid, volunteering, languages, music and continuous education. Will there be anybody who can get through it, let alone find any value in it? I probably wouldn’t!

TL;DR:

  • Burnout is a thing and we talked about it this week.
  • Porembela, working in hybrid/remote way, played in Galicia for the first time.
  • I have to make a move on establishing the cross-government linked data community.
  • GDS, and CDDO are interesting, but CDPS is even more so!
  • We dropped out from the Data Challenge in the semi-finals – but plan to continue the work regardless. Somehow.
  • While waiting for 999 calls, I had to experience why there is a need for the CDPS in Wales. They have plenty to do.

The full version:

My last week (and a bit) started the Wednesday before last. I got up early, and by 7am I was already sitting on a train to London, working through my inbox. It was going to be a long day and a long week involving a lot of travel to the extent I hadn’t experienced in six or seven years. Before then, I wouldn’t even think twice about it, but now going away for eight days? Is it necessary? Can I even do it? 

Hour after hour, meeting after meeting, task after task, I went through the day. By 17:00, I started getting messages from local pubs where colleagues were gathering – pre-meetings for our big Digital, Data and Technology (DDaT) Fest day tomorrow. But I wasn’t quite ready for it yet. So I kept working, and by 19:00, after 12 hours of working, I realised I still hadn’t prepared for the “burnout” presentation I was to co-present the next day! 

If this is autoironic, I don’t know what is! I spent my thirteenth working hour of the day preparing to talk to people about the importance of looking after themselves and not working too much!

DDaT Fest the next day was good. I joined the Department for International Trade 16 months earlier, and this was the first time I got to see how big our wider team is. Most people there I hadn’t seen in person before. There were many good discussions started, and many ideas were exchanged. But the “burnout” session’s success was a surprise. We had a few times more people there than we expected. It was one of the break-out sessions, but It looked as if everyone was there. It shouldn’t have been a surprise. After all, a lot of recent research shows more or less 1 in 2 of us show symptoms of it from time to time. The World Health Organisation upgraded it from a “workplace phenomenon” to an “illness” earlier this year. But it was a surprise to see the interest, to hear many people sharing their experiences in group discussions, and even more approaching us later to talk about it. This deserves a write-up of its own!

After DDaT Fest, I took a day off but not to go home and recover! Instead, I went to Galicia (north-west Spain) to continue experiments in hybrid ways of working. I play in a really badly timed folk duo. We published our first mini album on the 1st of March 2020, during the only festival we got to play that year. After a restart in 2022, Gerardo – the other half of the duo – moved from Wales to Galicia. But we enjoy playing together, and we are both experienced in remote working (IT style). Still, we are trying to find ways for Porembela to continue despite the 2,000km between us. It was an experiment, and it went well. We successfully tested the logistics and played two good concerts on Friday and Saturday. We have a short list of improvements to try in the next iteration, but definitely, there will be the next iteration, and we will make it work. The best part was that we got to play one of my xotas in Galicia. A traditional Galician xota (a folk dance) composed by a Pole in Wales was well received in its natural habitat. 

By Sunday night, I was back in London, ready for meetings starting early on Monday morning, all refreshed by the landscape change over the weekend, both geographical and mental. 

During my Monday visit to the Government Digital Service (GDS) office, I met with Charles – the Head of Data Architecture in the Central Digital and Data Office (CDDO). I wanted to discuss ways to progress my linked data and services ideas and how to start a cross-government community of practice (or interest). My talks and presentations over the summer and during DataConnect22 on the subject were very popular, and I have promised a few people to start a forum to progress it. But how? I know I could just do it, create a new slack channel or even a whole workspace. Still, there are already so many, and it seems the discussions are not getting us in any specific direction. So rather than starting from scratch, I’m trying to find a way to use what already exists, get some central support, and perhaps to help re-invigorate the work done there in the past. The details? I won’t share them here just yet, but I will be moving quickly now. I have spent a year convincing and influencing, preparing the ground. But in a proper Civil Service style, it seems my window of opportunity is closing. I have to act, or I will have to start the convincing and influencing all over again!

The same day, while in the GDS office, I discovered the Centre for Digital Public Services (CDPS). On the surface, this Welsh Government’s arms-length body appears to be a Welsh GDS copy. It got me interested straight away. I am a big fan of the GDS ideas (even if not always of their implementations) and the digital public service revolution they started. I have spent my time in the Civil Service trying to figure out how it works and why it doesn’t. Why are digital services so different here in my adopted home country – Wales? Why do local authorities appear to ignore it all, and why… too many questions to list here.

The point is: the idea of the Welsh GDS sparked my interest. Since then, I have spoken to a few people over the week – thank you for your time – and I was wrong. The Centre for Digital Public Services is very different from GDS and CDDO. The mission appears to be more pointed, the ambition more focused, and the drive to deliver fresher. The challenge in front of them is enormous, and a lot is at stake, but they are on a journey I will want to watch closely. 

Eventually, I got to Wednesday – my eighth day away from home, the day of the Data Challenge semi-finals. Since September, I have been working in a cross-functional, cross-departmental team to prepare our submission. We are trying to reduce the time it takes to transfer security clearances by looking at the data differently – as a personal, not an organisational asset. In addition, we think this would open many other possibilities to do with things like Disclosure Barring Service (DBS), qualifications, mobility of NHS staff between nations and many others. We have a great idea. We thought the presentation went well. And then we dropped out. We are still waiting for feedback more detailed than “Your presentation was great, the idea sound, but it was a very tough competition and we took a very long time to make a difficult decision”. 

But this is not the end. It could be a blog post of its own. Over the last three months, we have formed a solid team of people who really want to make it happen, and currently, the team spirit is “we show them judges!” and get it done anyway. For me, a relative newcomer, it has been a fantastic opportunity to understand better how the Civil Service works and get to know people in a few of the bigger departments. 

This wasn’t enough for the week. On Thursday my attention had to quickly switch to a recruitment campaign I’m helping with as a panel member. We are recruiting for a Structured Content Designer. Who that is? Well, in the public sector is a surprisingly unknown role and so it took some doing to think about the interviews which we held on Friday. They were very successful. We have seen some good candidates. We unanimously agreed on who was the best, so all that is left to hope the person will accept the offer. 

I spent last night volunteering as a first aider at our local Help Point in Swansea to relax after all of the above. It was a strange night: cold and quiet but with a lot of blood and three victims of violent assaults. I will leave the reflection on the graphic specifics in my private CPD folder. Still, there was something that night that brought Monday’s discovery of CDPS back. In between the calls, I watched our doctor and our operations manager struggling and cursing multiple local digital services they were trying to use. Wales seems to need to catch up to the wider UK’s digital service experience. It’s not good. Especially when it impacts things like 999 service provision. But it’s great that an organisation is committed to changing it for us all. 

There was more I could reflect on here in the open from just this last week, but it seems the week isn’t done yet. I have just had a call for any volunteers available to pick up some stacking 999 calls. So instead, I’ll quickly publish what I have, put on my green uniform and go out into the night. The new work week doesn’t start for another 12 hours! That’s plenty of time.

T-SQL Tuesday #114 – The SQL Puzzle Party

T-SQL Tuesday logoThere were times when I tried to look for puzzles to solve, especially the T-SQL puzzles (what happened to the T-SQL Challenge site?). Now I don’t. Life is challenging as it is, especially if you work with SQL Server and really try to understand what’s going on.

So rather than coming up with some contrived problem for you to solve as part of this edition of T-SQL Tuesday (thank you Matthew McGiffen) I will share something that surprised me only last week. And yes, I have solved it already, and will be blogging more about it soon so no there is no big price for solving my production issue here 😉

Here is the scenario

There is a table that stores millions of records. It has a primary key, a date when a record was processed, a bit column indicating whether it was processed or not, and some text fields that are used for something, but in our example, it’s just data that takes space on pages.

There is also an application which is using nHibernate to generate a T-SQL query that retrieves one (just one at a time) records from that table where IsProcessed = 0. There are 10-50 records like that at peak times, in a table which holds tens of millions of records so making it very, very fast should be easy with a tiny little covering filtered index. Well… it turns out, SQL Server prefers to scan the clustered index instead.

Have a look

The challenge setup

use tempdb
go
drop table if exists dbo.LongProcessingTable
if not exists(select 1 from sys.tables where name = 'LongProcessingTable')
create table LongProcessingTable (
Id int not null identity primary key
,ProcessedOn datetime2 null
,IsProcessed bit null
,SomeData nvarchar(1024) not null
)

-- just some text to fill up the space on pages
declare @sometext nvarchar(1024) = (
select string_agg(convert(char(1),name), '')
from sys.all_objects
)

-- create just 100k records with some random date values
-- at this time all records are marked as processed
insert into dbo.LongProcessingTable(ProcessedOn, IsProcessed, SomeData)
select top(100000)
dateadd(second, -abs(checksum(a.object_id, b.object_id)%10000), getdate())
,1
,@sometext
from sys.all_objects a
cross join sys.all_objects b

-- now mark 10 rows as not processed
update d set IsProcessed = 0, ProcessedOn = null
from (
select top (10) *
from dbo.LongProcessingTable d
order by ProcessedOn desc
) d

Now the query:

declare @IsProcessed bit = 0

select top(1) Id, SomeData
from dbo.LongProcessingTable
where IsProcessed = @IsProcessed

The above query comes from the application and cannot be changed. It is what it is. And to help you start, here is the index I thought would work, but doesn’t.

create index IX_LongProcessingTable_NotProcessedYet
on dbo.LongProcessingTable(IsProcessed) include (SomeData)
where IsProcessed = 0

The index gets ignored and the server goes for the table scan instead.
Of course, there was somebody who discovered it earlier. I wasn’t all that surprised that Erik Darling blogged about it in 2015, 2017 and 2018 it turns out, he even says ‘IT IS KNOWN’… well, it wasn’t to me. But even now, with that knowledge, I still cannot change the query, so what can I do? How to make this query more efficient without changing it, and without creating a covering indexing on the whole table which can contain hundreds of GB of data just to get one row.

If you are still reading… well, enjoy the challenge. I will follow up with a few comments and a couple of my attempts at solving the problem later this month (hopefully).