SQL Woes And Why Software Estimates Are (Almost) Always Wrong

Transcript is auto-generated and may contain errors.

all right I am just heading into the office here it's pretty crappy out looks like it was raining while I took a little nap but it's okay and I got to get some gas but it should be good to go Google Maps is like there's 15 accidents and whatever else going on um that's okay now this week though for me is a lot of connects uh at Microsoft and again if you're not familiar with what connects are at Microsoft it's a it's not the conversation that is your the result of a performance review but it's like a checkpoint that we leverage for level setting expectations and then uh like these types of things get used for performance reviews right so for example if I'm working with an employee and they're they're doing really well and we want to make sure that the result of the

connect and the conversation around the connect is that they understand that like they go okay I know I'm meeting expectations or exceeding expectations uh good news or the opposite right if someone is underperforming or there's some challenges that we can use a connect to make sure that that they they understand that ultimately like I always say one of the goals is that there are no surpris Rises so the obvious bad scenario is if someone thinks that they're doing well and when it comes time for uh the actual performance reviews and these happen by the way like the results of them are delivered in August so we've already passed that for the Cal like the the whole year um so if someone's doing not great and they don't realize that then it's a it's a really bad situation because then they're caught off guard they're they're

frustrated they're angry they're all like and rightfully so because it's confusing right so not a good spot to be in but I also say like it's not I mean it's a better spot but it's not great if someone is shocked that they are getting promoted or good rewards um obviously that's like a a good surprise but um the difference is that I think someone can be happy and excited and being like Oh like this is um I don't know like oh it's more than I expected or I didn't think that it was going to happen right now um but it's different if they were like I had no idea that I was doing well so if they're completely caught off guard by even good news then to me that tells me I'm not doing a good enough job communicating so no surprises is the goal

uh and that way people are able to focus on what's important and you know move forward and make progress so I'm doing a lot of that this week and uh it's one of those things where like there's there's simply not enough time in the day in my opinion to do it properly so because I do my connect conversations so basically if I back up a little bit employees will fill out a connect so it's a document where they can talk about their their wins their um you know their setbacks and challenges and then what their goals are coming up and they fill that out and then I respond to it in the document with my own comments so that it is truly documented and um and then we have a conversation about it so that's the the typical flow of things but there's also and

I've talked about this before but there's this uh like a talent guide It's like a rubric and it has a lot more detail around expectations for certain traits characteristics and things like that at different levels and I like making sure that when I have my connect conversations and when I'm writing information into a connect that I have both of these things addressed and it's not mandatory or required to have this Talent guide thing done but in my opinion in my opinion it should be mandatory so I take it upon myself to make sure that I'm doing that for each employee and that way they can see for every single thing like here's how here's my perspective around how you're addressing this or if there's opportunities we should try to to find for you to make sure that you get this exposure um and then I

I Bridge them both together so that when I'm leaving comments in the connect and giving my feedback like I can literally take pieces from that Talent guide and and show individuals like hey on this project like you were demonstrating this and that way the feedback I've gotten from employees that have gone through this with me is that they think it makes an enormous difference so I keep doing it but the the challenge right now is like I said there's there's just simply not enough time in the day so um I have to have the conversations during the day with employees but then that leaves me essentially no time at all uh to be responding to them because it takes depending on how much people write I like to try and match it um so if people don't write a lot they don't have to but

if they don't write a lot I'm not going to go into Super detail I'll give them my response as a kind of match similarly um and if they write a lot then I try to make sure that I you know not not trying to like word count match but I'm trying to make sure that if they were very verbose that I try to give them verbose feedback as well so um it means that sometimes I end up like spending just in the right up part in the response like a couple of hours uh so like last night for example I had one person that wrote a ton and one person that wrote a little kind of balanced out but it took me over 3 hours to to kind of get through and just do the written stuff and this is just like after work so

