C #에서 대규모 데이터베이스로 콘솔 응용 프로그램을 실행하는 동안 높은 메모리 소비

SQL 서버에서만 데이터를 가져 오는 콘솔 응용 프로그램에서 작업 중입니다. 이를 위해 sqlreader를 사용하여 데이터를 매우 빠르게 검색했습니다.

내 우려는 콘솔 응용 프로그램을 실행할 때 해당 응용 프로그램이 너무 많은 메모리를 차지한다는 것입니다. 계속 증가하고 있습니다. 작업 관리자를 확인했지만 운이 없습니다. 너무 많은 메모리를 사용하는 프로세스를 찾지 못했습니다. 인터넷 검색 후 Rammap 도구를 찾았 습니다 . 이 도구에 따르면 AWE는 너무 많은 메모리를 사용하고 있습니다. 7GB의 8GB 메모리가 필요합니다. 그러나 메모리를 해제하지 않았고 해당 쿼리가 SQL Server에서 제공되지 않고 시간 초과 만료 오류가 발생합니다.

그러나 AWE를 검색했지만 내 문제와 관련된 유용한 정보를 찾지 못했습니다.

그렇다면 왜 너무 많은 메모리를 사용합니까?

다음은 데이터를 검색하는 샘플 코드입니다.

/// <summary>
    /// Get Products 
    /// </summary>
    /// <param name="productIds">Product Ids</param>
    /// <returns>Product List</returns>
    public IList<Product> GetProducts(IList<int> productIds)
    {
        try
        {
            //pass product identifiers as comma-delimited string
            string commaSeparatedProductIds = "";
            if (productIds != null)
            {
                commaSeparatedProductIds = String.Join(",", productIds);
            }

            string query = "GetProducts";

            List<Product> productList = new List<Product>();


            //Open connection
            connection = new SqlConnection(_connectionString);
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            //create a command object
            using (var cmd = connection.CreateCommand())
            {
                //command to execute
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 120;

                cmd.Parameters.Add("@ProductIds", commaSeparatedProductIds);


                //database call
                var reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        //TODO Uncomment se name in sp
                        Product product = new Product();

                        product.Id = reader.GetValue<int>("Id");
                        product.Name = reader.GetValue<string>("Name");
                        product.ShortDescription = reader.GetValue<string>("ShortDescription");
                        product.FullDescription = reader.GetValue<string>("FullDescription");
                        product.ProductTypeId = reader.GetValue<int>("ProductTypeId");
                        product.CreatedOnUtc = reader.GetValue<DateTime>("CreatedOnUtc");
                        product.Sku = reader.GetValue<string>("Sku");
                        product.AllowCustomerReviews = reader.GetValue<bool>("AllowCustomerReviews"); Convert.ToBoolean(reader["AllowCustomerReviews"].ToString());
                        product.ApprovedRatingSum = reader.GetValue<int>("ApprovedRatingSum");
                        product.ApprovedTotalReviews = reader.GetValue<int>("ApprovedTotalReviews");
                        product.VendorId = reader.GetValue<int>("VendorId");

                        product.IsTaxExempt = reader.GetValue<bool>("IsTaxExempt"); Convert.ToBoolean(reader["IsTaxExempt"].ToString());
                        product.TaxCategoryId = reader.GetValue<int>("TaxCategoryId");
                        product.OldPrice = reader.GetValue<decimal>("OldPrice");
                        product.Price = reader.GetValue<decimal>("Price");
                        product.DisableBuyButton = reader.GetValue<bool>("DisableBuyButton"); Convert.ToBoolean(reader["DisableBuyButton"].ToString());
                        product.AvailableForPreOrder = reader.GetValue<bool>("AvailableForPreOrder"); Convert.ToBoolean(reader["AvailableForPreOrder"].ToString());
                        product.SpecialPrice = reader.GetValue<decimal>("SpecialPrice");
                        product.SpecialPriceStartDateTimeUtc = reader.GetValue<DateTime?>("SpecialPriceStartDateTimeUtc");
                        product.SpecialPriceEndDateTimeUtc = reader.GetValue<DateTime?>("SpecialPriceEndDateTimeUtc");
                        product.AvailableStartDateTimeUtc = reader.GetValue<DateTime?>("AvailableStartDateTimeUtc");
                        product.AvailableEndDateTimeUtc = reader.GetValue<DateTime?>("AvailableEndDateTimeUtc");
                        product.CallForPrice = reader.GetValue<bool>("CallForPrice"); Convert.ToBoolean(reader["CallForPrice"].ToString());
                        product.CustomerEntersPrice = reader.GetValue<bool>("CustomerEntersPrice"); Convert.ToBoolean(reader["CustomerEntersPrice"].ToString());

                        product.VendorId = reader.GetValue<int>("VendorId");
                        product.VendorName = reader.GetValue<string>("VendorName");

                        product.SeName = reader.GetValue<string>("SeName");

                        product.Category = reader.GetValue<string>("Category");
                        product.Manufacturer = reader.GetValue<string>("Manufacturer");
                        product.Tag = reader.GetValue<string>("Tag");

                        product.Picture = reader.GetValue<string>("Picture");

                        productList.Add(product);
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }

                //close up the reader, we're done saving results
                reader.Close();

                //close connection
                connection.Close();

                return productList;
            }
        }
        catch (Exception ex)
        {
            HelperClass.CatchException(ex);
            return new List<Product>();
        }
        finally
        {
            connection.Close();
        }
    }

