如何提高表的性能 [英] How to increase the performance of table

问题描述

In my project i have 20000 records in one table.
if i run the table i get more time to retrieving the date

任何可以从数据库中快速检索日期



我试过的：



select * from table_name



检索日期需要20多秒从数据库

Any Possible to retrieve the date quickly from the database

What I have tried:

select * from table_name

it takes more than 20 seconds to retrieving date from database

推荐答案

jgakenhe提出了非常好的观点 - 但首先要查看您要检索的数据。我可以在一秒钟内从我的SQL Server表中获取21024行，这纯粹是因为我检索的数据量很小：1 int ID，1 NVARCHAR（260）就是全部。那么看看你拿到了什么，以及SQL服务器PC和客户端之间链接的速度



那么看看你正在做什么的数据：如果你试图将它加载到任何形式的显示控件中，那么这将是一个缓慢的过程。



对不起，但我们不能挥挥魔杖说这就是这条线：把它变成这样的，因为你可以做很多不同的事情来减缓数据的速度。

但是我呢？我现在不再获取比我需要的更多的行或列 - 它总是会成为数据流量密集型。

jgakenhe makes very good points - but start by looking at the data you are retrieving. I can fetch 21024 rows from my SQL Server table in under a second, purely because the volume of data I am retrieving is trivial: 1 int ID, and 1 NVARCHAR(260) is all. So look at what you are fetching, and at the speed of the link between the SQL server PC and the client

Then look at exactly what you are doing with the data: if you are trying to load that into a display control of any form, then that is always going to be a slow process.

Sorry, but we can't wave a wand and say "It's this line: change it to such-and-such" because there are just so many different things you could be doing to slow down the data.
But me? I don't fetch any more rows or columns than I need at the moment - it's always going to be data traffic intensive.

它会很慢，这是很多记录到返回。



1）确保你有一把主钥匙。

2）只返回你需要的行;意思是按主键或其他列过滤。

3）仅选择所需的列。如果您有不需要的大列，则不要包含在查询中;例如：image，varchar（max）等...



此外，您通常不需要返回20000条记录。 20000条记录将关闭一个网页浏览器，并将打印到100页的PDF页面。

It's going to be slow, that is a lot of records to return.

1) Make sure you have a primary key.
2) Return only the rows you need; meaning filter by the primary key or another column.
3) Select only columns you need. If you have big columns you don't need, then don't include in your query; such as: image, varchar(max), ect...

Also, you should not typically need to return 20000 records. 20000 records will bring down a web browser and will print to 100's of PDF pages.

这篇关于如何提高表的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！