SQL 2005 Query Error

  • 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.

  • Do they need to select ALL rows?

    Are they also selecting all columns? (select *)

  • Yes ... and Yes

  • 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]

    SQL-4-Life
  • 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..

  • 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.

  • 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

  • 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

  • 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