/// <summary>
/// Helper class for SqlDataReader, which allows for the calling code to retrieve a value in a generic fashion.
/// </summary>
public static class SqlReaderHelper
{
    private static bool IsNullableType(Type theValueType)
    {
        return (theValueType.IsGenericType && theValueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    /// <summary>
    /// Returns the value, of type T, from the SqlDataReader, accounting for both generic and non-generic types.
    /// </summary>
    /// <typeparam name="T">T, type applied</typeparam>
    /// <param name="theReader">The SqlDataReader object that queried the database</param>
    /// <param name="theColumnName">The column of data to retrieve a value from</param>
    /// <returns>T, type applied; default value of type if database value is null</returns>
    public static T GetValue<T>(this SqlDataReader theReader, string theColumnName)
    {
        // Read the value out of the reader by string (column name); returns object
        object theValue = theReader[theColumnName];

        // Cast to the generic type applied to this method (i.e. int?)
        Type theValueType = typeof(T);

        // Check for null value from the database
        if (DBNull.Value != theValue)
        {
            // We have a null, do we have a nullable type for T?
            if (!IsNullableType(theValueType))
            {
                // No, this is not a nullable type so just change the value's type from object to T
                return (T)Convert.ChangeType(theValue, theValueType);
            }
            else
            {
                // Yes, this is a nullable type so change the value's type from object to the underlying type of T
                NullableConverter theNullableConverter = new NullableConverter(theValueType);

                return (T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType);
            }
        }

        // The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
        return default(T);
    }
}

저장 절차는 다음과 같습니다.

-- =============================================
-- Author:      Dharmik
-- Create date: 29-01-2014
-- Description: Get products for indexing
-- =============================================
ALTER PROCEDURE [dbo].[GetProducts] 
    @ProductIds nvarchar(MAX) = NULL

AS
BEGIN


if(@ProductIds is not null)
BEGIN   


CREATE TABLE #Product(
    Id [int] ,
    Name [nvarchar](400) NOT NULL,
    ShortDescription [nvarchar](max) NULL,
    FullDescription [nvarchar](max) NULL,
    ProductTypeId [int] NOT NULL,
    CreatedOnUtc [datetime] NOT NULL,
    Sku [nvarchar](400) NULL,
    AllowCustomerReviews [bit] NOT NULL,
    ApprovedRatingSum [int] NOT NULL,
    ApprovedTotalReviews [int] NOT NULL,
    VendorId [int] NOT NULL,
    IsTaxExempt [bit] NOT NULL,
    TaxCategoryId [int] NOT NULL,
    Price [decimal](18, 4) NOT NULL,
    OldPrice [decimal](18, 4) NOT NULL,
    DisableBuyButton [bit] NOT NULL,
    AvailableForPreOrder [bit] NOT NULL,
    SpecialPrice [decimal](18, 4) NULL,
    SpecialPriceStartDateTimeUtc [datetime] NULL,
    SpecialPriceEndDateTimeUtc [datetime] NULL,
    AvailableStartDateTimeUtc [datetime] NULL,
    AvailableEndDateTimeUtc [datetime] NULL,
    CallForPrice [bit] NOT NULL,
    CustomerEntersPrice [bit]  NULL,
    VendorName [nvarchar](max) NULL,
    SeName [nvarchar](max) NULL,
    Category [nvarchar](max) NULL,
    Manufacturer [nvarchar](max) NULL,
    Tag [nvarchar](max) NULL,
    Picture [nvarchar](max) NULL)


