ASP.NET Tips: Working with large SQL tables


There are a number of ways to query for data that is stored in a SQL Database from ASP.NET.  Generally the best way is to use stored procedures.  They can be compiled to be faster, and they are also much safer as they protect you from SQL injection problems, as discussed here.  For an example of using stored procedures, check out: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET.

This is all good information to use, but it doesn’t address the question of what to do about a large table.  For example, you can easily end up with a table in a database with over a million rows in it.  If you allow a query to run that basically does SELECT * FROM Table, you could end up running out of memory.  If you capture a dump and look at !dumheap -stat, it will show something like:

BigHeap

Granted this is a x64 dump, but you get the idea.  We have a bunch of System.Data.DataRow object (1,430,264) and if we look at the System.Data.Datatables (not shown) we see we only have two.  Looking at them, we see:

datatables

The solution here is to limit the amount of data that the stored procedure can return.  One way is to discussed here.  A good way to tell that this is the problem you are hitting is if your site works completely fine and then suddenly memory jumps by a large amount in a short period of time.  Getting a dump and looking at the output of these commands will tell you for sure.

More references

Book: “Real World ASP.NET Best Practices” by Matt Milner and Farhan Muhammad

kick it on DotNetKicks.com

Comments (9)

  1. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  2. Francois Ward says:

    I’m not into the whole SP vs dynamic SQL thing, so don’t take this as starting a debate 🙂

    That said… parameterized queries are also immune to SQL injection, and SQL Server also cache their query plans (which is the only "compilation" it does with stored procedures, too). There are finer details, but thats the general idea. Many of the other RDBMS work that way, too.

    So while there’s a million (good) reasons to use SPs, these really aren’t part of that equation 🙂

  3. Those are very good points.  I was trying to keep it simple but yes, those are very good things to point out.

  4. Sajid Wahab says:

    I have a problem with SQL Server,,,

    there are round about 7 hundred thousand records,, and I use multiple joins in select queries….

    after 3, 4 transactions it prompt me with,,

    Possible ErrorMsg 5242, Level 22, State 1, Line 1

    An inconsistency was detected during an internal operation in database ‘tempdb'(ID:2) on page (1:781). Please contact technical support. Reference number 4.

    can any body HELP me,,,.:(

  5. Josh Coswell says:

    Good insight.

    I will look for some more exmples of this kind in Large Data in SQL

    Is’nt large data from the database query independent of whether we use SP or NOT?

    Josh

    http://riverasp.net

  6. Suprotim says:

    The TOP clause in SQL Server 2005 has been enhanced. You can now specify an expression as the number definition in the TOP clause. This makes your TOP clause dynamic as you can pass the number value in a variable and use that variable in the TOP clause of your T-Sql query or your stored proc

    Sample Usage:

    DECLARE @TopVar AS int

    SET @TopVar = 20

    SELECT TOP(@TopVar)

    CustomerID,CompanyName, ContactName

    FROM Northwind.dbo.Customers

  7. Josh, you are right, and the link I give has T-SQL and Stored procedures limiting the amount of data returned.

  8. Great info Suprotim, this is exactly the kind of thing that will help in this situation