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:

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:

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

Published 26 March 08 10:10 by Tom

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# DotNetKicks.com said on March 26, 2008 10:11 AM:

You've been kicked (a good thing) - Trackback from DotNetKicks.com

# Francois Ward said on March 26, 2008 3:34 PM:

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 :)

# Tom said on March 26, 2008 4:50 PM:

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

# Sajid Wahab said on March 27, 2008 3:42 AM:

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,,,.:(

# Josh Coswell said on March 27, 2008 5:25 AM:

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

# Suprotim said on March 27, 2008 6:26 AM:

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

# Tom said on March 27, 2008 11:58 AM:

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

# Tom said on March 27, 2008 11:59 AM:

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

# Tom said on March 27, 2008 12:06 PM:

Sajid, a quick search pointed me to:

http://msdn2.microsoft.com/en-us/library/bb326394.aspx

And some useful info on:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx

Also may want to follow-up on this forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96651

Name (required)
Your URL (optional)
Comments (required)
Remember Me?

Search

This Blog

Home
Email

All my posts are provided "AS IS" with no warranties, and confer no rights. All the expressions expressed herein are entirely my own and not necessarily those of Microsoft. In addition, thoughts and opinions often change. Because a weblog is intended to provide a semi-permanent point-in-time snapshot, you should not consider out of date posts to reflect current thoughts and opinions.

Your country's flag: