Taking long time when handling large amount of data in gridview

Gani_tpt 1,786

I have been developing ASP.NET application. i am storing the image to sql table as binary format.

i have more than 5000 customer records in the table.

every customer will have the medical certificate as image in will be stored in SQL table in binary format ==> Is that good idea for Storing image in SQL table ...?

when we are handling the paging, particularly when saving record of selected checked row in each page "it is taking long time to save" or while loading the date into grid it is taking long time to display the data to the user.

we are keep on getting complaint from the end user, loading of grid view data is taking long time to display and

also, whenever the changing the page, it is also taking long time.

I want to handle this performance issue in the grid view.

currently i am using default paging.

if i use to start custom paging ==> can it solve my issue...?

AgaveJoe 26,246 Reputation points

2024-05-14T20:14:00.6633333+00:00

You have tons of experience on these forums. Have you done any troubleshooting to figure out where the bottle neck is in your code?

I'm guessing the query that binds the GridView returns the image column which is the same as downloading the image. If your unknown query fetches all 5000 records then your design downloads 5000 images. Depending on the image sizes downloading 5000 images could take a while. If you are embedding the images (Base64) then the image size increases by about a 1/3.

if i use to start custom paging ==> can it solve my issue...?

If you are returning 5000 records now and switch to returning, let's say, 10 records at a time then fetching the records will be 5000/10 = 500 times faster if all the images are approximately the same size.

I recommend creating an image handlers for displaying the images. Then you can remove the image column from the result set. The handler will fetch the image as if the image is a file on disk and you can use a regular image server control to fetch the image from the image handler.

https://www.google.com/search?q=web+forms+image+handler
Lan Huang-MSFT 26,606 Reputation points Microsoft Vendor

2024-05-15T06:56:26.41+00:00

Hi @Gani_tpt,

every customer will have the medical certificate as image in will be stored in SQL table in binary format ==> Is that good idea for Storing image in SQL table ...?

I remember your previous post insisted on uploading the image to a file server and then saving the file name and file path to a database. This approach should be more efficient.You can also try using database pagination.

This will involve creating custom sorting, custom filtering, and custom pagination, but it will significantly improve the performance of your code since you will only be able to retrieve one page of data from the database at a time, rather than all of it.

https://stackoverflow.com/a/17979628
Gani_tpt 1,786 Reputation points

2024-05-16T18:05:12.0266667+00:00

Hi @lan huang-msft,

Thanks for your suggestions.

Accepted answer

Bruce (SqlWork.com) 58,121 Reputation points

2024-05-15T19:24:36.38+00:00

the query for the grid should not include the image column as this will increase the network payload an greatly slow down the query. if the grid needs. thumbnail (probably not likely with medical cert), then a pre calculated thumbnail column should be added.

5000 rows is not too bad for query access, but a 5000 row html table, can have performance issues. you should use a javascript grid that supports virtual scrolling. if this is not an option, then you should use a paging grid.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Taking long time when handling large amount of data in gridview

0 additional answers