Does the speed of the query depend on the number of rows in the table?

Question

Let's say I have this query:

select * from table1 r where r.x = 5

Does the speed of this query depend on the number of rows that are present in table1?

Answer 1

The are many factors on the speed of a query, one of which can be the number of rows.

Others include:

• index strategy (if you index column "x", you will see better performance than if it's not indexed)
• server load
• data caching - once you've executed a query, the data will be added to the data cache. So subsequent reruns will be much quicker as the data is coming from memory, not disk. Until such point where the data is removed from the cache
• execution plan caching - to a lesser extent. Once a query is executed for the first time, the execution plan SQL Server comes up with will be cached for a period of time, for future executions to reuse.
• server hardware
• the way you've written the query (often one of the biggest contibutors to poor performance!). e.g. writing something using a cursor instead of a set-based operation

For databases with a large number of rows in tables, partitioning is usually something to consider (with SQL Server 2005 onwards, Enterprise Edition there is built-in support). This is to split the data down into smaller units. Generally, smaller units = smaller tables = smaller indexes = better performance.

Answer 2

Yes, and it can be very significant.

If there's 100 million rows, SQL server has to go through each of them and see if it matches. That takes a lot more time compared to there being 10 rows.

You probably want an index on the 'x' column, in which case the sql server might check the index rather than going through all the rows - which can be significantly faster as the sql server might not even need to check all the values in the index.

On the other hand, if there's 100 million rows matching x = 5, it's slower than 10 rows.