Solved

speeding up data binding

Posted on 2006-11-15
10
209 Views
Last Modified: 2012-05-05
HI,

I am populating a datagrid with 2500+ rows and 10 columns. This takes long time to load the data. is there any way to speed up the loading? I am using sql server 2000 database and dataset as datasource.

ayha
0
Comment
Question by:ayha1999
10 Comments
 
LVL 11

Expert Comment

by:ethoths
ID: 17947136
Why not use a paged data grid? Do you really need to see all 2500 rows at once. If so do you need to see all 10 columns as well as all 2500 rows at the smae tiome. If not try a summary/details strategy.

Otherwise..
Use a stored procedure to get your data.
Only pull what you need (columns and rows)
Use indexes on your tables.
Use mimimal html to format your output (css classes for styles and div for layout)
Cache the data as much as possible.




0
 
LVL 7

Author Comment

by:ayha1999
ID: 17947263
Hi,

dispalying paged datagrid will solve the probem in datagrid. but when I display same rows of data in a dropdownlist, this also takes long time, is there anyway to speed up the binding?

what is indexes and can u give a an example to use caching with datagrid?

Iwill increment ponits for u answer.

thanks in advance.

ayha
0
 
LVL 11

Accepted Solution

by:
ethoths earned 150 total points
ID: 17947676
Ok, In the drop down list you don't need 10 columns so that'll help.

Indexes are to do with the database tables. Basically they are like the index of a book and help you to find data very very very much more quickly that doing a table scan. As a basic rule you should consider all your where clauses as contenders for an index. If you have where city='London' in your SQL then indexing City will result in dramatically faster results. Of course this all depends on what data you have in the database and how much of it you want to retreive. Indexes won't help if you want all the data. Indexing a database is a topic in it's own right but for more infor check out...

http://www.w3schools.com/sql/sql_create.asp
http://www.sql.org/sql-database/postgresql/manual/indexes.html

Caching.
If your data does not change with every refresh then afetr you've fetched it from the data base put it in the cache...

Cache.Add("Key", dataset)

Then on subsequent reads you can just get it from here rather than hitting the database. Again, there are several options for setting this up (including dependancies) so it's best to do some research first...

http://samples.gotdotnet.com/quickstart/aspplus/doc/datacaching.aspx
http://aspnet.4guysfromrolla.com/articles/100902-1.aspx
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 17947825
Hi ayha1999,

what version of .net do you use? if you use .net 1.1 the dataset and datatable are the ways of binding so much records
the best practice way is to use objects that are bind to the datagrid. Also if you read the data from sql and use dataset i think you are using dataadapter - if this is true than you need to change the code to use datareader - it is much more fater

the second problem with the loading (no matter what is the scenario of loading data 2500+ are too many) you can create your own pager - and the sql statement/stored procedure to be modfied to get only the records that are needed to see on the page

Cheers!
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17955036
Where is the speed problem?

1) Getting data from the database?

2) Generating the web page?

3) What type of objects are you using to get the data?

4) Are you using complex SQL?

5) Have you optimized the database performance?

Bob
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Author Comment

by:ayha1999
ID: 17974534
Hi,

Instead of displaying all items in datagrid at a time, I implemented paging and it seems to be faster now.  my real problem is when loading dropdownlist and its selectindexchanged even fires.  I want to load 2500 + records in dropdownlist and when its selectindex changes I want to dispaly the selected values in textboxes, this takes very long time.

my sql query looks like;

select partno, name +':' + category as partinfo.... from my table

ddl1.selectitem.text="partno"
ddl1.selectedvalue="partinfo"

when select index changes

I split selectedvalue and distribute its values to textboxes.

this process is very time consuming.

how can speed up this process? is caching will increase speed then how can I do it? I am populating dropdown from dataset. howcan I optimize database performance?

thanks in advance.

ayha
0
 
LVL 11

Expert Comment

by:ethoths
ID: 17977939
Try using a hierarchical structure to your list of items. Can you group them together in any way so that you have fewer items in your list boxes but more listboxes. If you can do this then you can use AJAX cascading dropdowns…

http://ajax.asp.net/ajaxtoolkit/CascadingDropDown/CascadingDropDown.aspx
0
 
LVL 7

Author Comment

by:ayha1999
ID: 17988322
HI,

I am using .net ver 1.1. Is it possible to ajax dropdown with 1.1? Is it possible to use client-side caching to store my query result to avoid round-trip to the server during select indexchange?

ayha
0
 
LVL 11

Expert Comment

by:ethoths
ID: 17988385
I've not used ajax with 1.1 but I would think there is not problem. Basically its the browser (independent of the .net version) calling a webservice on the server. Since web services are available in 1.1 this shoudl still work.

The only way to client side cach the contents of the drop down would be to use javascript to poplulate the drop down in the first place. This code could then be put in a seperate .js file and included in th epage in the usual js way. It willbe a little slwoer initially but subsequent hits will be much quicker.

However, you have to ask is it sensible to have 2500 item in a list box. Using the cascading dropdowns will allow you to have fewer items in each box. Ultimately this is the best solution both for usability and speed.
0
 
LVL 7

Author Comment

by:ayha1999
ID: 17988414
thanks for the help/

ayha
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now