none
Exception of type 'System.OutOfMemoryException' was thrown.

    Question

  • How can I work around this? 'System.OutOfMemoryException'

    Background:
    I am trying to calculate the daily moving average (50day, 200day, etc...) for the closing price of a large number of stocks (~15,000 stocks over last 10+ years).  Because Stocks sometimes "Split" (2 for 1 for example)  This necessiates recalculating the moving average on occassion.  T-SQL is not very efficent at these types of calculations with queries running multiple hours for the amount of data I am using.  I would ultimately like to have this data saved back into a flatten table in SQL Server.  I would estimate the size of such a table to be around 50 million rows of data (Fields- Stock, Date, Moving Average Value).  So I figured SSAS/MDX and cubes would be a quicker approach.

    So I have no problem generating the moving average, the flattened data, with Small Sets of Stocks say less than 100 Stocks at a time,  however SSAS doesn't seem to handle larger sets of the query.  Within a minute I receive the System.OutofMemory Exception when I run the following query in MS SQL Server Management Studio.

    SELECT

     

    [Measures].[MVA50] on columns,
    [Dates].[Date].[Date] *[Tickers].[Ticker].[Ticker]
    on rows from [SplitAdjCLOSE]


    1) How do I get around the Memory Exception Error Other than Running this Query on Smaller Sets of Data (less than 100 at a time) multiple hundreds of times

    2)  Is there a way to run the query (using Openquery from SQL SERVER) such that all the data gets dumped into a table such that the data does not need to reside in memory?

    OR

    3)  Is there a better way to tackle this problem that I'm just missing?

    Thanks for the help!
    Jason







    Thursday, March 18, 2010 4:50 PM

Answers

  • z1rcon is right to double check whether it's client or server. I'm 99% sure it's the client (SSMS) that's throwing the out of memory exception.

    So basically we're suggesting you ignore the memory error, since you didn't want to view all that data in SSMS anyway.

    The suggestions above about using SSIS to dump the data into a table are good. And Okeeone's suggestion that SSAS isn't good for huge datadumps is also good.

    Another suggestion would depend on whether you're pulling the lowest level of data from that fact table. If you are, then you'll get your results much, much faster with a DRILLTHROUGH query. See more info here: http://technet.microsoft.com/en-us/library/ms145964.aspx


    http://artisconsulting.com/Blog/GregGalloway
    • Proposed as answer by Okeeone Thursday, March 25, 2010 12:52 PM
    • Marked as answer by JasonAkus Thursday, March 25, 2010 7:18 PM
    Saturday, March 20, 2010 2:21 PM
    Moderator

All replies

  •      You can make an SSIS package that would query the SSAS database and write it to another table.  I don't think that would help though.

         Are you reunning out of memory on the server or on the client?

         What is the formula for [Measures].[MVA50]?

         It's been my experience that SSAS is not the tool for massive datadumps.  If you need to look at summary information and drill to only a portion of the underlying data (presumably the data that you are interested in), it's great.  Using it to extract large quantities of data for analysis in other tools - not so much. 

    Thursday, March 18, 2010 6:33 PM
  • Thanks for the reply- In this case the client/server are the same.  Have 6GB- but all ram is being sucked dry on the machine when the query is running. Maybe I just need more (Even though the Board is Maxed Out)? It is a MASSIVE amount data.  But I'm suprised SSAS handles the memory like this and doesn't use some type of paging or better memory management to handle large queries.

    The Calculated Member MVA50 is set up as follows-

    AVG

     

    ([DATES].[Date].CurrentMember.Lag(49):[DATES].[Date],
    [Measures].[Closing Price]

    Yes - I'd agree with your comment that SSAS may not be the best for massive data dumps.  But it seems like it's really good and fast at calculating things like moving averages, trends, growth rates, especially on smaller sets of aggregated data.

    But If Not SSAS or SQL Server then what is the best way to deal with this amount of data?
    (I was iteratively dumping the data to be calculated stock-by-stock-into excel worksheets and having excel do the calculations and then "saving" back to sql server- but it's a little slow as you can imagine once you read the data, paste to excel, calculate, copy from excel, and write to a table.)

    I've tried the SSIS approach and I've tried
    the approach outlined here
    http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx

    by creating a linked server connection to SSAS from SQL Server and Doing a Select into Table from OPENQUAERY( MDX Query) but no luck either......

    any more clues or hints ?


    Thursday, March 18, 2010 7:09 PM
  •  

         You could set up an SSIS job with a foreach loop that iterates through all of the dates.  Inside the loop would be a dynamic MDX statement that only pulls the the data for a single date, writes the results and moves to the next date.  The query for a single date would (hopefully) be small enough that you wouldn't have an issue.   It could be done by week, month, whatever is the largest group of dates that could be handled without the memory issue popping up.

    • Proposed as answer by Okeeone Thursday, March 25, 2010 12:52 PM
    Friday, March 19, 2010 6:35 PM
  • Okay, but you still don't say if it is the client or server throwing the exception. That message is a generic exception that can be raised by either a client application or a service. If it is the AS service, is this a 32 bit or 64 bit box? The SQL Server client tools are 32 bit and as such can only address 3Gb of memory per process. Likewise for the 32 bit version of Analysis Services.
    Saturday, March 20, 2010 9:38 AM
  • z1rcon is right to double check whether it's client or server. I'm 99% sure it's the client (SSMS) that's throwing the out of memory exception.

    So basically we're suggesting you ignore the memory error, since you didn't want to view all that data in SSMS anyway.

    The suggestions above about using SSIS to dump the data into a table are good. And Okeeone's suggestion that SSAS isn't good for huge datadumps is also good.

    Another suggestion would depend on whether you're pulling the lowest level of data from that fact table. If you are, then you'll get your results much, much faster with a DRILLTHROUGH query. See more info here: http://technet.microsoft.com/en-us/library/ms145964.aspx


    http://artisconsulting.com/Blog/GregGalloway
    • Proposed as answer by Okeeone Thursday, March 25, 2010 12:52 PM
    • Marked as answer by JasonAkus Thursday, March 25, 2010 7:18 PM
    Saturday, March 20, 2010 2:21 PM
    Moderator
  • I'm now 99% sure it's on the client.  Breaking the problem up into smaller pieces using SSIS works.  Not super fast but certainly works.

    I was under the impression that running a stored procedure within SSMS was actually handled on the server not the client.  But I guess I was mistaken.

    thanks for the help and direction....

    Best,

    j

     

    Thursday, March 25, 2010 7:29 PM
  • Jason-

    I want to comment on this statement: "I was under the impression that running a stored procedure within SSMS was actually handled on the server not the client.  But I guess I was mistaken."

    Just to be clear, I assume you meant "running an MDX query within SSMS was actually handled on the server not the client"? If so, you are correct that it is run on the server. Long gone are the days of AS2000 where lots of work was done in the client.

    So why are you getting out of memory errors? I'm not sure whether it's SSMS running out of memory while reading the response from the server, or SSMS running out of memory while rendering the results, but it is definitely NOT SSMS running out of memory because it is EXECUTING the MDX query. Hope that clears it up.


    http://artisconsulting.com/Blog/GregGalloway
    Friday, March 26, 2010 11:30 AM
    Moderator