Post reply

How to select query of 13000001 no. of rows

  • May 31, 2011 at 4:57 am

    #389623

    On running select query of 13000001 no. of rows the error is:

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    Thanks

  • May 31, 2011 at 5:01 am

    #1331879

    Your client application doesn't have enough memory to hold all of those rows. This is not a database engine error

    What are you trying to do that requires 13 million rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 31, 2011 at 5:14 am

    #1331885

    This is my test DB.To do anything.

    Thanks

  • May 31, 2011 at 5:15 am

    #1331887

    Then get more memory on your client machine.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 31, 2011 at 5:50 am

    #1331902

    You're prolly trying to execute the query in SSMS. Set your output to a file, then execute.

  • May 31, 2011 at 6:16 am

    #1331919

    Thanks Jason I have alraedy applied it.

    Thanks

  • May 31, 2011 at 6:19 am

    #1331923

    Bear in mind that if you run query output to file you'll have to go and open the file to see the results, and the app you use to read the file may well run out of memory itself.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 31, 2011 at 6:22 am

    #1331925

    The file size is around 1.12 Gb and it is notepad file.

    When it opens my system is becoming slow.

    Thanks

  • May 31, 2011 at 6:24 am

    #1331926

    Of course it will be slow. That is a lot of data!

    As Gail stated, your client app will also most likely puke. Why do you need to return all rows?

    -

    Jason

  • May 31, 2011 at 6:27 am

    #1331928

    forsqlserver (5/31/2011)

    The file size is around 1.12 Gb and it is notepad file.

    When it opens my system is becoming slow.

    try using textmate to open it.

    but as other have said, do you really need that many rows?

  • May 31, 2011 at 6:27 am

    #1331930

    forsqlserver (5/31/2011)

    The file size is around 1.12 Gb and it is notepad file.

    When it opens my system is becoming slow.

    Well..... yes.

    How much memory does your client machine have? I struggle with > 1GB of data on a laptop with 3GB of memory.

    This is purely a client machine hardware issue. If you want, for whatever reason, to load millions of rows of data into memory, you need the hardware to handle it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 31, 2011 at 6:31 am

    #1331932

    I wonder if you ever taught any of my developers? < grin >

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • May 31, 2011 at 6:37 am

    #1331935

    Thanks Gail, I have already said this is my test DB,I have 2 GB RAM ...

    Thanks

  • May 31, 2011 at 6:41 am

    #1331939

    forsqlserver (5/31/2011)

    Thanks Gail, I have already said this is my test DB,I have 2 GB RAM ...

    Dude simple math. You have 2 GB or ram. Most likely all of it already used by the OS ans sql server.

    You want to put another 1.2 GB in ram.

    2-2-1.2 = FAILURE.

    The end, goodbye. Buy more ram or find another way to see this data where you don't open 1.9 gazillion rows.

  • May 31, 2011 at 6:47 am

    #1331944

    forsqlserver (5/31/2011)

    Thanks Gail, I have already said this is my test DB,I have 2 GB RAM ...

    That it's your test DB is irrelevant. You do not have enough memory on your machine to do what you're trying. Buy more memory or select less data. Seriously, why do you need 13 million rows for anything/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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