BLOG – 10 Mistakes Committed by Inexperienced Programmers in Relation to Databases

1. Lack of Understanding

Inexperienced programmers seem to, at times, not fully understand the complexity of their actions. Knowing the syntax of SQL or how to perform a join are obvious points on the checklist of necessary skills. But sometimes these skills are not followed by a grasp of how queries impact the database as a whole. Without a solid understanding, programmers might unknowingly write queries that are inefficient or even harmful.

The crux of the problem often lies in a lack of detailed knowledge. It could be, for example, about how databases manage data, how different SQL commands affect performance, and how proper indexing works. Moreover, a shallow understanding of the database schema can lead to redundant or incorrect data retrieval. This not only slows down the database but can also lead to incorrect data analysis, impacting business decisions.

2. Using Outdated Advice

Luckily, every time a piece of advice is needed, a novice programmer has the whole 2.25 billion pages at their disposal (even more considering this number was true in 2022). Yet, with all that information around us, or actually because there’s so much information around us, some of it is outdated. What worked a decade ago might not be the best approach today. It’s due to advancements in database management systems, changes in best practices, and improvements in hardware capabilities.

For example, certain SQL optimization tips that were once critical for performance improvement, such as manually creating indexes for every join operation, might now be automatically handled by more advanced database engines. Adhering rigidly to these old methodologies can lead to wasted effort and suboptimal performance. It’s especially relevant when newer versions of databases optimize these processes automatically.

3. No Implementation Plan

Getting into database changes without an implementation plan is like going on a road trip. But on the road trip you have no map, a phone, or even a clear destination. It’s adventurous but probably not going to end well. Inexperienced programmers often make the mistake of rushing into database modifications with enthusiasm but without a structured plan. This leads to changes that can cause more chaos than improvement.

Implementing changes involves much more than just tweaking a bit of SQL here or adding an index there. Without a clear plan, these changes can disrupt service, corrupt data, or introduce new bugs into a system that was working just fine before. 

4. No Query Verification Plan

Inexperienced programmers often deploy queries directly into production environments without adequate verification, assuming that if a query runs without errors, it must be correct and efficient. This approach can lead to significant problems, including performance bottlenecks, incorrect data retrieval, or even system downtime, due to untested or improperly tested queries being introduced to live environments.

Skipping the verification of queries means skipping a crucial step in ensuring they function correctly under actual operating conditions and perform efficiently under typical load scenarios. Without this step, queries that seemed harmless in a controlled development environment could behave unpredictably or become problematic when subjected to real-world pressures.

5. No Rollback Plan

A significant oversight that we’ve all seen is the absence of a rollback plan for database changes. Without a clear strategy to revert changes made to the database, they leave no safety net if something goes wrong. This can lead to prolonged downtime and data discrepancies, as well as complicate the recovery process.

For instance, if some adds a new column to store crucial data and it inadvertently disrupts other database functions or the data migration leads to loss or corruption, the absence of a rollback plan would mean there is no quick way to restore order. The result could be operational disruptions and an urgent, often chaotic effort to fix the issues without compromising data integrity.

6. Failure to Verify Performance at Scale

Inexperienced programmers often overlook the importance of verifying database performance at scale. While a query or system might function flawlessly under the light load of a few test cases, it could falter when subjected to the full weight of real-world usage. Not testing how a system performs under expected operational loads can lead to major issues, including slow response times, timeouts, and transaction failures when the system is finally deployed.

Why do they decide not to follow through with the tests then?

  • Overconfidence in Code Efficiency
  • Tight Deadlines
  • Resource Constraints
  • Underestimation of Load Impact

7. Lack of Communication

Let’s be honest, the majority of people who get into programming do not do it because of their inherent love of talking to people. Often, programmers are drawn to the field for the love of code, problem-solving, and perhaps the quiet focus it demands. However, when it comes to managing and developing databases within a company, silencing the chatter is not quite the boon it might seem. Keeping communication lines closed can lead to isolated efforts, redundant work, and missed opportunities to leverage collective knowledge.

When you have a room full of people trying to figure out a solution, without talking to each other,  they could either spend hours, maybe days, figuring it out alone—or start a conversation, bounce some ideas around, and perhaps solve it together in a fraction of the time. 

8. Undefined SLA or Baseline

Having, or not having a clear Service Level Agreement (SLA) or a performance baseline can be a difference between a predictable, optimized system and an abomination of one. 

No clear performance baseline or SLA means that there is no agreed-upon reference point for evaluating the system’s efficiency or responsiveness. This lack of clarity can lead to disagreements between developers and clients or among team members themselves about whether the system is performing adequately. For example, a situation when a database query returns results in two seconds – is that fast enough? Without benchmarks or performance targets, such a question can become impossible to answer.

Not to mention that without well-defined expectations, optimizing system performance becomes a moving target. Programmers may not know which aspects of the system to prioritize for improvement or how to allocate resources effectively. This can result in wasted effort on something that barely impacts user satisfaction or business goals.

9. Loss of Diagnostic Data in Oracle

By default, Oracle systems will purge diagnostic logs after eight days. This default setting can catch inexperienced programmers off-guard, leading to a potential blackout in data; data that could be crucial for diagnosing issues or understanding performance trends over time.

Why does this matter? Diagnostic data in databases like Oracle includes critical information such as error logs, execution histories, and system health indicators. These logs are invaluable for backtracking through a database’s operations to pinpoint where things might have gone wrong. They also what plays a significant role in tuning the database for that perfect, optimal performance based on past activity.

10. Lack of Holistic Vision: They lack an overall vision for the project or system.

Lastly, it’s way too easy to focus on immediate tasks or short-term goals without considering the broader implications of their work on the system as a whole. The ability to look further comes with experience and the lack of holistic vision can lead to a piecemeal approach. In such cases, individual components are developed or optimized without regard to how they fit into the larger project. The result is a system that may function in parts but lacks cohesion and scalability as a whole.