um the fact that I'm driving into work a little bit later today again I'm trying to make sure that I am balancing things out it's 100% Going to result in me working more hours this week which is totally fine um this is the kind of stuff that I think is really important as an engineering manager so you know I'm not bothered by it but I'm going to try to be responsible with my own time so uh if I have to drive in a little later leave a little earlier that's cool um in this particular case I know I have a meeting at the very end of the day so I am already going to be staying a little later at the office but that's kind of where things are at so I'm just going to stop here to get gas obviously you don't need to

watch that this will be the one part I trim out this person is parked right in the middle so I can't get gas maybe I can um but I'll trim this part out and then we will resume on a fun topic from the weekend okay we're ready to get back at it it was pretty frustrating there's like a there like a guy parked in the middle of like three pumps I don't know like maybe that's just I'm sure he didn't pull up that way and decide to be a jerk um so I got to a pump and then there's just like a cup instead of a the actual like nozzle it's like okay like pump's not working that gas station I don't know why I should learned my lesson that gas station is always stupid there's always something stupid going on in that gas station

whether it's pumps out of order or something else it's stupid but it's so convenient okay so the topic I wanted to talk about on the drive today is around this funny situation from the weekend and it's a continuation of the previous video so if you watched the last one and ended up here awesome um if not I'll catch up pretty briefly so I was talking about uh like users making feature requests and them doing the solutioning around the feature requests and why why I think that's a dangerous trap to just feed into accepting a solution from a user instead of understanding why they're they're after it so um one of the things that came up over the weekend was around this this keyword search capability for brand ghost and for a little bit more context the uh the team that is building brand ghost uh

there's a bunch of people in Canada so um this past weekend was Canadian Thanksgiving yes it's a different time than American Thanksgiving no it is no different uh it's just different time and it's not as uh it's I don't want to say it's not as celebrated that's the wrong phrasing but it's not as big of a deal like Thanksgiving in the US is like ridiculously big and in Canada it's like in my opinion it's kind of like Easter thanks iing it's just like just another kind of holiday but us is like Thanksgiving like take the whole year off um so anyway it's uh Thanksgiving in Canada and I think for a couple of the guys they were taking some extended holidays to be with their family so uh good opportunity to do it before the end of the year but what that means and this

is not a criticism at all this is just me explaining the scenario it means that we're not going to be able to uh deliver some things as effectively during this period where they have some time off so not a big deal but we just have to understand that so with that said um I had a user request for some keyword searching and I said okay like now that I've dug into it and I understand what's up uh we're not going to be doing semantic search right away I think that will be a cool thing to do later because we have to do uh a lot of sort of uh like vector based indexing on the content anyway uh to do better work with AI on content so that's coming we could do semantic search later I think that'll be cool but for now I said

hey you know what I can whip up the back end to write an API that's going to feedback the list of content like I basically already have all of that piped through I just don't have an ability to provide a search query and I'm like this should be pretty easy like I can probably get this done honestly in like an hour uh tops and sure enough I was uh like you know I could copy and paste a route uh I could change some of the parameters around and honestly I had within like 10 minutes I had something end to end where I was like this should at least allow someone in the front end when they're back from vacation to be able to uh to access this query things and great like cool at least at least for the like 95% happy path like are

there edge cases and stuff I might need to think through sure but to at least get it usable I feel very good about it so when it came time to go try this out um I realized oh crap like okay that the amount of data we have is not huge uh it will be someday but right now it's not huge and I was like you know what doing a a keyword search uh without an index is going to be real crappy so I said I'm going to go ahead and just make sure that there's an index on the column that has the content I want to search over so again I'm not concerned about that I go to my SQL workbench and uh go to apply an index on this column and then I realized it's a text column and text columns are not allowed

to have just a normal index on them she like oh no um so now I'm having a bit of a panic where I'm going okay like it will probably work without it but it's going to feel kind of ridiculous if we don't have much content someone goes to search and it's just like real slow so I I had a bit of a moment and I don't know why I didn't think about this before but I was like oh like literally there is a full text index that you can use on a text column and I said okay great like have a work around so I said okay I got to go back to the query um change it up and uh it's kind of weird I I like writing my own sequel but there's some things like uh like a a full text search on

