8

Suppose we are using java or python to access the database. Then is it considered a wastage of time and unnecessary to learn the kind of data structures and objects being used inside sql?

Please answer in reference to the software industry. Please try to tell in which cases it will be good to have knowledge of such things.

I've an argument with someone who says that it's unnecessary to learn such things.

  • 11
    Whoever you're arguing with needs to learn the inevitability of leaky abstractions. – Alternatex Jun 24 '16 at 21:56
  • 5
    @Alternatex - should probably have linked the source of that piece of wisdom. – Jules Jun 24 '16 at 22:40
  • If all you know is how to use a hammer, you treat every problem as if its a nail.... – gbjbaanb Jun 28 '16 at 7:24
20

A few years ago I worked on an application that was written by somebody who had clearly never learned how SQL databases work. I was given a problem report to fix -- the main status summary page, which had always been slow, had now started to be so slow that it was hitting the server script execution timeout (of 3 minutes) during rendering. It seemed that as the number of clients in the system was increasing, the time to render the status page was increasing quadratically.

It didn't take me long to notice the problem, which was that the page used a query that amalgamated data from two different tables, neither of which had any indices. Because each table had size that was growing in O(n) with the number of clients, the query was taking O(n^2) time to execute because it was fetching each row of the first table, and for each of those rows it was fetching every row of the second table to compare them.

Solving the problem took minutes, and anyone who understands how SQL databases work would have been able to do so just as quickly. The original author didn't, so left behind a totally inadequate solution.

You need to understand how (at least in general terms) a technology works in order to avoid making horrendous mistakes like this one.

  • How about the kind of object that the SQL will return for a particular query? Is it necessary to know such details? The argument put forward before me is that since the language querying the database such as Java converts the SQL object into another form before returning it to the calling code, we don't need to know the kind of object the bare-bone SQL returned. – aste123 Jun 25 '16 at 6:10
  • 2
    @aste123 It's important to understand any differences between the data types used by the database and your host language, because they could cause difficulties in conversions. Consider dates, for example. Many databases have a much smaller range of dates they can store than Java does (SQL Server, for example, will reject any date prior to the year 1753, and MySQL prior to 1001, while both reject dates after 9999). – Jules Jun 28 '16 at 7:48
5

Don't discount the possibility that you'll need to actually go into the database and query it directly as part of a debugging process. If you ever end up doing that, you'll definitely want to know all about the database technology and how your particular database is structured. Maybe it won't happen. But if it does (and in my experience it always does at some point) you'll need that knowledge.

But let's assume that you'll never need to go look in the database directly for any reason at all. Lets say you're using the ORM in a way consistent with all the best practices laid out by the community. You could make a performant app without any major mistakes/bottleneck/inefficiencies wrt to the data. But if you don't really understand the underlying database, you wouldn't really understand why you're doing things the way you are. Worse, you'd not really understand how the best practices apply to your specific use case. These facts should sow some doubt that you're creating the optimal solution. Your solution may work, but you'll not be able to say "this is the best solution" with any real confidence. If you can't say that, you're not a great asset in the eyes of your company and if you do say that and you're wrong, that's going to look terrible for you.

Beyond just the philosophical hangups I have about not learning the fundamentals of your tech stack, I deal with tangible reasons to know your stack from top to bottom on a daily basis. In my company we have a huge monolith handling enormous amounts of data. Things are modeled well, but there are dozens upon dozens of types of objects in the application and the relationships between them are an amazing web of foreign keys and association tables. Frankly, if you never look in the SQL and just dive into the app (though everything is modeled correctly in the app and uses the ORM and established best practices for that ORM), figuring out how to get this bit of information given this other bit over here can be a near impossible chore. But if you can dive into the DB, you can see all the fields in each model, follow the connections between tables, figure out a path from one piece to the other, test it with a query, then go find the proper models to do it through the ORM quickly and efficiently. I wouldn't be half the asset to my company that I am if I didn't have a high level of comfort with bare-metal SQL.

5

Only up to a point

As a software developer, you'll probably have to query and update the database, and knowing how the DB operates is critical to avoiding bad queries, inefficient joins and so on. You might have a dedicated DBA who can decide where to add indexes ir partition the database, but you can't count on it, not in small companies and not always in large ones either.

However

While you should know what indexes are and how they should be used, you probaby don't need to know how they work internally. The internal implementation details are just that - implementation details.

Knowing how to check an SQL query plan and build your code accordingly ia part of the API your DB exposes. Knowing the internal algorithms and data structures it uses to achieve it? Not. So much. As an analogy, I should know the performance implications of saving files to disk. I don't need to care about how my filesystem is implemented.

