Does more data increase slower queries?

Question

Let's say I have one table with 1000 rows and another table with the same structure / index, but 10 million records. Will the performance of CRUD operations on a larger table be slower than less? Thanks.

Answer 1

It depends on the database engine, but as a rule, yes, it will be slower, simply because you need to wade through the index to find your data. If you do a global update, it will also be slower, simply because you are changing more data.

Databases are also often configured to read quickly or quickly - in the second case, you need to update the index, which takes longer when it is large.

Answer 2

It depends. Creating, deleting, and updating will be slightly slower on average, as it is more likely that index structures will have to be reorganized. In addition, if more data is often requested in the database system, it is less likely that the data you are trying to cache in RAM must be read from the hard drive. But these differences should not be very important for changing queries.

Checkout / Reading will of course be slower. It’s just obvious that having to select, 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 RAM.

Answer 3

It depends on many factors, which are almost impossible to say. Example. The database engine stores data as strings that have pointers to strings. For some reason, your 10M row table contains only four rows. So you have 10M pointers for four lines.

An update to replace one line with another will actually replace one line with another; pointers will be the same. No need to update indexes either. Same speed no matter how many lines you have.

Delete will be slower if you only delete flags as "deleted" only. After some time, the cleanup process will actually clear the table. But you, as a database user, will not notice: the deletion is immediately returned.

The selection will be slower as it should return more data. The time until the first row can be returned will depend on the design of the engine and your request. A well-written query running against a 10M table with well-matched indexes can be faster than one against a 1K table with bad indexes. It depends on the amount of RAM on the server (perhaps it can store the entire database in RAM), disk speed (RAID array with a large number of disks that can work in parallel compared to a slow PC with small RAM and one disk).

Inserting is usually slower since you will have more (and more) indexes in table 10M, but if you do not have indexes, adding one row to table 10M is usually as fast as adding to a small table.

Answer 4

Without indexes (or orders), there should be no significant differences for insertion, updating, and deletion in terms of page access (not counting the location of the page time, which is highly dependent on the type of SQL and cache 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 a single integer index on a sequential key will be very similar to 1000 rows and 10M rows, since each insertion or deletion will only change one index page (99.9% of the time with full page indexes) and the updates will not have any changes index. Index pages for 10M rows will fit into the cache of most servers

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

10 million lines do not worry. If the row length is 100 bytes, then the whole table will be placed in <2 GB of RAM

If you're worried about performance, add more RAM, it's cheaper than trying to optimize the database

Answer 5

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

Why are you asking?