减少恢复20万十万条记录的时间 [英] Reducing time retriving 200000 lakhs of records

问题描述

嗨
我必须根据日期选择评论来查看内部联接表中的2,00,000条记录





i已经在日期字段的桌子上创建了索引

虽然它正在拍摄



30分钟来回溯记录



如何最小化它



请帮助我

Hi I have to retrive 2,00,000 records from view inner join table based on selecting critria betwwin Dates


i have created Index on table for Date Field
though it's taking

30 min to retrive records

How can i minimise it

Please help me

推荐答案

为什么你检索这么多记录？当然这需要时间。和记忆。和带宽。



以适当的小页面获取记录然后处理它们。



BTW：一个可怕但刚刚发生 - 你实际上并没有试图在GridView或类似的东西中显示它们，是吗？因为，如果你是，你可以翻页：在GridView中一次显示一页信息 [ ^ ]

Why are you retrieving so many records? Of course it is going to take time. And memory. And bandwidth.

Get the records in appropriate small pages and deal with them then.

BTW: A horrible though just occurred - you aren't actually trying to display them all in a GridView or similar, are you? Because if you are, you can page it: Showing a page of information at a time in a GridView[^]

很少有事情需要考虑：

- 你真的需要所有记录吗？如果您正在使用它们进行计算或行消除，请尽量在数据库服务器上执行此操作。

- 数据库是瓶颈还是例如网络或客户端应用程序？

- 如果确实需要记录，表中的总行数是多少。如果你从表中取出大部分（比如> 10％），你实际上不想使用索引而是要进行表扫描。

Few things to consider:
- Do yo really need all the records? If you're using them for example for a calculation or row elimination, try to do as much as possible on the database server.
- Is the database the bottleneck or for example the network or your client application?
- If you do need the records, what's the total amount of rows in your table. If you're fetching a large portion from the table (say >10%), you actually don't want to use index but to do a table scan instead.

Sir



我在日期之间显示水晶报告。和用户可以输入日期，如果用户输入有2年差距的日期，那么记录将超过2 00 000.





之后我优化查询删除不需要的字段，不需要的左外连接和查询

的格式如下：

Sir

I am showing the crystal Report between Date. and Date can be entered By User so If User enters Date having 2 years Gap then records will be more Than 2 00 000 .


After that i have optimise query removing unwanted fields,unwanted left outer joins and query
is in simple form as below

SELECT DISTINCT bt_memid,bt_level,bt_pvcode,bt_date ,BT_Type,ISNULL(SUM(BT_LeftCnt),0) AS BT_LeftCnt,ISNULL(SUM(BT_RightCnt),0) AS BT_RightCnt,ISNULL(SUM(BT_ConfLeftCnt),0) AS BT_ConfLeftCnt, ISNULL(SUM(BT_ConfRightCnt),0) AS BT_ConfRightCnt,ISNULL(SUM(BT_ownbv),0) AS BT_ownbv,ISNULL(SUM(BT_Confownbv),0) AS BT_Confownbv,ISNULL(SUM(BT_ConfLeftBV),0) AS BT_ConfLeftBV,ISNULL(SUM(BT_confrightbv),0) AS BT_confrightbv,tm_memno,tm_name FROM Binary_Dtls INNER JOIN TempMember_Mst ON TempMember_Mst.TM_MemId=Binary_Dtls.BT_Memid WHERE  BT_Date between CONVERT(DATETIME,'20/11/2010',103) AND  CONVERT(DATETIME,'24/11/2010',103) GROUP BY bt_memid,bt_level,bt_pvcode,bt_date ,BT_Type,tm_memno,tm_name ORDER BY BT_Memid ASC, BT_Date ASC

表格为

tables are as

这篇关于减少恢复20万十万条记录的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！