    DECLARE @ProductId INT
DECLARE mapping_cursor CURSOR
FOR

SELECT * FROM [nop_splitstring_to_table](@ProductIds, ',')
--SELECT TOP 80000 ProductId  FROM Incremental_Solr_Product WHERE SolrStatus=1 AND IsDeleted=0 AND StoreId=1

OPEN mapping_cursor
FETCH NEXT
FROM mapping_cursor INTO @ProductId
WHILE @@FETCH_STATUS = 0

BEGIN


    INSERT INTO #Product 
        (Id,
        Name,
        ShortDescription,
        FullDescription,
        ProductTypeId,
        CreatedOnUtc,
        Sku,
        AllowCustomerReviews,
        ApprovedRatingSum,
        ApprovedTotalReviews,
        VendorId,
        IsTaxExempt,
        TaxCategoryId,
        Price,
        OldPrice,
        DisableBuyButton,
        AvailableForPreOrder,
        SpecialPrice,
        SpecialPriceStartDateTimeUtc,
        SpecialPriceEndDateTimeUtc,
        AvailableStartDateTimeUtc,
        AvailableEndDateTimeUtc,
        CallForPrice,
        CustomerEntersPrice,
        VendorName,
        SeName,
        Category,
        Manufacturer,
        Tag,
        Picture)

     SELECT 
        p.Id,
        p.Name,
        p.ShortDescription,
        p.FullDescription,
        p.ProductTypeId,
        p.CreatedOnUtc,
        p.Sku,
        p.AllowCustomerReviews,
        p.ApprovedRatingSum,
        p.ApprovedTotalReviews,
        p.VendorId,
        p.IsTaxExempt,
        p.TaxCategoryId,
        p.Price,
        p.OldPrice,
        p.DisableBuyButton,
        p.AvailableForPreOrder,
        p.SpecialPrice,
        p.SpecialPriceStartDateTimeUtc,
        p.SpecialPriceEndDateTimeUtc,
        p.AvailableStartDateTimeUtc,
        p.AvailableEndDateTimeUtc,
        p.CallForPrice,
        p.CustomerEntersPrice,
        v.Name AS 'VendorName',
        u.Slug AS 'SeName',
        (SELECT pcm.Id,pcm.CategoryId,c.Name AS 'CategoryName',pcm.DisplayOrder AS 'CategoryDisplayOrder' FROM Product_Category_Mapping AS pcm JOIN Category AS c ON pcm.CategoryId=c.Id WHERE pcm.ProductId=@ProductId FOR XML RAW ,ROOT('Category')) AS 'Category',
        (SELECT pmm.ManufacturerId ,m.Name,pmm.DisplayOrder  FROM Product_Manufacturer_Mapping AS pmm JOIN Manufacturer AS m ON pmm.ManufacturerId=m.Id WHERE pmm.ProductId=@ProductId FOR XML RAW ,ROOT('Manufacturer')) AS 'Manufacturer',
        (SELECT ptm.ProductTag_Id,t.Name FROM Product_ProductTag_Mapping AS ptm JOIN ProductTag AS t ON ptm.ProductTag_Id=t.Id WHERE ptm.Product_Id=@ProductId FOR XML RAW ,ROOT('Tag')) AS  'Tag',
        (SELECT TOP 1 ppm.PictureId,p.MimeType,p.SeoFilename FROM Product_Picture_Mapping AS ppm LEFT JOIN Picture AS p ON ppm.PictureId=p.Id WHERE ProductId=@ProductId ORDER BY DisplayOrder FOR XML RAW ,ROOT('Picture')) AS  'Picture'
        FROM Product as p LEFT OUTER JOIN Vendor AS v ON p.VendorId=v.Id 
        LEFT OUTER JOIN UrlRecord AS u ON p.Id=u.EntityId
        WHERE p.Id=@ProductId AND u.EntityName='Product' AND u.LanguageId=0




FETCH NEXT

FROM mapping_cursor INTO @ProductId

END

CLOSE mapping_cursor
DEALLOCATE mapping_cursor


    SELECT * FROM #Product
DROP TABLE #Product 


END     
ELSE
    PRINT 'Provide product ids...'

END