ASP.NET: How to improve memory usage in these few lines of code?

Question

I have a database that has only one table of about 7,000 records. I have a GridView to display these records 10 rows at a time (I use the automatic paging feature provided with GridView).

Here's how I read in the table and show the records. Apparently, I read in the whole table but display only 10 rows which seems wasteful of memory. BUT I think I need to read in the whole shebang so that the paging can work. Am I wrong? If so how do I improve this code:

private void ShowGridViewData()
    {
        // I LEAVE ONLY RELEVANT CODE TO AVOID CLUTTER

        string queryString = (string)Session["queryString"];
        String connectionString = ConfigurationManager.ConnectionStrings["productsSQLConnectionString"].ConnectionString;
        DataSet ds = new DataSet();

        SqlConnection connection = new SqlConnection(connectionString);
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);



        // Fill the DataSet.
        adapter.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();


        adapter.Dispose();
        connection.Close(); 


    }

Additional info: I cannot change the code to use LINQ. And one more reason I had when I decided to read in the whole table is that paging would not require another database access. I might be wrong with this reasoning.

Additional info: I cannot change the code to use LINQ. And one more reason I had when I decided to read in the whole table is that paging would not require another database access. I might be wrong with this reasoning. — user776676, Nov 10 '11 at 02:38
You're much better off editing your original question rather than adding a comment — Conrad Frix, Nov 10 '11 at 19:38

score 3 · Answer 1 · answered Nov 10 '11 at 12:42

Since you've added a comment that completely changes the dynamic of the question, I'm posting a second answer.

In order to keep from going to the database each and every time, I would store your dataset into Session and access it from there each time. I have modified your code from above to include this, the only thing I don't see, is where you're implementing your paging to the GridView. I assume that's included in the code that was 'removed to avoid clutter'

private void ShowGridViewData()
{
    // I LEAVE ONLY RELEVANT CODE TO AVOID CLUTTER

    DataSet ds = new DataSet();
    if (Session["ProductsDataSet") == null) {

        string queryString = (string)Session["queryString"];
        String connectionString = ConfigurationManager.ConnectionStrings["productsSQLConnectionString"].ConnectionString;

        SqlConnection connection = new SqlConnection(connectionString);
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

        // Fill the DataSet.
        adapter.Fill(ds);

        Session["ProductsDataSet"] = ds;

    } else {
        ds = (DataSet)Session["ProductsDataSet"]
    }

    GridView1.DataSource = ds;
    GridView1.DataBind();


    adapter.Dispose();
    connection.Close(); 
}

score 2 · Accepted Answer · edited May 23 '17 at 11:47

BUT I think I need to read in the whole shebang so that the paging can work.

Yes but you can do use paging without loading the whole dataset by using an Object Data Source see Manually setting a GridView's PageCount when DataSource doesn't return full result set?

And one more reason I had when I decided to read in the whole table is that paging would not require another database access. I might be wrong with this reasoning.

Yes and No. It really depends since your use case. Typically you should worry more about resource utilization per request, not per session. This way you don't allocate resources that don't get used (pay-per-play)

So usually I follow the pay-per-play model and only get the data I'm going to show unless we're dealing with

Expensive SQL operations If you result set is small but takes a long time to create like a summary operation I might want to cache it like Anthony Shaw's answer.

Global state If its something that shared across users then only the first user pays and the play is free for everyone else. But you still need a sensible caching policy)

score 1 · Answer 3 · answered Nov 10 '11 at 02:09

Take a look at this blog entry. It creates an ad-hoc sql paging using ROW_NUMBER() function in SQL. You'll have to pass the correct page number.

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

BONUS, the example already uses 10 as the pagesize :)

score 1 · Answer 4 · edited May 23 '17 at 12:03

1

One option is to let SQL so the paging either in your stored procedure or by using LINQ. This post covers that: efficient way to implement paging

edited May 23 '17 at 12:03

Community

1
1

answered Nov 10 '11 at 02:12

Duke Hall

582
4
12

score 1 · Answer 5 · answered Nov 10 '11 at 02:23

1

It depends on what you are doing but if you have content that doesn't update much consider caching it. Then when updates are made to whatever you are displaying, delete/update the cache. This will help prevent a new connection to SQL if nothing changed!

answered Nov 10 '11 at 02:23

Gabriel Graves

1,751
1
22
40

ASP.NET: How to improve memory usage in these few lines of code?

5 Answers5