However to the However

If, as clarifying comments show, the question is about understanding DB access vs. relying purely on ORMs and other code abstractions, the answer is overwhelmingly "yes, you should know DB access". Not every project uses or can use an ORM, and ORMs aren't ideal for certain tasks (reports, bulk inserts, and more).

  • How about the kind of object that the SQL will return for a particular query? Is it necessary to know such details? The argument put forward before me is that since the language querying the database such as Java converts the SQL object into another form before returning it to the calling code, we don't need to know the kind of object the bare-bone SQL returned. – aste123 Jun 25 '16 at 6:09
  • @aste123 here's an example why you do care: what are the range of dates you can put in a SQL datetime column? What are the range of dates you can put in a Java datetime variable that gets read from the DB? If the two are not exactly the same, you can end up with problems that you'll have no clue about fixing. But, sure, the average programmer doesn't have to care, but the great programmer does, always.. – gbjbaanb Jun 28 '16 at 7:26
  • @Jules well I never! ... still, great minds... probably have the same damned datetime related annoyances :-) – gbjbaanb Jun 28 '16 at 8:52
3

It is absolutely worth the time! Being a full stack developer enables you to efficiently produce value-added solutions. I've seen all too often communication breakdowns and silo'd development... Triple the development time and half the quality.

At the end of the day, the more skills you have, the more valuable you will be.

3

If you profess to knowing nothing about cars, would I be happy with you servicing the brakes on mine? I think not.

Databases are noticeably different from the data structures that you're used to working with in programming. They have their own oddities and idiosyncrasies and other things that will bite you in the Application Performance if you don't have a grasp of them.

I have encountered people with this "I don't need to know Databases" mentality; most of them regard Databases as nothing more than Spreadsheets and produce applications that perform appallingly badly as a result.

That said, you don't need to know how databases work internally.

Do get to know the Logical stuff; Tables, Indexes, Views and the like.

Don't get caught up in the implementation details of how a particular DBMS handles these things; they all do it differently from one another (and sometimes between versions of themselves!), so a general "overview" will serve you best.

2

You absolutely need to know. For example, if your database is storing dates, you need to know what kind of precision you can expect. If you're storing a timestamp in a DATE field, you should know if the database is going to truncate your value to the nearest second (or worse, the nearest day). You should also know that values coming from a NUMBER(9,2) column have to be stored in a floating-point variable, while those in a NUMBER(15,0) can be stored as integers. You might also find it handy to know little oddities like Oracle's CHAR columns are blank-padded to the length specified, while VARCHAR2 columns are not. And their LONG data type actually stores variable-length strings, not numbers.

Every database has their quirks, and you should know what they are (or at least what to look for).

1

Understanding how things work under the hood will help you debug your queries for performance & storage considerations.

For example, a range query will perform better with a B-tree type of index. And when doing joins, you can add hints to the query engine on whether to use HASH or MERGE joins. And on the physical side, you can distribute tables in one database to different physical disk partitions to minimize head contention (probably still suitable even with SSDs).

0

First you need to be clear on what SQL is and is not. SQL is a query language and data manipulation language used for accessing and manipulating data in a relational database. But the schema and data objects (tables, columns, indexes, constraints) in the database are not "in SQL", SQL is just one possible language for querying and manipulating the data.

In order to be able to work effectively with a relational database, you need to understand tables, columns, datatypes, primary keys, foreign keys and indexes. You also need to understand the basics of querying: projection, filters, joins. You need to understand the basics of normalization.

But none of these things in principle requires you to touch SQL. You may be able to design the database schema in a GUI designer, and you may be able to write queries and updates in some other language like SqlAlchemy for Python or Linq for .net. Some even argue these languages are a purer representation of the relational model than SQL.

So in theory your friend is right - you don't need to learn SQL. But you still need to learn how relational databases work, and when you know that, SQL is pretty easy to learn, since it is just some syntax.

While not necessary, it is quite convenient to know SQL, since you can query any database directly in SQL without the need of a separate translation layer. And since all tutorials, books and examples use SQL, it will be difficult to avoid learning it.

-1

I ran into a problem wherein serial numbers were being stored as 10-digit decimal numbers in a database, and read into 32-bit integers in Java. This was fine until we hit our first serial number that was larger than 2G, so it could not be represented in Java's 32-bit signed integer. Understanding the DB data types might have prevented this issue.

protected by gnat Jul 11 '16 at 19:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.