如何加快我的选择*查询“530,000”行 [英] How Do I Speed Up My Select * Query Of "530,000" Rows

问题描述

我应该怎样做才能将530,000的select *查询加速到网格视图



数据库索引是否有助于这种情况如果是，我应该如何使用它在那张桌子上



谢谢。

what should i do to speed up the select * query of 530,000 to a grid view

Does database index helps in this situation if yes how should i use it on that table

Thanks.

推荐答案

两项改进：

1 ）不要SELECT *

而是只选择你需要的列。在传输您不会查看的数据方面毫无意义。

2）不要选择所有行。您认为您的用户将如何处理一个满五十万行的网格视图？您认为他需要多长时间才能找到他想要的信息？



相反，请将数据分页。提供过滤器和搜索。只是读取数据并倾倒在用户身上总是会很慢 - 尤其是在基于Web的应用程序中，您将受到最慢部分的影响：客户端宽带连接。这根本不会很快......



说真的，正确地完成工作：索引等等无济于事。减少行数，同时让用户的生活更轻松！

Two improvements:
1) Don't SELECT *
Instead select only the columns you need. There is no point at all in transferring data you aren't going to look at.
2) Don't select all the rows. What the heck do you think your user is going to do with a grid view full of half a million rows? How long do you think it is going to take him to find the information he wants?

Instead, page the data. Provide filters, and searches. Just reading the data and dumping on the user is always going to be slow - particularly with a web based application where you are at the mercy of the slowest part: the client broadband connection. Which is not going to be fast at all...

Seriously, do the job properly: indexing and so forth isn't going to help. Cut down on the number of rows and make life easier for your users at the same time!

检查以下评论可以帮助您加快提取过程。



在DB级...



1.不要使用

Hi,

check the below comments which may help you to speed up your fetching process.

At DB level...

1. Do not use

SELECT * FROM 

使用数据库中的有限列和必需列。



2.检查表中的索引，请注意有时更多索引/错误索引可能会减慢输出速度查询您的批量数据。



3.根据要求从主表中获取#temp表中的有限数据。在#temp表中也进行索引。在#temp表上写下查询。



4.使用

instead use limited and required columns from database.

2. Check indexing in your tables, kindly take a note that sometimes more indexing / wrong indexing may slow your output query when your data in bulk.

3. Fetch limited data in #temp tables from your main tables as per requirements. Do indexing in #temp tables also. Write query on your #temp tables.

4. Make use of

WITH (NOLOCK)

前端...



5.前端级别的问题似乎比数据库级别更高。因为，从数据库中获取530,000条记录不是大问题，在前端显示是一个大问题。



6.前面没有显示这么多记录的意义结束。如何搜索所需数据？



7.检查前端可以提供多少过滤器@前端只显示所需数据。



8.当您使用网格视图时，请使用分页属性。



9.如果可以的话，避免数据处理前端。



10.如果要求提供530,000条记录，则不是显示数据而是下载数据。





希望上面会帮助你。





干杯。

At front End...

5. It seems to be problem at front end level more than database level. Since, fetching 530,000 records from DB is not big issue, displaying at front end is a big issue.

6. There is no point of displaying so many records at front end. How one can search the required data?

7. Check how many filters you can provide @ Front end to display only required data.

8. As you are using Grid View, make use of pagination property.

9. Avoid, if you can, data manipulation at front end.

10. If providing 530,000 records is in requirement, then instead of displaying data give data download.


Hope above will help you.


Cheers.

这篇关于如何加快我的选择*查询“530,000”行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！