Reducing time retriving 200000 lakhs of records

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

Posted 11-Feb-11 1:15am

dubalumesh

Updated 15-May-16 22:26pm

Manas Bhardwaj

v2

Add a Solution

3 solutions

Solution 1

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[^]

Posted 11-Feb-11 1:22am

OriginalGriff

Comments

Sergey Alexandrovich Kryukov 11-Feb-11 16:59pm

My 5. Probably OP has little understanding on query?
--SA

Solution 2

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.

Posted 11-Feb-11 1:54am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dubalumesh · Accepted Answer · 2011-02-13T20:21:00

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

SQL

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

sql<br