Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL
Hi,
 
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.
 
Thanks
 
Naresh
Posted 28-Jan-13 22:28pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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...
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

create indexes on this table. so you can fetch records in less time
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
 
Cheers.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
 
Database
* 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.
 
Code
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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

A most common situation,
 
In one word: Paging
Can be done as folks here suggested on the server side GUI handling.
 
But
 
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.
 
Cheers,
Edo
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 260
1 Sergey Alexandrovich Kryukov 150
2 ChauhanAjay 120
3 BotCar 105
4 Richard MacCutchan 100
0 Sergey Alexandrovich Kryukov 9,056
1 OriginalGriff 7,981
2 CPallini 2,613
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100