none
System resource exceeded

    Question

  • I received this message while running a series of queries that are matching names and addresses. It has run successfully a hundred times but now suddenly is hanging up. The queries are on the front-end joining backend tables. I'm running Access 2010 in Office 2010 32-bit on a Win7 64-bit machine with 8GB RAM. I shut down every other program. My total physical memory usage was at 37%.

    How can I manage my memory consumption better?


    Darrell H Burns

    Thursday, February 16, 2012 4:31 AM

Answers

  • You're probably suffering from all of the Replace functions you are using.  If you have to use it so much I would just create your own function to replace the values so only one function call is necessary per record.

    Bob Larson, Access MVP 2008-2010, 2011

    • Marked as answer by Bruce Song Monday, March 05, 2012 4:16 AM
    Friday, February 17, 2012 12:51 AM

All replies

  •  Do you have any custom procedures being used in the queries? 

    Bob Larson, Access MVP 2008-2010, 2011

    Thursday, February 16, 2012 6:05 PM
  •  Do you have any custom procedures being used in the queries? 

    Bob Larson, Access MVP 2008-2010, 2011

    No, but I do nest the queries. For example, I execute about 30 query combinations like this:

    SELECT Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#","") AS AddressCompressed, Count(Location.Loc_ID) AS NumRecs, Min(lk_Data_Source.Merge_Priority) AS MinDSMergePriority
    FROM lk_Data_Source INNER JOIN Location ON lk_Data_Source.Code = Location.PrimaryDataSource
    WHERE (((Location.Loc_Street_Num)>"") AND ((Location.Loc_City)>"") AND ((Location.Loc_State)>"")) OR (((Location.Loc_City)>"") AND ((Location.Loc_State)>"") AND ((Location.Loc_Street_Name)>"")) OR (((Location.Loc_Street_Num)>"") AND ((Location.Loc_Zip_Primary)>"")) OR (((Location.Loc_Street_Name)>"") AND ((Location.Loc_Zip_Primary)>""))
    GROUP BY Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#","")
    HAVING (((Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1));
    

    is nested inside...

    SELECT vw_Location_Dupes_MinDS_6.AddressCompressed, Min(Location.Loc_ID) AS FirstID
    FROM (lk_Data_Source INNER JOIN vw_Location_Dupes_MinDS_6 ON lk_Data_Source.Merge_Priority = vw_Location_Dupes_MinDS_6.MinDSMergePriority) INNER JOIN Location ON lk_Data_Source.Code = Location.PrimaryDataSource
    WHERE (((Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#",""))=[AddressCompressed]))
    GROUP BY vw_Location_Dupes_MinDS_6.AddressCompressed;
    

    is nested inside...

    INSERT INTO Temp_Loc_Matches ( FromLocID, ToLocID, MatchLevel )
    SELECT Location.Loc_ID AS FromLocID, vw_Location_Dupes_6.FirstID AS ToLocID, 6 AS MatchLevel
    FROM Location, vw_Location_Dupes_6
    WHERE (((Location.Loc_ID)<>[FirstID]) AND ((Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#",""))=[AddressCompressed]))
    ORDER BY Location.Loc_ID;
    


    Darrell H Burns

    Thursday, February 16, 2012 7:06 PM
  • You're probably suffering from all of the Replace functions you are using.  If you have to use it so much I would just create your own function to replace the values so only one function call is necessary per record.

    Bob Larson, Access MVP 2008-2010, 2011

    • Marked as answer by Bruce Song Monday, March 05, 2012 4:16 AM
    Friday, February 17, 2012 12:51 AM