a column is not something I write that often so I'm like I don't remember the syntax go look it up I'm like okay it looks straightforward drop it in awesome okay now I'm like time to actually start testing this out and I go to run it and uh it doesn't work and it's not working because it's just timing out so it's taking over 30 seconds it's taking over 30 seconds to go run this query and I'm going this is impossible like there's there's literally not that much data I feel like I could go through the bites by hand faster and find the find the text uh probably not but you know it's there's honestly not that much there's literally zero excuse for it to be taking 30 seconds in my opinion okay so where do we go from here I'm like all right well the

query that I'm using is basically identical to another one it's just that there's an additional clause on it for the um what's it called the uh the actual full Tech search so I said okay like I suspect my full Tech search is totally bricking this thing somehow even though there's an index now but like let me remove it and then I realized it's also not working so well on the Bas query it's finishing but it's like it's real slow and I'm going oh no so here's the issue I I basically I'm touching a piece of code trying to it's not quite extending it but I'm I'm reusing the pattern that's already there building upon it and the base pattern that I'm using is already proving to be problematic so now I'm about to propagate that pattern even further and I'm going oh no like this

ain't good like I don't want to go build this feature and use this pattern if it's that awful so part of me is going like I don't understand why like we're not seeing this in production like something's really weird here but like I can repeat this nonstop and I can repeat it I tested it outside of our C application cuz I'm going maybe I don't know how maybe there's something going on with the app itself I'm going to go to my SQL workbench and run it same behavior right it's taking too long so at this point I'm I'm like kind of freaking out I'm going okay I got this really simple thing I'm supposed to do I'm noticing like this really bad existing Behavior so so like what's up here and I start trying to do like explain query plans on the actual query itself

I'm feeding it to chat GPT to see if I can get any more ideas it's suggesting more indices and stuff and I'm like I don't know man like it wants some compound indices but I'm like these columns are already individually indexed and like there's legitimately not that much data so like I can't I can't explain what's going on here so um I want to kind of come back to the generalized view of this scenario I'm talking about for like General software engineering I'm going to come back and explain how I ended up kind of making some progress on it so uh the idea that I wanted to talk about here though is like is kind of like around estimates and and prioritizing things so in this situation I decided that I would prioritize my time trying to implement this backend change that was not critical

to get done and in in the sense of urgency there was no urgency because if I got it done in 2 seconds I don't have someone in the front end that's going to be able to even code against it they're on vacation so it's not urgent in that sense and the point and the reason that I prioritize it is because I said it should be so simple that it would be almost like kind of silly just to delay it because I can go knock this out right away but the reality is it's not how it happened in practice and I think that this is important to think about because this kind of thing happens all of the time in software engineering maybe maybe you're not as kind of silly as I am in terms of like how I messed this up but like it happens

where people end up you know saying oh it should be quick and then and then it's not now how I wanted to talk about this in like a general software engineering sense is that I think it's really important when you're working in teams to be able to call this stuff out like right away so if you're estimating that something's going to take like x amount of time right so whe like whether it's a day whether it's an hour whether it's a week whatever um you know I'm not big on estimates in the first place but at least if you're like we have a feeling it should be super quick or take roughly some amount of time if you are noticing that it is not on track I think that it's incredibly important to raise awareness of that as soon as possible and I think that

some people are nervous about this they're fearful because like if someone said we think that this should take like an hour because it's so simple and all of of a sudden it's not taking an hour it's like taking significantly longer do people feel like they're like they're letting down their team like they want it to be accountable you know I'm going to take this on I'm going to deliver it but all of a sudden like now you can't get it done you're blocked like are you are you not good enough are you not smart enough are you missing the point like I think some people get nervous that they're letting people down and that they might look bad so what they end up doing is they just double down on not saying anything they're like no no no like I'm going to get it done

