web aplication fast when 1000000 records

Question

0.00/5 (No votes)

See more:

hello

my Microsoft .net visual studio framework is 2010. and
and sql 2008 .

I am fetching data from sql server my rows 10,000

but when I am fetching data it will take so much time

I defined commandtimeout = 0 but it is taking so much time

what should I do without fetching data from sql.?

how can fast fetching data from sql server?

bcoz my data and rows will increment by 1,00,000 to 2,00,000

which process I do so I can fetch 2,00,000 records fast

plz help

Posted 11-Oct-14 6:34am

Member 11145969

Add a Solution

Comments

[no name] 11-Oct-14 12:51pm

Most likely there is zero reason for you to be querying so much data. The obvious solution is to only query the data that you need at the time you need it.

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)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2014-10-11T08:48:00

Solution 1

No user would look at these much of records on a page.
So, what you can do is, just fetch the number of records you want to show on a page.
Also implement Custom Paging, that means you will design one SQL Procedure which will take input as Page Number and the Number of Records you need and return you accordingly.

You can see one Example - Custom Paging in ASP.Net GridView using SQL Server Stored Procedure[^].

And yes as you said, you can implement jQuery Ajax to get the Data and bind to GridView.
You can also use jQGrids.

Posted 11-Oct-14 8:48am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Afzaal Ahmad Zeeshan 11-Oct-14 14:56pm

Exactly, as far as this problem is concerned, no matter which way you go. You'll come to this answer, "Add paging feature to your website". I am going to +5 this answer, since my answer doesn't have the Stored Procedure thing, otherwise its same ;-)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 11-Oct-14 15:00pm

Thanks Afzaal. :)

Afzaal Ahmad Zeeshan 11-Oct-14 15:01pm

You're most welcome brother. :)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 11-Oct-14 15:03pm

:)

Afzaal Ahmad Zeeshan · Answer 2 · 2014-10-11T08:54:00

Advice

As always it is said, there is no need for you to fetch this much data, where are you going to present that much data? Suppose, for instance, you got all that data. How are you going to present it to the user? That would take a lot of time again, most of the users don't have fast computer systems like the development teams have. So there is a chance that the user has to experience a bad UX (User Experience).

It will be better for you to load thousand-th part of this data, ~200 records at a time, and then display them, if user wants more records, fetch next ones.

This way, your problem will be minimized, and your user won't have to face time dilation and other problems too.

Solution

Since you're here for a solution, so here is one. Increase the amount of RAM and CPU on your system, and add the feature of caching. Facebook and other giants use this feature to make sure that their service is fast and the users don't have to wait for their response (HTTP response; not user oriented responses).

https://en.wikipedia.org/wiki/Cache_(computing)[^]

Read above Wikipedia article to learn more about Caching.

So, the only way of letting this thing happen, is to increase the RAM, CPU and making sure all of the processes are done and caches, so that as soon as the user requests for data. Response from the cache is picked up and transferred to the stream, so that he can easily read the data without having to wait.

Note: As already mentioned, rendering of the output also takes time. So this also doesn't fit as the best possible way. There isn't any best solution.