Select * from a table with one billion rows failing

Question

Select * from a table with one billion rows failing

RPSQL

Hall of Fame

Points: 3229
More actions
November 3, 2010 at 1:01 pm

#146009

Select * from a table with one billion rows failing with following error (SQL 2005 SP3 HF11)
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

RPSql (11/3/2010)
Select * from a table with one billion rows failing with following error (SQL 2005 SP3 HF11)
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

i'm not surprised; selecting that much data and trying to render it into a display (gridmode/textmode/etc) would make any GUI run out of memory.

what are you trying to actually do? why would you try to get all billion rows? what was your process going to do with them?

If you can explain that, we can probably suggest a better alternative.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Not to mention if some of the columns are blobs (image, text, etc). Why select *? You should only select the columns you actually need.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 3

RPSql (11/3/2010)
Select * from a table with one billion rows failing with following error (SQL 2005 SP3 HF11)
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Makes sense. What's your question?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 4

note that even if the table with a billion rows had two columns, an ID and a varchar(30) for a name or something(38 bytes of data per row), you'd need 35+ gig of RAM / local harddrive space to download and then render the data.

/*--Results

Bytes KByes Megs Gigs

-------------- ---------------- -------------------- ----------------------

38000000000.0 37109375.000000 36239.6240234375000 35.3902578353881835937

*/

SELECT

--assuming just 38 bytes of data per row

(38.0 * 1000000000) AS Bytes,

(38.0 * 1000000000) / 1024 AS KByes,

(38.0 * 1000000000) / (1024.0 * 1024.0) AS Megs,

(38.0 * 1000000000) / (1024.0 * 1024.0 * 1024.0) AS Gigs

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

This is data releated and down to the size of the returned data grid. Is there a reason for want ALL the data from this table?

I've seen this a number of times when running sprocs in a loop which return a result or return value. I all cases to date, switching to text view in management studio has worked.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Was the error thrown on the database server or on the client receiving the data? Was there an 'order by' on the select?

And then again, I might be wrong ...
David Webb

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

RPSql (11/3/2010)
Select * from a table with one billion rows failing with following error (SQL 2005 SP3 HF11)
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

I don't think this is application/business specific query.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

GSquared SSC Guru Points: 260824 More actions · Answer 8

David Webb-200187 (11/3/2010)
Was the error thrown on the database server or on the client receiving the data? Was there an 'order by' on the select?

Honestly, does it matter whether there's an Order By or not?

There is not now, nor will there ever be, any reason for a human eye to look at 1-billion records at one time, and that query has no other possible use as written.

Even if the server were able to process the query, and the network were able to feed it to your workstation in finite time, and your workstation were able to render it, it would still be useless because it would take multiple lifetimes for a human eye and brain to do anything with it. If you spend an average of 0.1 second looking at each row, you'll be staring at it for over 3 years, without taking a break for sleep, food, or anything else. And 0.1 seconds isn't long enough to accomplish anything useful that a computer can't do better.

Which is why I started out by asking what the question was. Yes, there's an error, and the reason for the error is obvious, but there's been no statement yet of what the person was trying to accomplish. I'm hoping there's some result the original poster was trying to get besides 1-billion rows of data on their computer screen, and that we can help with solving that actual, underlying problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 9

Carlton Leach

SSCarpal Tunnel

Points: 4202

November 4, 2010 at 6:42 am

#1246491

This must be a pi$$ take

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

Carlton Leach (11/4/2010)
This must be a pi$$ take

Or a troll

This should fix the error:

Select top 100 * from table_with_one_billion_rows

Or get a bigger monitor 🙂

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

I was going to suggest this solution:

Select

*

from

table_with_one_billion_rows

cross join

table_with_one_billion_rows

steveb. SSC-Forever Points: 46733 More actions · Answer 12

or ask these guys to run the query for you

http://www.bbc.co.uk/news/technology-11644252%5B/url%5D

Leo.Miller SSChampion Points: 13051 More actions · Answer 13

homebrew01 (11/4/2010)
Carlton Leach (11/4/2010)
This must be a pi$$ take
Or a troll

I don't think so. I looked at the users previous posts (click on the user, select "Find all member's posts") and they look legitamate. Although they do appear to be a realy newbe.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Lowell SSC Guru Points: 323518 More actions · Answer 14

maybe he was doing a select * from BillionRowTable so he could see the column names, and the error threw him off.

He may not be familiar with sp_help [TableName] yet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!