none
Memory keeps increasing when showing realtime data RRS feed

  • Question

  • I'm working on a mvc application to display realtime data (using IIS server), I have to work with EF and signalR (which I'm pretty new to this two), the software works perfectly except for one part where I have to reload the data from SQL Server every seconds (whenever the DB is modified) up to 70 rows can be modified (but usually about 40). 
    It works but when i check on the IIS worker process, the memory usage keeps growing whithout releasing it no matter what I tried... It use JS to reload this part and it seems that the problem (even to me it doesn't seem like the right way to do it) but I can't find another way around.
    I already tried to force the connection/disconnection, the GC seems to do his work, I know I can work with IIS server to recycle the pool application and limit the memory usage but it doesn't fix the issue. I know the issue come from the reload on the partial view, but I'm blocked. 
    Hope you guys can help.

    Model (The model is pretty basic only with some calculation like)

    public decimal TRS_EQ
            {
                get 
                {
                    if (pcs_theoriques_eq != " " && pcs_theoriques_eq != "" && pcs_theoriques_eq != null)
                    {
                        long theoEq = Convert.ToInt64(pcs_theoriques_eq);
                        if (pcs_bonnes_eq != null && pcs_bonnes_eq != " " && pcs_bonnes_eq != "" && theoEq != 0)
                        {
                            return Convert.ToDecimal(pcs_bonnes_eq) / theoEq * 100;
                        }
                    }
                    return 0.0m;
                }
            }

    Controller

    private IQueryable<TempsReel> collection;
    public ActionResult IndexPartial()
            {
                Response.Cache.SetNoStore();
                Response.Cache.SetNoServerCaching();
                using (var db = new SignalRTestContext())
                {
                    collection = from g in db.TempsReels select g;
                    collection = collection.OrderBy(g => g.groupe_machine);
                    ViewBag.NotifierEntity = db.GetNotifierEntity<TempsReel>(collection).ToJson();
                    ViewBag.IdMachine = Variables.idMachine;
                    ViewBag.Count = Variables.ListCatArrets.Count();
                    ViewBag.NomCategorie = Variables.ListCatArrets.Select(c => c.arretcatName).ToList();
                    ViewBag.Color = Variables.ListCatArrets.Select(c => c.arretcatColor).ToList();
                    collection = ArretTempsReel.OrganiseListParGroupe(collection);
                    GC.Collect();
                    db.Dispose();
                    return PartialView(collection.ToList());
                }               
            }

    View (reloading only partial view)

    <div id="tbTempsReelMachines">   
        @{ Html.RenderPartial("IndexPartial", Model); }
    </div>
    
    @section scripts 
    {
        <script src="~/Scripts/jquery.signalR-2.4.0.min.js"></script>
        <script src="~/signalr/hubs"></script>
        <script type="text/javascript">
            var signalRHubInitialized = false;
            $(function ()
            {
                InitializeSignalRHubStore();
            });
    
            function InitializeSignalRHubStore()
            {
                if (signalRHubInitialized)
                    return;
                try
                {
                    var clientHub = $.connection.tempsReelArretHub;
    
                    clientHub.client.broadcastMessage = function (message)
                    {
                        if (message === "Refresh")
                        {
                            ReloadIndexPartial();
                        }                  
                    };
                    $.connection.hub.start().done(function ()
                    {
                        clientHub.server.initialize($("#NotifierEntity").val());
                        signalRHubInitialized = true;
                    });
    
                } catch (err) {
                    signalRHubInitialized = false;
                }
            };
    
            function ReloadIndexPartial()
            {
    
                $.post('@(Url.Action("IndexPartial",
                "TempsReels", null, Request.Url.Scheme))')
                    .done(function (response)
                    {
                        $("#tbTempsReelMachines").html(response)     
                        if (!signalRHubInitialized)
                            InitializeSignalRHubStore();     
                    });
                $("#tbTempsReelMachines").end();
            };
        </script>
    }

    ViewModel (to organize the data)

    public class ArretTempsReel
        {
            public static IQueryable<TempsReel> OrganiseListParGroupe(IQueryable<TempsReel> InputList)
            {
                List<string> Groupe = InputList.Select(g => g.groupe_machine).Distinct().ToList();
                List<TempsReel> L = new List<TempsReel>();
                foreach (var item in Groupe)
                {
                    var l = InputList.Where(f => f.groupe_machine == item).ToList();
                    L.AddRange(l);
                    TempsReel SP = new TempsReel();
                    SP = SommePartielle(l);
                    L.Add(SP);
                }
                return L.AsQueryable<TempsReel>();
            }
            public static TempsReel SommePartielle(List<TempsReel> InputList)
            {
                TempsReelClone TC = new TempsReelClone();
                try
                {
                    foreach (var item in InputList)
                    {
                        TC.pcs_bonnes_eq += (item.pcs_bonnes_eq != null) ? Convert.ToInt64(item.pcs_bonnes_eq) : 0;
                        TC.pcs_bonnes_j += (item.pcs_bonnes_j != null) ? Convert.ToInt64(item.pcs_bonnes_j) : 0;
                        TC.pcs_bonnes_of += (item.pcs_bonnes_of != null) ? Convert.ToInt64(item.pcs_bonnes_of) : 0;
                        TC.pcs_mauvaises_eq += (item.pcs_mauvaises_eq != null) ? Convert.ToInt64(item.pcs_mauvaises_eq) : 0;
                        TC.pcs_mauvaises_j += (item.pcs_mauvaises_j != null) ? Convert.ToInt64(item.pcs_mauvaises_j) : 0;
                        TC.pcs_mauvaises_of += (item.pcs_mauvaises_of != null) ? Convert.ToInt64(item.pcs_mauvaises_of) : 0;
                        TC.pcs_theoriques_eq += (item.pcs_theoriques_eq != null) ? Convert.ToInt64(item.pcs_theoriques_eq) : 0;
                        TC.pcs_theoriques_j += (item.pcs_theoriques_j != null) ? Convert.ToInt64(item.pcs_theoriques_j) : 0;
                        TC.pcs_theoriques_of += (item.pcs_theoriques_of != null) ? Convert.ToInt64(item.pcs_theoriques_of) : 0;
                    }
                    return new TempsReel
                    {
                        pcs_bonnes_eq = TC.pcs_bonnes_eq.ToString(),
                        pcs_bonnes_j = TC.pcs_bonnes_j.ToString(),
                        pcs_bonnes_of = TC.pcs_bonnes_of.ToString(),
                        pcs_mauvaises_eq = TC.pcs_mauvaises_eq.ToString(),
                        pcs_mauvaises_j = TC.pcs_mauvaises_j.ToString(),
                        pcs_mauvaises_of = TC.pcs_mauvaises_of.ToString(),
                        pcs_theoriques_eq = TC.pcs_theoriques_eq.ToString(),
                        pcs_theoriques_j = TC.pcs_theoriques_j.ToString(),
                        pcs_theoriques_of = TC.pcs_theoriques_of.ToString(),
                        nom_machine = "TOTAL GROUPE",
                        categorie = "",
                        nom_arret = ""
    
                    };
                }
                catch (Exception)
                {
                    return null;
                }
            }

    Hub

    public class TempsReelHub : Hub
        {
            internal NotifierEntity NotifierEntity { get; private set; }
    
            public void DispatchToClient()
            {
                Clients.All.broadcastMessage("Refresh");
            }
    
            public void Initialize(String value)
            {
                NotifierEntity = NotifierEntity.FromJson(value);
                if (NotifierEntity == null)
                    return;
                Action<String> dispatcher = (t) => { DispatchToClient(); };
                PushSqlDependency.Instance(NotifierEntity, dispatcher);
            }
        }

    Notifier for SQL change

    public class SqlDependencyRegister
        {
            public event SqlNotificationEventHandler SqlNotification;
    
            readonly NotifierEntity notificationEntity;
    
            internal SqlDependencyRegister(NotifierEntity notificationEntity)
            {
                this.notificationEntity = notificationEntity;
                RegisterForNotifications();
            }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security",
            "CA2100:Review SQL queries for security vulnerabilities")]
            void RegisterForNotifications()
            {
                using (var sqlConnection = new SqlConnection(notificationEntity.SqlConnectionString))
                {
                    using (var sqlCommand = new SqlCommand(notificationEntity.SqlQuery, sqlConnection))
                    {
                        foreach (var sqlParameter in notificationEntity.SqlParameters)
                            sqlCommand.Parameters.Add(sqlParameter);
                        sqlCommand.Notification = null;
                        var sqlDependency = new SqlDependency(sqlCommand);
                        sqlDependency.OnChange += OnSqlDependencyChange;
                        if (sqlConnection.State == ConnectionState.Closed)
                        {
                            sqlConnection.Open();
                        }
                        sqlCommand.ExecuteNonQuery();
                        //sqlCommand.Parameters.Clear();
                        sqlConnection.Close();
                    }
                }
            }
            void OnSqlDependencyChange(object sender, SqlNotificationEventArgs e)
            {
                SqlNotification?.Invoke(sender, e);
                RegisterForNotifications();
            }
        }
    vendredi 10 mai 2019 09:56