Click here to Skip to main content
15,501,342 members
Please Sign up or sign in to vote.
3.50/5 (2 votes)
See more:

I have one table in sql server database with huge amount of data. I am fetching the record from this table and it will take too much time while we open the page. Can any one give me any idea how can i optimize that records because i am using select statement with joins.



We can't really answer that - we have no idea what your query looks like, how it works, or what it returns.
The first thing to look at is: What data am I fetching? Is all of it necessary at this point, or can I forget some of it completely? Or put it off till later and load it in the background?

How much data are you fetching? How complex is the join? Why? Do you need it to all that? What part of the query is taking time? How much time is it taking? How much does it take if you remove something?

Use the Stopwatch class, and get some numbers for how long things are taking. Then look at timing the various things that are happening. Then you can start to look at what needs to be speeded up, and can give us some factual information when you ask for help!

At the moment, generalizations are all we can give you...
Share this answer
create indexes on this table. so you can fetch records in less time
Share this answer
[no name] 29-Jan-13 5:33am    
If you implement indexes wrongly it may take more time. So be careful using indexes.
As OriginalGriff mentioned (general pointers for performance):

1) Server side paging.
2) Indexing on the tables on the columns that are used as filters.
3) Simplifying the queries and getting rid of complex and redundant joins.
4) Using AJAX on the client side to load data only when needed.
5) Caching frequently accessed but rarely changing data, (Be very careful with this).
6) Optimising your code to simplify and eliminate loops (redundant ones).

It all really depends what your slow queries are and where exactly in the code is a performance hotspot.

Share this answer
You need to figure out what exactly is taking so long, like OriginalGriff said. Basically there are a few things you can look at:
* Load the query in SQL Manager and check there. If that is taking long, you found the origin of the problem, if not the problem is in your code.

* Are there indexes and shouldn't they be rebuilt ?
* Do you have a lot of large objects (BLOB eg) ?
* How complex is the query, make sure that the query is built in such a way that the indexes are used! Basically, although it depends on database, it means that if you have NULL values in the column, if you use functions (eg toUpper, ...) the index is not used. Read up on the cases for your database.

A lot can go wrong here.
* make sure you aren't copying objects needlessly around.
* Make sure you don't have loops that contain a lot of string concatenations, use StringBuilder for that.
* debug, debug, debug ... I often use logs and log information messages with the timestamps... helps a lot...
* is the network slow?

hope this helps.
Share this answer
A most common situation,

In one word: Paging
Can be done as folks here suggested on the server side GUI handling.


It can also be done on the DB side, create a stored procedure that will deliver bulks of BULK_SIZE number of rows in return.

Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900