Does more data mean slower queries?

Question

Let's say I have one table that has 1000 rows and the other table with the same structure/index but 10 million records. Will the performance of CRUD operations on the bigger table be slower than the smaller one? Thanks.

Answer 1

It depends. Creating, Deleting and Updating will be slightly slower in average since it is more likely that index structures will have to be reorganized. Also, if more data is frequently requested from the database system, then it is less likely that the data you are trying to access in cached in RAM and must be read from the hard disc. But these differences should not be very significant for modifying queries.

Fetching/Reading will certainly be slower though. It is just obvious that having to fetch, filter, sort and group large amounts of data is slower than doing the same with small amounts. This is especially true if the data you are working on is too large to fit into the machine's RAM.

Answer 2

Depends on the database engine, but generally yes, it will be slower, simply because you have to wade through index to find your data. If your making a global update, it will also be slower, simply because you're changing more data.

Databases are also often tuned towards either fast write or fast read - in the second case you have to update index, which takes more time, once it is big.

Answer 3

That depends on so many factors that it's almost impossible to say. Example: The DB engine stores data as rows which have pointers to the strings. For some reason, your 10M rows table only contains four different strings. So you have 10M pointers to four strings.

An update to replace one string with another would actually only replace one string with another; the pointers would be the same. No need to update the indexes, either. Same speed no matter how many rows you have.

Delete would be slower unless delete just flags the string as "deleted". A cleanup process running some time later would then actually clean the table. But you, as a user of the database, won't notice: The delete returns immediately.

Select would be slower since it has to return more data. The time until the first row can be returned will depend a lot on the engine design and your query. A well written query running against a 10M table with well chosen indexes can be faster than one against a 1K table with bad indexes. It depends on the amount of RAM on the server (maybe it can keep the whole database in RAM), disk speed (RAID array with lots of disks that can work in parallel versus a slow PC with little RAM and a single disk).

Insert is usually slower since you will have more (and bigger) indexes on the 10M table but if you have no indexes, adding a single row to a 10M table is usually as fast as adding to a small table.

Answer 4

Without indexes (or orders) there should be no significant difference for Insert, Update, and Delete in terms of page accesses (not including locating page time which is greatly effected by SQL type and memory cache size etc)

The type and number of indexes and the SQL product you use WILL have a noticeable effect

A 10M row table with just a single integer index on a sequential key will perform very similar for 1000 rows and 10M rows as each insert or delete will only change one index page (99.9% of the time with full page indexes), and updates will have no index changes. The index pages for 10M rows will fit into the cache of most servers

But an index on a varchar(50) attribute can be many times slower with 10M rows compared to 1000 rows, but that is the cost of large indexes

10 million rows isn't much to worry about though. If you row length is 100 bytes then the whole table will fit into <2GB of RAM

If you are worried about performance add more RAM, it is cheaper than trying to hand optimise a database

Answer 5

Of course, but I'm not sure that's very useful information without any context. For purposes of application design, it's usually one of the least of your concerns, because there are so many ways to deal with it, and there aren't usually many alternatives that are cost-effective.

Why do you ask?