如何利用数据库中的大量数据提高查询性能。 [英] How to improve queries performance with large amount of data in database.

问题描述

亲爱的，





我在一个在线应用程序中工作，其中一年有100万个数据的表，我正面临着各种查询中的性能问题。未来几年，表数据也会增加，性能问题也会增加。那么我该怎么办才能解决这些问题呢？

我应该按年分开数据库，还是每年对表进行索引，还是有更多的选项可以提高性能？





任何回复都非常可观。





提前致谢!!!

Dear all,


I am working in a online application where tables having 1 million data for one year and I am facing performance problem in various queries. For upcoming years also table data will increase as well as performance issue. Then what should I do to solve these problems?
Should I separate database year wise or do indexing year wise on tables or there are some more options available to improve performance?


Any response is highly appreciable.


Thanks in advance!!!

推荐答案

有许多不同的选项可以改善数据库的性能，但首先，有必要分析你的问题和了解数据库为何缓慢。



因此，作为第一步，你可以尝试这个：



a）采取一些查询非常慢



b）在SQL Management Studio中运行查询，并使用以下选项：

查询 - >包括实际执行计划（Ctrl + M）

查询 - >包括客户统计（Shift + Alt + S）





务必使用实际执行计划，而不是预计的执行计划！



实际执行计划为您提供了良好的绩效瓶颈核心指南。



你可以然后采取进一步措施，例如要么改进数据结构（索引，数据类型，表的一般设置），要么可以提高SQL语句的质量。



也许你可以在这里发布执行计划。

There are many different options to improve the performance of a database, but first, it necessary to analyze your problem and to understand why your database is slow.

Therefore, as a first step, you can try this:

a) Take some query which is very slow

b) Run the query in SQL Management Studio, and use these options:
Query -> Include Actual Execution Plan ( Ctrl+M)
Query -> Include Client Statistics (Shift+Alt+S)


Be sure to use the ACTUAL Execution Plan, NOT the ESTIMATED Execution Plan!

The Actual Execution Plan gives you a good guide to the core of your performance bottlenecks.

You can then take further steps, e.g. either improve your data structures (indexes, data types, general setup of tables), or you can improve the quality of your SQL statements.

Perhaps you can post a Execution Plan here.

这篇关于如何利用数据库中的大量数据提高查询性能。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！