Data Fetch in Asp.net

Question

0.00/5 (No votes)

See more:

Hello All,

I am fetching data from database using asp.net , but problem is that DB table contains more then 100,000 rows of data. My UI runs slowly when it fetchs the data , and it takes a lot lot time .

Please suggest for faster fetching.

Posted 31-May-11 11:26am

Raj Bose

Updated 31-May-11 11:36am

Keith Barrow

v3

Add a Solution

Comments

Keith Barrow 31-May-11 17:36pm

Is your query returning 100,000 rows or is that just the table size?

2 solutions

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)

thatraja · Answer 1 · 2011-05-31T16:06:00

Solution 2

It's horrible man that loading 100,000 rows at a time. Just think about the end user, they never like that much delay or something.

Just use paging, filtering in data.

In database side
SQL Server 2005 Paging Results[^]

In your controls
GridView Multiple Filter AJAX Control[^]
Repeater with Paging and Sorting Features[^]

Posted 31-May-11 16:06pm

thatraja

Comments

Sergey Alexandrovich Kryukov 31-May-11 22:56pm

Absolutely, my 5.
--SA

Sergey Alexandrovich Kryukov · Answer 2 · 2011-05-31T12:15:00

Solution 1

The user never wants to see 100,000 rows of data at a time. The control you're using can be used in a virtual mode. In this way, you will fetch only the data which could be visible on one screen, ideally, 3 or so times more. If a user wants to look at previous/next page, it will be a situation of cache hit, so the result will be rendered almost immediately. In worst cases, the user wants to scroll far away; and it will be a total cache miss, but re-fetching of full data will not take too long time as this is just another 3+ screens of data. You can devise more sophisticated caching mechanism, but the idea will be pretty much the same, http://en.wikipedia.org/wiki/Cache[^].

—SA

Posted 31-May-11 12:15pm

Sergey Alexandrovich Kryukov

Comments

Manfred Rudolf Bihy 31-May-11 18:18pm

Good advice! 5+

Sergey Alexandrovich Kryukov 31-May-11 20:00pm

Thank you, Manfred.
--SA

Raj Bose 31-May-11 18:26pm

Thanks

Sergey Alexandrovich Kryukov 31-May-11 20:01pm

You're welcome.
Are you going to formally accept this solution (green button)?
Thank you.
--SA

Manfred Rudolf Bihy 31-May-11 20:29pm

Proposed as solution!

Sergey Alexandrovich Kryukov 31-May-11 21:43pm

Thank you very much, Manfred.
--SA

Ashishmau 1-Jun-11 0:29am

The control you're using can be used in a virtual mode....I didn't get this line..can you explain little bit..

Sergey Alexandrovich Kryukov 1-Jun-11 0:57am

It's a bit complicated, need some work on your side. DataGridView and ListView have virtual mode. In this mode, you don't populate it with data -- they present data supplied on request, depending on user's action. For example, is a user scrolls a virtual DataGridView, is request data for the rows which should scroll into view. You supply data in event handles. Data binding does that automatically.
--SA