I'm going to get it done um and that thing that was supposed to take an hour is now you're two three hours in and then you start panicking more and you're like oh man like this is now getting really embarrassing because we supposed to be so easy and you just keep um keep doubling down on the fact that like you you are trying to be accountable for it but I think that this is very problematic and maybe it sounds obvious as I'm saying it um cuz sometimes my examples feel a little bit contrived and exaggerated but but honestly people do this kind of thing and it's like I don't blame them but I think we need to do a better job in general like fostering a culture where people understand that like no one is assuming that we're going to have the ability to perfectly

estimate how much time something will take it's just completely unrealistic but why it's helpful to talk about things going off track early is because prioritization so to give you an example um my my situation from the weekend like I am the product owner I'm doing what I want um now if someone else was the product owner and they said hey Nick like yeah I think that's a good one to jump on because like because it is quick right it's like it's not urgent but it's so it's so simple you know engineering team's agreeing it's simple we should just knock it out and get it done the um the challenge though is like it's not and then all of a sudden if you're pouring more time into it then what ends up happening is that it's potentially a missed opportunity to be working on other more

important things from a like a priority perspective so to give you like to continue on that example if if I told the product owner hey like we said it was going to take an hour and like you know I'm already like 2 hours in and I I'm really thinking there's some complication here and like we might want to rethink this that product owner has an opportunity to say okay you know what we said it wasn't urgent Let's uh you know let's park it for now uh like thank you for working on it let's make sure that we have a note on where the complication was so we can we can re-evaluate it but like let's switch gears to this other thing because now that we know that's going to take longer no worries let's pivot to something else like you have that ability to do

uh that thing that we call Agile where you're being agile you're being Nimble you're pivoting to things that might make more sense conversely if you stuck to your plan and you're going over budget on time like you end up in these situations where you're spending potentially a lot of time on something that's truly just not important at that point in time so so my perspective on this is just that communicating this kind of thing early and often if you're off track there's no shame in it right like and and people should build cultures around this where there is no shame because as soon as people start to feel like shameful of like things taking too long and they're worried and they're embarrassed they're not going to speak up about it they're going to do the exact opposite they're going to keep hiding from it because

they are embarrassed they are afraid and they don't want to look stupid they don't want to be criticized so I think we have to do a good job in being welcoming of this kind of stuff and saying hey look like you know if it's off it's totally fine like it's not there's so many things that aren't like a person's fault so in the example maybe maybe this one was my fault I don't know but um there's there could be all sorts of things we didn't account for that explain why something ends up taking longer and we don't know until we go do it so like to to blanket statement just blame people for things taking longer is kind of silly um and like I encourage people to shy away from that because uh it's just not it's not helpful now there are going to be

situations where people maybe they need coaching maybe they need to skill up and stuff and like that can be addressed separately like someone should work with the you know engineering manager should work with their employee or the other way around right to initiate it um but that that should be add I'm not saying that there isn't room for that kind of thing but I'm saying the um sort of the majority case that we should be focusing on is when things are not on track we should be speaking up about them so that we can make more informed decisions so to go back to my situation from the weekend um I said okay like I have no idea why this is happening but like I don't want to like go testing stuff in production because I'm learning I'm learning that we're and I've already talked about

this in these Vlogs but I'm learning that we're at the point now where I'm feeling nervous about doing things in prod uh we have database backups and stuff it's not like it's not going to be catastrophic by any means but I I don't want to risk any downtime just because it's not fair to people especially if I have all the tools to be able to do it locally so I said you know what I'm going to go run this stuff locally and I said there's a way that I can do that because I can literally dump the database and then repport all the content locally I can do this on my machine and that way I can experiment with the query I can if I need to index things I can do that so that's exactly what I did I I dumped the database and

