从sql中选择大量数据 [英] selecting huge data from sql

问题描述

嗨我有一个包含26列和大约100万条记录的表，当我在sql管理中从数据库中选择所有记录大约需要26秒时，它就是这么多，我在表中使用了聚簇索引，我的系统配置是：4 GB Ram，处理器：2.66 GH，和32位操作系统，所以问题是为什么加载100万这么慢，我不认为100万条记录对sql server 2005来说太多了，是不是相关的到我的硬件，如RAM或CPU？如果是的话那么需要多少RAM呢？谢谢。

Hi I have got a table with 26 columns and about 1 million records in it, when I select all records from database in sql management it takes about 26 sec and it is so much,I have used clustered index in table ,my system config is :4 GB Ram,and processor:2.66 GH,and 32 bit operating system, so question is why loading of 1 million is so slow , I do not think 1 million records are so much for sql server 2005, is it related to my hardware such as RAM or CPU ? if yes so how much RAM is necessary for it? thanks.

推荐答案

已经提到并覆盖了很多次，在数据库中拥有大量数据会花费大量时间来提取！ 100万条记录，26秒是公平的。有两种方法可以移动，一种是如果你有很多钱可以用来（而浪费......是的，浪费）。其他方法是使用你的机智并缩短此查询。



第一种方法 - 到最近（或最远）的商店购买硬件，购买大量内存，新CPU，或者我应该说一个大型机。然后使用它！



第二种方法 - 我会采用这种方法，在这种方法中，你要做的是你将在查询中添加一个新的约束为 TOP 100 所以查询将是这样的

It has been mentioned and covered a bunch of times that having a huge data in your database would take a lot of time to be extracted! 1 million records, 26 seconds are fair. There are two ways to move, one is if you've got a lot of money to spare (and waste... yes, waste). Other way is to use your wit and cut short this query.

First method - Go to nearest (or farthest) store for hardware stuff, buy a lot of RAM, a new CPU or should I say a mainframe at all. Then use it!

Second method - I would go with this approach, in this approach, what you will do is you will add a new constraint in the query as TOP 100 so the query would be like this

SELECT TOP 100 * from ...

这会做什么？这将选择表格中的前100条记录，使其更快地执行并返回到您的应用程序，以便作为表格和其他您想要对这些结果进行渲染的内容进行渲染，通常显示。



这对此有何帮助？你试过读表中的记录了吗？一次只显示几条记录，大多数情况下我一次只向用户显示20条记录，没有用户只在一个时间内查看 100万条记录。你会告诉他前100，然后如果他想要他可以请求接下来的100，依此类推。这会更好，假设一个用户拥有1GB内存的奔腾4计算机，如果你向他发送这么多数据进行处理和渲染，他就会离开你的网站。考虑发送较少的数据，然后如果他想阅读，则向他发送下一个数据，依此类推。没有用户有兴趣阅读你所拥有的所有记录，他想知道那些与他相关的记录 - 特别是他自己的记录。

What would this do? This would select the first 100 records in the table, making it faster to be executed and returned to your application for rendering as a table and other stuff that you want to do with those results, usually display.

How will this help? Have you tried reading the records in the table? There are only a few records shown at a time, mostly I've shown only 20 records to the users at a time, no user would have a look at 1 million records in just an instance of time. You would show him the first 100, then if he wants he can request for the next 100 and so on and so forth. This would be better, suppose a user has a pentium 4 computer with 1GB RAM he would leave your website if you send him this much data to process and render. Consider sending less data, then if he wants to read, send him the next data and so on. No user is interested in reading all of the records that you're having, he wants to know about those which are relative to him - his own record specially.

获取记录的其他方式更快的是将查询结果发送到文本文件而不是网格。哪个处理必须更快，然后你可以从该文件中读取。

Other way to get the records faster is to get the result of your query to a text file instead of grid. Which will process must faster and then you can read from that file.

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