locked
SELECT Query on LARGE DATASET - System.OutOfMemoryException Exception RRS feed

  • Question

  • User-1967952962 posted

    Hi,

          I have a table with 25+Million Records. The table structure as follows

    ID NAME PARENTID DATE
    1 test 9/10/2018
    2 test2 9/10/2018
    3 Test3 1 9/10/2018
    4 test4 1 9/10/2018
    5 test5 9/10/2018
    6 test6 9/10/2018
    7 test7 5 9/10/2018
    8 test8 9/10/2018
    9 test9 5 9/10/2018

     I have to Select only Orphan Records That is not a parent or child.  

    With above example, I need the result as follows

    ID NAME PARENTID DATE
    2 test2 9/10/2018
    6 test6 9/10/2018
    8 test8 9/10/2018

    current query 

    WITH T1 as(
    Select ID
    FROM TestTable as tab1
    Where NOT EXISTS
    (
    Select distinct ParentID FROM TestTable
    )
    )
    Select *
    FROM TestTable
    INNER JOIN T1 ON T1.ID = v.ID
    AND
    ParentID = ''


    giving System.OutOfMemoryException Exception  when running this against 25 million records

    Any Help Appreciated.

    Monday, September 10, 2018 6:14 PM

All replies

  • User77042963 posted
    CREATE TABLE TestTable(
       ID       INTEGER  NOT NULL PRIMARY KEY 
      ,NAME     VARCHAR(5) NOT NULL
      ,PARENTID INTEGER 
      ,DATE     DATE  NOT NULL
    );
    INSERT INTO TestTable(ID,NAME,PARENTID,DATE) VALUES
     (1,'test',NULL,'9/10/2018')
    ,(2,'test2',NULL,'9/10/2018')
    ,(3,'Test3',1,'9/10/2018')
    ,(4,'test4',1,'9/10/2018')
    ,(5,'test5',NULL,'9/10/2018')
    ,(6,'test6',NULL,'9/10/2018')
    ,(7,'test7',5,'9/10/2018')
    ,(8,'test8',NULL,'9/10/2018')
    ,(9,'test9',5,'9/10/2018');
    
    
    
    Select * from TestTable
    WHERE ID IN ( 
     Select ID from TestTable 
     WHERE ParentID  is null
     Except 
    Select  ParentID FROM TestTable
    )
     
     
    
    drop table TestTable

    Monday, September 10, 2018 6:46 PM
  • User364663285 posted

    Hi,

    Cause

    This issue occurs because the computer does not have sufficient memory to complete the requested operation. A limitation in SQL Server 2000 Reporting Services causes certain parts of report processing to be memory bound. For example, query result processing and object model rendering are memory bound. The computer does not have sufficient memory to complete the requested operation when one or more of the following conditions are true: 

    • A report is too large or too complex.
    • The overhead of the other running processes is very high.
    • The physical memory of the computer is too small.

    A report is processed in two stages. The two stages are execution and rendering. This issue can occur during the execution stage or during the rendering stage. If this issue occurs during the execution stage, this issue most likely occurs because too much memory is consumed by the data that is returned in the query result. Additionally, the following factors affect memory consumption during the execution stage:

    • Grouping
    • Filtering
    • Aggregation
    • Sorting
    • Custom code

    If this issue occurs during the rendering stage, the cause is related to what information the report displays and how the report displays the information. For example, the following factors affect memory consumption during the rendering stage:

    • The number and types of controls
    • The relationship between the controls
    • The formatting
    • The amount of data that is displayed

     

    Tuesday, September 11, 2018 10:00 AM
  • User753101303 posted

    Hi,

    You mean you are loading 25+ million rows in a System.Data.Dataset object ? Usually you try to load just the data needed at a particular point. What would you do with those rows  then ? Or this is really a db side error ?

    IMO it's best to quote the exact error message (and at least some of the call stack) to make sure about what happens. For now it seems a client side error rather than really a db side error.

    Edit: this is an SSMS error maybe ? See https://support.microsoft.com/en-us/help/2874903

    Tuesday, September 11, 2018 10:40 AM