13 Replies Latest reply: Jul 23, 2012 8:26 AM by mrpjspencer RSS

    Set analysis causing out of object memory

      Good evening, im using a bit of set analysis to identify duplicate records. Prior to the set analysis being used the script runs fine up to this point.

       

      I then use this :-

      =If(Count(TOTAL <Payment_Reference_In_CA>CA) > 1,'X')

       

      As soon as I do (looking at task manager) the memory usage goes through the roof until I get out of object memory.

       

      I have 4.7 million lines of data loaded which I dont really need anyway.

       

      Can anyone help me by limiting the load by restricting the data to only load data relating to Payment_Reference_In_CA if it has more than 1 record under field CA.

       

      Thanks.

       

      Paul.

       

       

        • Re: Set analysis causing out of object memory
          Stefan Wühl

          Paul,

           

          could you please clarify if your issue arise while executing your load script or when you add an expression to a chart object?

           

          You are saying, '..the script runs fine to this point' so I assume you are executing a load script.

          But your posted expression looks like a chart object expression.

           

          Also, the expression you posted

          =If(Count(TOTAL <Payment_Reference_In_CA>CA) > 1,'X')

          does not contain a set analysis construct, I only can see a total qualifier with field list as part of your aggregation function.

           

          The expression itselft doesn't look problematic to me with regard to extreme memory consumption, but I also know next to nothing about your setting, so it may be helpful if you could add some information about your data model, chart dimensions and expressions (if you are using that expression in chart object), or the relevant part of your load script.

           

          If possible, attaching a small sample application that demonstrate your issue might speed things up.

           

          Regards,

          Stefan

            • Re: Set analysis causing out of object memory

              Hi Stefan, thanks for your reply, I am running a load script where I only want it to provide non-distinct records. Here is the script I am using:-

               

              Main Table:

              LOAD BP,

                   CA,

                   Incoming_Payment_Method,

                   Annual_DD_Payer,

                   Standing_Order,

                   Payment_Reference_In_CA

              FROM*

               

              I am wanting to make the script load where field 'Payment_Reference_In_CA' has more than 1 relating entry for Field 'CA'.

               

               

              The Expression =If(Count(TOTAL <Payment_Reference_In_CA>CA) > 1,'X' works well if there isnt a large amount of data to pull. I am wanting to achieve a restricted load in order to limit the load to what I only need.

                • Re: Set analysis causing out of object memory
                  Nilesh Gangurde

                  Dear mrpjspencer,

                   

                  You can follow the below mentioned things:

                  1) Use of if statement in the expression is not the good practice,because for the large data it wont work.

                  2) You can load the limited no. of records then make selections and then load the whole data. To avoid selection erasing use lock.

                   

                  Hope this helps..

                   

                  Regards,

                  Nilesh Gangurde

              • Re: Set analysis causing out of object memory

                I am trying to construct an IF statement to limit the load to what I need. This is where I am wanting to go, am drawing blanks at the moment.

                  • Re: Set analysis causing out of object memory
                    Stefan Wühl

                    I don't think a single if() statement would be sufficient, since you can't decide to load / not load the record only looking at the one single record, right?

                     

                    I believe you probably need to do a group by load first, something like

                     

                    FILTER:

                    LOAD Payment_Reference_In_CA where Count >1;

                    LOAD

                    Payment_Reference_In_CA,

                    count(CA) as Count

                    FROM ... group by Payment_Reference_In_CA;

                     

                    LOAD

                    Main Table:

                    LOAD BP,

                         CA,

                         Incoming_Payment_Method,

                         Annual_DD_Payer,

                         Standing_Order,

                         Payment_Reference_In_CA

                    FROM ... where exists(Payment_Reference_In_CA);

                     

                    drop table FILTER;

                     

                    edit: Forgot the group by clause ...

                     

                     

                    • Re: Set analysis causing out of object memory

                      Hi,

                       

                      I think you need a preceeding LOAD with a group by clause to fix the records you need during the following (main-)load.

                      LOAD

                          CA,

                      Where CountDistinct > 1

                      ;

                      LOAD

                          Count(DISTINCT Payment_Reference_In_CA)    AS CountDistinct,

                          CA,

                      Resident RawUser

                      Group By CA,

                      ;

                       

                      Second:

                      Do the Load with en exists() like

                       

                      LOAD

                           *

                      where exists(CA)

                       

                      This should be faster.

                       

                      HtH

                      Roland

                    • Re: Set analysis causing out of object memory

                      This seems a bit messy where I am only after achieving something quite simple. (to anglicise what I want:-)

                       

                      (this goes for all entries within my database, there are millions)

                       

                      Only load property if it has more than 1 telephone number,

                       

                      or

                       

                      Only load record A if record B has more than 1 entry thats linked to record A...

                      • Re: Set analysis causing out of object memory

                        This seems a bit messy where I am only after achieving something quite simple. (to anglicise what I want:-)

                         

                        (this goes for all entries within my database, there are millions)

                         

                        Only load property if it has more than 1 telephone number,

                         

                        or

                         

                        Only load record A if record B has more than 1 entry thats linked to record A...

                        • Re: Set analysis causing out of object memory

                          Sorted it, I just told it to load entries where len>0 there were quite a few entries that were blank therefore useless to me, this then made my script only load what I needed