查询的速度是否取决于表中的行数?

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?

#0

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.

#1

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.

#2

Almost always yes. The real question is: what is the rate at which the query slows down as the table size increases? And the answer is: by not much if r.x is indexed, and by a large amount if not.

#3

Not the rows (to a certain degree of course) per se, but the amount of data (columns) is what can make a query slow. The data also needs to be transfered from the backend to the frontend.

#4

The Answer is Yes. But not the only factor. if you did appropriate optimizations and tuning the performance drop will be negligible Main Performance factors

  • Indexing Clustered or None clustered
  • Data Caching
  • Table Partitioning
  • Execution Plan caching
  • Data Distribution
  • Hardware specs

There are some other factors but these are mainly considered. Even how you designed your Schema makes effect on the performance.

#5

You should assume that your query always depends on the number of rows. In fact, you should assume the worst case (linear or O(N) for the example you provided) and exponential for more complex queries. There are database specific manuals filled with tricks to help you avoid the worst case but SQL itself is a language and doesn't specify how to execute your query. Instead, the database implementation decides how to execute any given query: if you have indexed a column or set of columns in your database then you will get O(log(N)) performance for a simple lookup; if the system has effective query caching you might get O(1) response. Here is a good introductory article: High scalability: SQL and computational complexity

推荐文章

显式字符编码和编码/解码应该作为Perl中的“最佳实践”加以推广吗?

显式字符编码和编码/解码应该作为Perl中的“最佳实践”加以推广吗?

推荐文章

.htaccess URL重写帮助!

.htaccess URL重写帮助!

推荐文章

用Boost和Visual C++ 2005解压缩ZIP文件?

用Boost和Visual C++ 2005解压缩ZIP文件?

推荐文章

如何实现和测试ASP.NET MVC FakeRepository

如何实现和测试ASP.NET MVC FakeRepository

推荐文章

带$variable的LIKE运算符

带$variable的LIKE运算符

推荐文章

在线程池中设置最小线程数

在线程池中设置最小线程数

推荐文章

在MySQL中按单行条件对行组排序?

在MySQL中按单行条件对行组排序?

推荐文章

如何在Java中实现以二进制数组为键、二进制数组为值的缓存

如何在Java中实现以二进制数组为键、二进制数组为值的缓存

推荐文章

nhibernate查询按用户的Guid返回用户

nhibernate查询按用户的Guid返回用户

推荐文章

异常处理后如何返回for循环

异常处理后如何返回for循环

推荐文章

在Silverlight中将FontWeight绑定到布尔值

在Silverlight中将FontWeight绑定到布尔值

推荐文章

如何使用jQuery将光标从指针切换到手指?

如何使用jQuery将光标从指针切换到手指?

推荐文章

如何切换jQuery函数调用(更改操作)

如何切换jQuery函数调用(更改操作)

推荐文章

为什么InvokeLater导致我的JFrame不能正确显示?

为什么InvokeLater导致我的JFrame不能正确显示?

推荐文章

如何将jqGrid数据与其他表单值一起发布

如何将jqGrid数据与其他表单值一起发布

推荐文章

如何配置JPA使用JNDI?

如何配置JPA使用JNDI?