It then proceeds to delete all the rows in a randomized order using either the three explicit DELETE statements or a single statement that relies on the ON DELETE CASCADE mechanism. The benchmark first populates the database with 20,000 rows in the first table (100,000 rows in each of the second and third tables). For each row in the first table, both the second and the third tables each contain five rows. The resulting database schema consists of three tables, with the second and third referencing the first. I used ODB to quickly create a benchmark that I could run against various databases. For our case, both speed and CPU resources used seemed like the most relevant metrics, so that’s what I measured. Server load itself can be defined in many ways, for example, as CPU, memory, or disk resources needed to complete the task. That is, whichever approach results in less sever load is better. However, after some consideration, we may realize that other criteria, such as server load, can be equally or even more important.
That is, whichever approach deletes objects faster is better. One fairly natural definition would be faster is better. And we are back to the original question: which one is better?įirst, let’s define better. For the erase() case, however, either approach can be used.
As you may have guessed, erase_query() is exactly the case where relying on the ON DELETE CASCADE clause is the only practical option. Alternatively, to erase an object or multiple objects that match a certain criteria, we can call erase_query(). If we know the object id (primary key), then we can use the erase() function. If you request ODB to generate the database schema for you, then ODB will add the ON DELETE CASCADE clause to such foreign keys.Īt the same time, ODB provides two ways to erase an object state from the database. These additional tables are “linked” to the primary table (i.e., the object table in case of a container and the root object table in case of an inheritance hierarchy) with foreign key constraints. The two most notable ones are containers inside objects as well as object inheritance hierarchies. In ODB, as in other ORMs, certain OOP constructs are mapped to additional tables. ODB allows you to persist C++ objects to a number of relational databases without having to deal with tables, columns, or SQL, and manually writing any of the mapping code. This came up during my work on ODB, an object-relational mapping (ORM) system for C++. The question that I got the other day was this: if both approaches can be used, which one is better? But before trying to define what is better and answering this question, let me first give you some background on what triggered this question. The same can be achieved with ON DELETE CASCADE by executing just one DELETE statement. For example, if we want to delete several rows matching a certain, non-primary key-based criteria, then to use the first approach we would first have to execute a SELECT statement that returns all the primary keys matching this criteria and then iterate over these keys and execute a set of DELETE statements for each of them. Oftentimes this limitation makes the second approach the only practical option. One important limitation of the first approach is that you need to know the primary key (here I assume that foreign keys reference primary keys in the target tables) of the referenced row in order to be able to delete all the referencing rows. This clause tells the database to automatically delete the referencing row if the row it references is deleted. The other option is to declare our foreign keys as ON DELETE CASCADE. The first option is to execute an explicit DELETE statement for each table that contains referencing rows and then finish by deleting the referenced row (this order is important if we don’t want to violate any foreign key constraints). If we need to delete a row in one of these tables as well as all other rows that reference it, then we have two options. Let’s say we have several tables in an SQL database referencing each other with foreign key constraints.
0 kommentar(er)
