• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

speeding up data binding

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
ayha1999
Asked:
ayha1999
1 Solution
 
ethothsCommented:
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
 
ayha1999Author Commented:
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
 
ethothsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mmarinovCommented:
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
 
Bob LearnedCommented:
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
 
ayha1999Author Commented:
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
 
ethothsCommented:
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
 
ayha1999Author Commented:
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
 
ethothsCommented:
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
 
ayha1999Author Commented:
thanks for the help/

ayha
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now