CODEWITHSUNDEEP
database-design
adn295
adn295

Reputation: 21

Is there any case that if the number of tables involved in the operation(query) increased then is it affect to the performance?

The number of operations performed on database degrade the performance.But the number of tables involved in any query (for example joining of tables) reduce the performance ??

Upvotes: 0

Views: 30

Answers (2)

Ronnis
Ronnis

Reputation: 12833

As a rule of thumb, the nr of tables involved in a database operation says nothing about the performance of that operation. Think primary key lookups on several tables vs full scanning one big table.

As a rule of thumb, adding an additional table to a specific database operation will in general result in a measurable performance degradation. By measurable, I mean that the database will provide you with a number to describe the performance of both queries and the number for the second query will be worse. If the difference is noticable at all, is another question. Again, think adding 10 more primary key lookups to a join, versus adding one more 9 billion row table to fully scan.

As a rule of thumb, don't trust rules of thumbs. For example, the above rules are wrong if the additional table is inner joined and empty, or contains a small nr of rows, or enables a more efficient access path (cartesian joins of small tables to be able to use index on big table), or ... or .... or..

Upvotes: 0

nvogel
nvogel

Reputation: 25526

Not necessarily. Performance is determined by indexes, internal storage structures and query plans among other things. There is no direct correlation between performance and the number of tables involved in a query.

Upvotes: 1

Related Questions