Voice of the DBA

Category: Editorial

  • Bad Stored Procedures

    I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

    Or is it?

    I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

    Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

    I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

    The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

    The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • When Do We Worry About Scalability?

    “We need to get the code written for feature X. Can you finish this query today?”

    We’ve all heard some variation of that request. We have a request or demand, and we need to get it done. We need to get code out so our business can advance, sell more things, get more customers, etc. There’s always some reason to get new code pushed to production quickly.

    However, many technical people want to ensure their code works well. At least, I believe most do. While most people can write code that works and meets the requirement, some don’t know how to write code that performs well or don’t know how to test their code to check. Often there isn’t a large workload in dev or test environments to verify things.

    There may not be a large workload in production either, at least not at first.

    So, what do you worry about first: your code being used or performing well? That’s a similar question to this one: Worry about Scalability or Popularity First? While most of us don’t work for a startup and our organizations have some sort of financial stability, does popularity matter?

    I’d say that for any feature you build, whether a startup mobile app or a legacy ERP system, you’re still looking at this type of question. You want to know if it’s used, and how often. That might determine if you spend more time on this feature or area. Maybe you have some idea of popularity, or just plan old use of the feature. In that case, certainly make sure it will scale to not only meet your data size now, but plan for some level of growth across the next 6-12 months.

    If it’s a new area of functionality for your application, then maybe you have no idea. In that case, the DevOps approach is get something working, a minimally viable version of your code or query, and then tune it later if it becomes a problem. Many technical people approach the endless number of tickets and requests they get like this.

    The problem is management often doesn’t budget in time to clean up the technical debt (to care about scalability).

    My view for database code is that we should always be leveling up our database code knowledge. If we deploy bad code in production, and can’t fix it, then at least we can avoid adding to the problem by writing the same poorly performing code again. Learn a better way to write that type of query. Whether you’re splitting strings, finding islands and gaps, calculating running totals, or anything else. Learn what works well and write that code next time.

    That helps your team balance the scalability and popularity-chase by producing good code the first time. Or at least, the next time.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Technology and Privacy

    There can be a big divide among tech professionals on how they view data privacy. Some don’t worry too much, and some are very upset about the lack of data privacy and poor data handling practices from many organizations. Most people are probably in the middLe. I do know that every time I post about a Tesla, there are many people who mention the incredible amount of data Tesla collects, and how that makes them nervous.

    Tesla discloses that they collect, and I get copies of my data with my own logger. I find the data interesting and I glance at it over time to look for trends. I’m still happy that my top destination in the last year (after home) is the gym 😉

    Other car companies collect data on you and GM is in trouble in Texas. I suspect other companies do this, likely to learn more about how to build better cars, but also to get data they may be able to sell. The same thing likely happens with most of your data. Certainly, mobile phone companies collect lots of data, and I wouldn’t be surprised if a lot of your rentals/purchases/visits are being captured and sold to others from all sorts of vendors. Some of you might even be capturing usage data in your software that your customers might not like.

    I don’t know how I feel about this. I do think disclosure is important but not in the form of some EULA or a contract that people have to click through. I think there ought to be choice of what data is being collected and how it’s used. Or if it can be used. While I often just accept or dismiss cookie banners, I do appreciate that I can refuse cookies and still use more websites. I ought to be able to easily opt out from most data collection from most companies. It really should be optional.

    The world isn’t going back to analog devices and actions. Digital technology, with software invading most parts of your life, is here. I wouldn’t be surprised to find out most new appliances have a phone home feature that captures data and sends it back through unsecured wi-fi. A good reason to put a password on all your networks.

    In portions of the world, there are restrictions on data and privacy. They could be stronger, and the laws could limit more of what companies can do. Many companies still make money or run fine.

    They will be fine. Organizations always find ways to work within whatever system exists.

    I think that would be a good thing for most of us if more data privacy restrictions were in place. However, I don’t know how most of us would even go about helping enact them.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • AI Sings the Blues

    Quite a few of you aren’t thrilled or enamored by GenAI (Generative Artificial Intelligence) with things like ChatGPT or CoPilot. Some of you love it or find it fascinating. My guess is many of you are like me and you’re not quite sure how useful this is with a healthy dose of skepticism.

    No matter how you feel, I find this piece on AI Blues fascinating to read. It talks about some of the problems with AI and how people are becoming less tolerant of the small errors that AIs make. I think people might be upset with some of the issues, but I also think these are types of things we might see in a colleague as well, and we might tolerate them for much longer than we think.

    The thing in the piece that really caught my eye is that training models to make less errors might make them less creative. I don’t think they are very creative, but I do think part of the idea is that they might do something better than we could do, at least for those of us working in a space where we’re a beginner to intermediate (or advanced beginner). In many cases, we’d expect better output from a model.

    The note about training not being often or in-depth enough as well is interesting. Perhaps some of the models ought to be trained separately, with some having a more specific focus, like learning Python and producing very few errors, or learning Python to produce interesting solutions to large scale problems even if there are some errors in the code.

    I could see similarly more tightly trained models for other areas, such as marketing email or legal issues. Maybe we need a lot of different models tailored for different jobs. Of course, if companies are struggling to generate revenue on AI already (and I think they are), then can they afford to train and retrain specific models focused for smaller audiences?

    Maybe, and maybe Moore’s law will help bail out some AI experiments, but I’m not sure. For now, I continue to experiment, and I think skilled tech people ought to do the same. I don’t think it will replace us, but I do think it can be a useful tool in many ways to help us become more productive.

    Steve Jones