the question sounds pretty simple but I couldn't find anything on topic except for this article here. So is there any (even remotely) scientific (or at least accredited) opinion on the matter? The actual DBMS doesn't matter at all - I just want to know if joins outweigh selects in the long run, or inserts outweigh creates and (if possible) by what margin. Something like that.
closed as not constructive by Aaron Bertrand, Leigh Riffel, Mark Storey-Smith, gbn, JNK♦ Jun 15 '12 at 20:26
As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or specific expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, see the FAQ for guidance.
This is extremely specific to the environment. There are two types:
OLAP (Online Analytical Processing) - this type of system is designed more for reporting and analysis, in which case often times
OLTP (Online Transaction Processing) - this type of system is designed more for data manipulation, and typically
Often times these roles are encompassed by a single instance/database. Because of this both must be considered. There are dominant systems (like Data Warehouses) that may be dedicated soley to OLAP.
Joins simply create a relationship between tables. They are used in conjuction with the respective DML statement (
It sounds like you're asking the ratio of DDL vs. DML. In a typical implementation DML will be the majority of a production environment's queries, though DDL is almost never going to be stale.
It also depends to a very large extent on the purpose of the database. A database that collects data from sensors for instance that loads thousands of records per second likely has more inserts than anything else. An online store database likely has more selects becasue people look at far more stuff than they buy. A mature Enterprise business system may have more updates than anything, particularly if most of the new records are inserted through some type of bulk insert process. It would be a rare database that would have more deletes than anything else. Generally that would happen only on rare occasions such as losing a major client and removing theire data. If you have more DDL than DML statments and the database isn't in the process of setting up, then you probably are doing something drastically wrong becasue structure should not change as frequently as data.