then I wrote like a I don't want to say say I wrote a functional test I was using a testing framework to to go do it but uh imported all the data ran the query against it and I I put the uh you know from from going back and forth the chat gbt I said okay it wants all these compound indices on there I'm going to jam them on and we're going to see and it went from being like you know not completing in 30 seconds to executing in uh in like 1 second and I was like oh man okay hm maybe there's something going on here and uh I was like okay like that's good to know but like now that means I'm going to have to figure out a strategy for applying the indices on the live data making sure I'm not locking

stuff up but at least I know the query itself can be structured in a way that's fast enough like that's that's a good spot to be in like I feel good about this now and then so I start writing tests on this stuff um because I'm like I want to make sure I have my b is covered uh cuz there's more there's other things going on in this query and uh the keyword searching part was just an addition so I said while I'm here I will go test the crap out of this thing uh on on the related queries as well and then have coverage on the keyword one and then I had this bright idea where I said you know what I'm going to go ahead and run this again but I'm not going to add those indices that chat GPT recommend recomended cuz

I'm being stubborn and I said like I'm pretty confident there's already sufficient indices on this and there's not a lot of data so like it should go fast I think I needed my query restructure but it should go fast and uh sure enough I run it and I measure the performance of it and it's like sub 1 second so it was like 7 seconds in some of the test that I did literally on the same data that's in my production database just in a clone of it so I'm going huh like that's pretty interesting and so I haven't concluded but uh I haven't concluded but my suspicion is that things weren't working effectively when I was going from my machine to the remote database something weird about how that query was working was causing some some type of round trip time or something else to

happen but when it was running locally and when it's in production it's going to be running on the same uh virtual Network so it should have the same characteristics of me running it locally uh to to a database it's local so basically long story short that's why I wasn't observing the characteristics in production on the existing query because the setup is the same it's a different machines but same network and uh that's why as soon as I replicated that behavior and I was running it on the same machine problem went away um so anyway that was my kind of like my stupid moment I spent so much time on it that's why I was saying maybe in this case yeah that was my fault but you know lesson learned I guess is uh I should have I should have leaned into that feeling of like

we're running this thing in prod like why don't I see this these characteristics in pra and instead of like over indexing on how to no pun intended on the index part but instead of like over indexing on on how to go fix this query and and index things it was like am I comparing Apples to Apples right now or is this like not the same situation wasn't the same situation so anyway I spent way too long on this thing that was supposed to be very quick but that's okay so summary don't be dumb like me uh but in all seriousness if you're if you're prioritizing things and trying to estimate how long they take and that's a factor in the priority you know something can be done quick or whatever it happens to be then I think it's it's important to have the conversation around

if that expectation is not lining up and ensuring that we create environments where that is the case that people can speak up about it um a lot of stuff sorry I'm just watching this guy was nervous he was going to try to ducky into the lane uh a lot of stuff though I think in software engineering is like lot of it comes down to making sure that people have like a safe place right if people are fearful uh people are uncomfortable people feel like they can't ask questions they can't reach out to people they can't ask for help when you have these things in place it just like it slows a team down to a like a halt almost so especially if you're in like a management leadership position or even if you're a more like senior engineer you don't have to be senior even

you can do this as a junior engineer but like you have to lean into your your leadership skills so whether that's formally as part of your role or informally um you know trying to to build that culture right we cannot you can't dictate a culture you can't just say I declare that everyone must be comfortable be nice if we could do that but it's not how it works so um you can't dictate the culture but you can do the things to set up your team for Success so that that culture can form so culture is like the The observed uh result of all these other things that we do it's not the thing that we we don't just do culture and then like it happens you do other things you set things up in particular ways that enable a culture to flourish and depending on

what you're doing will change what that culture is so in this example I think leading by example to show you know when things are off track it's okay to speak up like you're not punished for it we learn we adjust we adapt whole team does better these are all good things and if you can demonstrate that there's this good opportunity then people will lean into it but that's the one of the videos from one of the talks I guess from one of the videos that did not survive Monday so thanks for watching and I'll see you next time