November 18, 2008 at 9:34 am
I have a user ( programmer ) that recently has a need to query a table that contains 33 + million rows. Each row is about 300 bytes and he's selecting ALL rows. He's using SQL 2005 and keeps getting the error : "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."
I can't find much about this problem anywhere except for several articles that describe a "Known Microsoft problem". Any insight from you folks would be appreciated. As always, thanks.
November 18, 2008 at 9:40 am
Do they need to select ALL rows?
Are they also selecting all columns? (select *)
November 18, 2008 at 9:43 am
Yes ... and Yes
November 18, 2008 at 9:46 am
why do they need all columns of all rows?
I can't think of an app that would display that much data on one page!?! or maybe I'm just a small fish a in big bowl
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 9:49 am
This isn't really a 'bug' with SQL.
The problem is SQL server getting the blame for bad application design and coding.
What situation do they need 33millions rows, must be a massive report..
November 18, 2008 at 9:53 am
Have him look into SQL Server side paging for his data the users cannot possibly look or edit at all of that data at once. Surely 25- 50 rows at a time would suffice. If he is making an extract than SSIS is more the tool to use.
April 6, 2009 at 8:45 am
I have a table with 82 million records.
I want to test if there are any duplicates and I ran a query
SELECT DISTINCT
[DemoProfileId]
,DEMOKEY
FROM [NationalIRD].[dbo].[DemoProfile]
expecting the same number of rows that are in the table.
After running for 15 minutes, it give me an error message:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
Machine Environment: Windows 2003 , SQL server 2005 std, SP3.
4gb Memory , 350 gb Hardidsk.
The same query ran successfully on a old server with the same configuration.
Any Ideas?
Sree
April 7, 2009 at 12:55 am
Its management studio that ran out of memory, not SQL server. After all, you ARE asking it to display 82 _million_ rows. Thats a lot of rows.
Why not get sql server to tell you the number of rows, rather than making the client application (management studio) count them.
try this:
select count(*) from
(
SELECT DISTINCT
[DemoProfileId]
,DEMOKEY
FROM [NationalIRD].[dbo].[DemoProfile]
) x
April 7, 2009 at 1:25 am
If you need only elements duplicated, and not all the diferent ellements, you can try something like this:
SELECT [DemoProfileId], DEMOKEY
FROM [NationalIRD].[dbo].[DemoProfile]
GROUP BY [DemoProfileId], ,DEMOKEY
HAVING COUNT(*) > 1
And, off course, if you have an index for [DemoProfileId], DEMOKEY
the performance will be better.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply