patternMinor
Why do large tables slow down queries in databases?
Viewed 0 times
whytablesdatabasesslowlargedownqueries
Problem
Kind of a generic question; but I have noticed that large tables in certain CMS's can slow them down to a crawl. The solutions for this I've seen are to just truncate those tables.
Why do large tables slow down SQL queries so much?
How do Indexes help large tables?
How does disk speed factor into this?
Why do large tables slow down SQL queries so much?
How do Indexes help large tables?
How does disk speed factor into this?
Solution
Most of the time, sql performance issues comes from a poor miss job on the indexing. Selecting the wrong field to index or even the lack of clustered index can create huge bottle necks. I have seen many, many times that most dba forgets to create indexes for deletes. Yeah. I know, a delete... How come? Deletes, at least in sql server, performs several tasks that involve checking if the element can be deleted, check related tables, etc and those tasks can be slow as molasses without the proper indexing.
On the other hand, performance issues can very well come from lazy programmers who don't really care about performance or creating heaps on the server. These type of guys just creates select queries or select * without caring if these queries are performing full table scans, and believe me, in those situations, it doesn't matter if the db is 3Mb or 1Gb.
Finally, if you are really planning to have very large tables, you should consider a good partition policy and create your db with that in mind. This way, you can create the proper partitioned indexes.
As in most cases, in databases, the problem is between the keyboard and the chair ;)
On the other hand, performance issues can very well come from lazy programmers who don't really care about performance or creating heaps on the server. These type of guys just creates select queries or select * without caring if these queries are performing full table scans, and believe me, in those situations, it doesn't matter if the db is 3Mb or 1Gb.
Finally, if you are really planning to have very large tables, you should consider a good partition policy and create your db with that in mind. This way, you can create the proper partitioned indexes.
As in most cases, in databases, the problem is between the keyboard and the chair ;)
Context
StackExchange Database Administrators Q#48665, answer score: 4
Revisions (0)
No revisions yet.