Hot answers tagged

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 ...


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 ...


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 ...


5

.net already uses connection pooling, so when you create/dispose a connection, you are not actually opening and closing a database connection, just fetching and returning a connection to the pool. So you don't really get any benefit by changing to Open/Close, and the using construct guarantees that the connection is returned to the pool correctly even in ...


5

It somewhat unlikely but possible that someone has built code that looks for this specific error and handles it in some special way that is different from what it would do if there were no results. Maybe they might return an error to the user. Such an approach is bad-practice IMO but I've seen (much) worse. I think that it's a mistake that this syntax ...


3

Such kind of questions cannot be answered in a sensible manner on an abstract level, without the surrounding context. One has to try this out using the real system: a real database, filled with real data, a real network, a real client machine running a real client application. Then it will be possible to profile both approaches and compare them. And keep in ...


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 ...


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.


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 ...


2

Foreign keys in your database enables data integrity, as you can't delete a parent row if there is a child row in another table. While you can rely on the framework to handle data for you, the framework will not enable data integrity and you will eventually end up with orphan rows in your database. So, my advice is: design the database properly in order ...


1

the database has to go through tens of thousands of comments to find the few ones that match your article No, it doesn't. This concept is called indexes. In basic terms, when you expect to do a search for something within a table, based on a specific column, you define this column as the index. Later, when you ask your database to return all items where ...


1

The standard is to use first_name, last_name, etc. I mean standard not as formally defined (say in ISO 9000) but informally, a usage I have observed over 30 years in dozens of companies and hundreds of systems, thus an informal standard. There's nothing to stop you doing things a different way if it makes sense for your situation, however: Developers ...


1

This is an optional 1 to 1 relationship or a 1 to (0,1) relationship. A true identity relationship would be 1 to (1,1). I find this notation useful in understanding the scale of the relationship. A one to many relationship could be 1 to (0,10), 1 to (1,5), 1 to (1, *). The fist digit is always 0 (optional) or 1 (mandatory) while the second specifies an ...


1

Triggers fire synchronously with the transaction which wrote the data initially. That Tx will incur further latency because of the trigger. This may be acceptable to you, or not. If the trigger code fails the original Tx will fail too. Change Data Capture may be a reasonable approach. It can be asynchronous at runtime. The application will be ignorant of ...


1

The cost of ETL should be cheaper because there's some level of batching involved, rather than doing work for every INSERT/UPDATE. But as the time requirement becomes smaller, trigger becomes more suitable answer. On the other hand, if it can be implemented using trigger (e.g. copy to another table), maybe you don't really need to use ETL. You need ETL to ...


1

I guess what your sources are refering to is ACID compliance. There are lots of SQL databases which provide ACID. Most NoSQL databases, however, do not provide this for the sake of better scalability. Here is an article on Quora which might give you an explanation. For both categories of databases, however, if one wants data not to get lost, regular backups ...


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 ...


1

The point of a database transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently. Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the ...


1

The data belongs to the organization, not the application. While right now you're using Laravel exclusively, in the future there might be several applications interacting with the database. I'm currently working on an app that started in FoxPro, migrated to ASP classic, and was partially upgraded to ASP.NET. There are at least three applications interacting ...



Only top voted, non community-wiki answers of a minimum length are eligible