How Do I Speed Up My Select * Query Of "530,000" Rows

Question

1.40/5 (3 votes)

See more:

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.

Posted 26-Aug-14 5:11am

Hidhoud1991

Add a Solution

2 solutions

Solution 2

Hi,

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

At DB level...

1. Do not use

SQL

SELECT * FROM

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

SQL

WITH (NOLOCK)

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.

Posted 26-Aug-14 20:10pm

Magic Wonder

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)

OriginalGriff · Accepted Answer · 2014-08-26T05:20:00

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!