Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
Out of curiosity, why are you asking? – HLGEM Jun 13 '12 at 17:29
@HLGEM I happen to be doing some research on joins in outsourced databases and I thought I might make my contribution seem more important to the community if I could officially claim that joins are the top query of all time :) – ingenious Jun 13 '12 at 17:34
joins aren't a type of query at all – HLGEM Jun 13 '12 at 17:42
@HLGEM No they aren't, they are a type of clause. Same as 'having' or 'where'. But for lack of a better word I'll leave it at query for the moment. Any edits that will make my question more readable are more than welcome. – ingenious Jun 13 '12 at 17:47

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.

2 Answers

up vote 3 down vote accepted

It depends...

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 SELECTs dominate the majority of the queries.

OLTP (Online Transaction Processing) - this type of system is designed more for data manipulation, and typically INSERT`UPDATE\DELETEstatements are significant enough to optimize for this type of transaction.SELECT` is also a large part of this environment, and considerations need to be made for this as well.

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.

I just want to know if joins outweigh selects in the long run

Joins simply create a relationship between tables. They are used in conjuction with the respective DML statement (SELECT, INSERT, UPDATE, DELETE).

or inserts outweigh creates

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.

share|improve this answer
A lot of abbreviations I need to get familiar with :) And my question was rather something like: Are 'simple' selects more common then joins? If not - which kind of joins takes the first place? I (same as mostly everyone out there) also have some "gut feeling" that it depends (a lot) on the type of appliance there is at hand but I wondered if there is any scientific work done on the subject. Thanks for the answer though, it got me started on some interesting topics. – ingenious Jun 13 '12 at 17:30
up vote 2 down vote

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.

share|improve this answer

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