表有大量数据的问题 [英] Problem with Table having huge amount of data

问题描述

我在sql server数据库中有一个包含大量数据的表。我从这张表中取出记录，打开页面时需要花费太多时间。任何人都可以告诉我如何优化记录，因为我使用带连接的select语句。



谢谢



Naresh

Hi,

I have one table in sql server database with huge amount of data. I am fetching the record from this table and it will take too much time while we open the page. Can any one give me any idea how can i optimize that records because i am using select statement with joins.

Thanks

Naresh

推荐答案

我们无法回答这个问题 - 我们不知道您的查询是什么样的，它是如何工作的，或者它返回的是什么。

首先要看的是：我拿到了什么数据？这一切都是必要的，还是我可以完全忘记它的一部分？或者把它关掉，然后把它装到后台？



你拿到了多少数据？加入有多复杂？为什么？你还需要它吗？查询的哪个部分需要时间？花了多少时间？如果你删除了什么需要多少钱？



使用秒表课程，并获取一些数字表示事情需要多长时间。然后看看正在发生的各种事情的时间安排。然后你可以开始查看需要加速的内容，并在你寻求帮助时给我们一些事实信息！



目前，概括都是我们可以给你......

We can''t really answer that - we have no idea what your query looks like, how it works, or what it returns.
The first thing to look at is: What data am I fetching? Is all of it necessary at this point, or can I forget some of it completely? Or put it off till later and load it in the background?

How much data are you fetching? How complex is the join? Why? Do you need it to all that? What part of the query is taking time? How much time is it taking? How much does it take if you remove something?

Use the Stopwatch class, and get some numbers for how long things are taking. Then look at timing the various things that are happening. Then you can start to look at what needs to be speeded up, and can give us some factual information when you ask for help!

At the moment, generalizations are all we can give you...

在这个表上创建索引。所以你可以在更短的时间内获取记录

create indexes on this table. so you can fetch records in less time

正如OriginalGriff所提到的（性能的一般指针）：



1）服务器端分页。

2）对用作过滤器的列上的表进行索引。

3）简化查询并消除复杂和冗余的连接。

4）在客户端使用AJAX仅在需要时加载数据。

5）缓存频繁访问但很少更改数据，（请小心）。

6）优化你的代码以简化和消除循环（冗余的）。



这完全取决于你的慢查询是什么以及代码中的确切位置一个表演热点。



干杯。

As OriginalGriff mentioned (general pointers for performance):

1) Server side paging.
2) Indexing on the tables on the columns that are used as filters.
3) Simplifying the queries and getting rid of complex and redundant joins.
4) Using AJAX on the client side to load data only when needed.
5) Caching frequently accessed but rarely changing data, (Be very careful with this).
6) Optimising your code to simplify and eliminate loops (redundant ones).

It all really depends what your slow queries are and where exactly in the code is a performance hotspot.

Cheers.

这篇关于表有大量数据的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！