locked
Database Updates appear to be Looping multiple times per update RRS feed

  • Question

  • User1429161042 posted

    I am new to SignalR. I am doing a, what appears to be a simple update to the browser off of a single table on my remote SQL server. The database is updated every 15 minutes and data is being push to the browser no problem. The issue I am having that isn't visible to the user is that the ajax update routine is cycling countless times in the back ground. Here is a small section of the web log off of the IIS server(7.0).

    2019-07-29 20:31:36 10.255.123.70 GET /PDM/Get id=ORL 9022 - 10.16.276.67
    2019-07-29 20:31:36 10.255.123.70 GET /PDM/Get id=ORL 9022 - 10.16.276.67
    2019-07-29 20:31:36 10.255.123.70 GET /PDM/Get id=ORL 9022 - 10.16.276.67
    2019-07-29 20:31:36 10.255.123.750GET /PDM/Get id=ORL 9022 - 10.16.276.67

    It does this to the point where I see this error in the browser development tools. "Failed to load resource: net::ERR_INSUFFICIENT_RESOURCES" Has anyone experienced this with database updates? 

    I'll be glad to share anything else from my code. Please let me know, any help anyone can offer me I would appreciate. Thanks so much

    Curt

    Monday, July 29, 2019 8:45 PM

All replies

  • User283571144 posted

    Hi Curt Kauffmann,

    According to your description, we couldn't directly find out the reason why the ajax update multiple times.

    Could you please post the details codes about your ajax and singlar?

    If you could post more details codes, it will be more easily for us to reproduce your issue and find out the solution.

    Best Regards,

    Brando

    Tuesday, July 30, 2019 1:57 AM
  • User1429161042 posted

    Thank you for your response! If it was something simple I overlooked I didn't want to clutter with a lot of code. A brief out line of the project. The web server is using IIS7(I suspect this may have some to do with it) and my sql server is 2008R2. When the user first browses to the site the interface is there but has no data. The user has to enter an ID and click run. . This fires an ajax that triggers a JsonResult that returns one row of data. After that, the database is updated behind the scenes every 15 minutes. Using the initial parameter entered by the user those updates are being successfully pushed to the browser. So here are my code items;

    I have one hub named  [HubName("pdmHub")] here is the code:

    using System;
    using System.Linq;
    using Microsoft.AspNet.SignalR;
    using Microsoft.AspNet.SignalR.Hubs;
    
    namespace PDM.Hubs
    {
        [HubName("pdmHub")]
        public class PDMHub : Hub
    
        {      
            public static void Show()
            {
                IHubContext context = GlobalHost.ConnectionManager.GetHubContext<PDMHub>();
                context.Clients.All.updateData();     
            }
        }
    }

    Here is my Startup.cs 

    using System;
    using System.Linq;
    using System.Web;
    using Owin;
    using Microsoft.Owin;
    using Microsoft.AspNet.SignalR;
    
    [assembly: OwinStartup(typeof(PDM.Startup))]
    namespace PDM
    {
        public class Startup
        {
            public void Configuration(IAppBuilder app)
            {
                GlobalHost.Configuration.DefaultMessageBufferSize = 10;
                app.MapSignalR();
            }
        }
    }

    Here is my Global.cs

    using System;
    using System.Linq;
    using System.Web;
    using System.Web.Http;
    using System.Web.Mvc;
    using System.Web.Optimization;
    using System.Web.Routing;
    using System.Configuration;
    using System.Data.SqlClient;
    
    namespace PDM
    {
        // Note: For instructions on enabling IIS6 or IIS7 classic mode, 
        // visit http://go.microsoft.com/?LinkId=9394801
    
        public class MvcApplication : System.Web.HttpApplication
        {
            string connString = ConfigurationManager.ConnectionStrings
            ["DefaultConnection"].ConnectionString;
    
            protected void Application_Start()
            {
                AreaRegistration.RegisterAllAreas();
                WebApiConfig.Register(GlobalConfiguration.Configuration);
                FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
                RouteConfig.RegisterRoutes(RouteTable.Routes);
                BundleConfig.RegisterBundles(BundleTable.Bundles);
                SqlDependency.Start(connString);
            }
    
            protected void Application_End()
            {
                //Stop SQL dependency
                SqlDependency.Stop(connString);
            }
    
        }
    }

    Here is my JsonReusult that works just fine. I pass in a parameter and it returns just one row. 

    using System;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using PDM.Hubs;
    using System.Data.SqlClient;
    using System.Configuration;
    
    namespace PDM.Controllers
    {
        public class PDMController : Controller
        {
            public ActionResult Index()
            {
                ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";
                return View();
            }
    
            public JsonResult Get(string id)
            {            
                    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                    {
                        connection.Open();
                        using (SqlCommand command = new SqlCommand(@"SELECT [org_id]
                                                                              ,[drivers_plan]
                                                                              ,[drivers_actual]
                                                                              ,[hours_plan]
                                                                              ,[hours_used]
                                                                              ,[hours_rate]
                                                                              ,[hours_change]
                                                                              ,[stops_plan]
                                                                              ,[stops_complete]
                                                                              ,[stops_rate]
                                                                              ,[stops_change]
                                                                              ,[delv_stops_plan]
                                                                              ,[delv_stops_complete]
                                                                              ,[delv_rate]
                                                                              ,[delv_change]
                                                                              ,isnull([delv_hour_range],'No Goal This Hour') as [delv_hour_range]
                                                                              ,isnull([delv_hour_qty],2) as [delv_hour_qty]
                                                                              ,[pu_stops_plan]
                                                                              ,[pu_stops_complete]
                                                                              ,[pu_rate]
                                                                              ,[pu_change]
                                                                              ,[pu_pending]
                                                                              ,isnull([pu_hour_range],'No Goal This Hour') as [pu_hour_range]
                                                                              ,isnull([pu_hour_qty],2) as [pu_hour_qty]
                                                                              ,[ob_shpts_plan]
                                                                              ,[ob_shpts_in_gate]
                                                                              ,[ob_rate]
                                                                              ,[ob_change]
                                                                              ,isnull([ob_hour_range],'No Goal This Hour') as [ob_hour_range] 
                                                                              ,isnull([ob_hour_qty],2) as [ob_hour_qty]
                                                                              ,[sph_current]
                                                                              ,[sph_change]
                                                                              ,[sph_trend]
                                                                              ,[sps_update_tmstp]
                                                                  FROM [dbo].[real_time_daily] where org_id = '" + id + "'", connection))
                              
                        {
                            // Make sure the command object does not already have                
                            // a notification object associated with it.                
                            command.Notification = null;
                            SqlDependency rt_dependency = new SqlDependency(command);
                            rt_dependency.OnChange += new OnChangeEventHandler(dependency_RealTime_OnChange);
                            if (connection.State == System.Data.ConnectionState.Closed)
                                connection.Open();
                            SqlDataReader reader = command.ExecuteReader();
                        
                            var listPD = reader.Cast<System.Data.IDataRecord>()
                                .Select(x => new
                                {
                                    org_id = x.GetString(0),
                                    drivers_plan = x.GetInt32(1),
                                    drivers_actual = x.GetInt32(2),
                                    hours_plan = x.GetDecimal(3),
                                    hours_used = x.GetDecimal(4),
                                    hours_rate = x.GetDouble(5),
                                    hours_change = x.GetDouble(6),
    
                                    stops_plan = x.GetInt32(7),
                                    stops_complete = x.GetInt32(8),
                                    stops_rate = x.GetDouble(9),
                                    stops_change = x.GetDouble(10),
    
                                    delv_stops_plan = x.GetInt32(11),
                                    delv_stops_complete = x.GetInt32(12),
                                    delv_rate = x.GetDouble(13),
                                    delv_change = x.GetDouble(14),
                                    delv_hour_range = x.GetString(15),
                                    delv_hour_qty = x.GetInt32(16),
    
                                    pu_stops_plan = x.GetInt32(17),
                                    pu_stops_complete = x.GetInt32(18),
                                    pu_rate = x.GetDouble(19),
                                    pu_change = x.GetDouble(20),
                                    pu_pending = x.GetInt32(21),
                                    pu_hour_range = x.GetString(22),
                                    pu_hour_qty = x.GetInt32(23),
    
                                    ob_shpts_plan = x.GetInt32(24),
                                    ob_shpts_in_gate = x.GetInt32(25),
                                    ob_rate = x.GetDouble(26),
                                    ob_change = x.GetDouble(27),
                                    ob_hour_range = x.GetString(28),
                                    ob_hour_qty = x.GetInt32(29),
    
                                    sph_current = x.GetDecimal(30),
                                    sph_change = x.GetDouble(31),
                                    sph_trend = x.GetString(32),
                                    sps_update_tmstp = x.GetDateTime(33).ToString()                             
                                }).ToList();
    
                            return Json(new { listPD = listPD }, JsonRequestBehavior.AllowGet);
                        }
                    }
            }
    
            private void dependency_RealTime_OnChange(object sender, SqlNotificationEventArgs e)
            {
                if (e.Type == SqlNotificationType.Change)
                {
                    PDMHub.Show();
                }
            }
        }
    }
    

    Here is my javascript:

    $(function () {
        // Proxy created on the fly            
        var realtime = $.connection.pdmHub;
        var id = $('#SearchID').val().toUpperCase();
    
        realtime.client.updateData = function () {
            
            getRTData(id);
    
        };
    
        // Start the connection   
        $.connection.hub.start().done(function () {
           
            getRTData();
    
        }).fail(function (error) {
    
            alert(error);
    
        });
    
        $.connection.hub.disconnected(function () {
    
            alert('disconnected');
            setTimeOut(function () {
    
                $.connection.start();
    
            }, 5000);
    
        });
    
      
    });
    
    function myFunction(event) {
    
        var x = event.which || event.keyCode;
    
        if (x == 13) {
    
            var id = $('#SearchID').val();
            getRTData(id.toUpperCase());
           
        } else {
            return true;
        }
       
    }
    
    var count = 0;
    
    function getRTData(id) {
    
        var id = $('#SearchID').val().toUpperCase();
        var url = '/PDM/Get?id=' + id;
    
       // debugger;
        $.ajax({
           
            url: url,
            type: 'GET',
            datatype: 'json',
            success: function (data) {       
    
                    UpdateInterfaceData(data);        
                    $('#org_id_hidden').val(id.toUpperCase());
    
                }        
        });   
    }

    The UpdateInterface function is passed the data and just updates the individual data elements on the view. It's fairly long:

    function UpdateInterfaceData(data) {
    
        var data_tmstp = data.listPD[0].sps_update_tmstp.toString(); 
        // alert(data_tmstp);
        var data_org_id = data.listPD[0].org_id;
               
       
            //if (data.listPD.length > 0) {
    
            //////////////////////////////
            /////// org_id //////////////
            /////////////////////////////
    
            $('#org_id').text(data.listPD[0].org_id);
    
            /////////////////////////////
            /////// drivers div code //////
            /////////////////////////////
        
            $('#drvr_hours_plan').text(data.listPD[0].drivers_plan.toFixed(0)).fadeIn(1500);         
            $('#drvr_hours_used').text(data.listPD[0].drivers_actual.toFixed(0)).fadeIn(1500);
               
    
            // /////////////////////////////
            // /////// hours div code //////
            // /////////////////////////////
    
            $("#route_hours_plan").text(data.listPD[0].hours_plan.toFixed(0)).fadeIn(1500);          
            $("#route_hours_used").text(data.listPD[0].hours_used.toFixed(0)).fadeIn(1500);
             
            var pct = parseFloat(data.listPD[0].hours_rate * 100).toFixed(2);             
            $("#pct_hours_plan_dec").text(pct + '%').fadeIn(1500);
    
            $("#hrs_pct_chg").text(data.listPD[0].hours_change.toFixed(0)).fadeIn(1500);
    
    
            // /////////////////////////////
            // /////// stops div code //////
            // /////////////////////////////
               
            $("#stops_plan").text(data.listPD[0].stops_plan.toFixed(0)).fadeIn(1500);            
            $("#stops_complete").text(data.listPD[0].stops_complete.toFixed(0)).fadeIn(1500);
      
            var pct = parseFloat(data.listPD[0].stops_rate * 100).toFixed(2);
            $("#pct_stops_plan_completed").text(pct + '%').fadeIn(1500);
                        
            $("#stops_pct_chg").text(data.listPD[0].stops_change.toFixed(0)).fadeIn(1500);        
    
            // ///////////////////////////
            // /// pickups div code //////
            // ///////////////////////////
    
            $('#pu_stops_planned').text(data.listPD[0].pu_stops_plan.toFixed(0)).fadeIn(1500);
               
            $('#pu_stops_completed').text(data.listPD[0].pu_stops_complete.toFixed(0)).fadeIn(1500);
                 
            $('#pu_pending').text(data.listPD[0].pu_pending.toFixed(0)).fadeIn(1500);
    
                
            pct = parseFloat(data.listPD[0].pu_rate * 100).toFixed(2);
            $('#pct_pu_plan_compl_dec').text(pct + '%').fadeIn(1500);
     
            $('#pu_pct_chg').text(data.listPD[0].pu_change.toFixed(0)).fadeIn(1500);
                  
            var pu_hour_qty = data.listPD[0].pu_hour_qty;
            var pu_hour_range = data.listPD[0].pu_hour_range;
    
            if (pu_hour_qty == 0) {
                document.getElementById("pu_indicator").src = "/Images/red-down-arrow.png";
            }
            else if (pu_hour_qty == 1) {
                document.getElementById("pu_indicator").src = "/Images/yellow-warning.png";
            }
            else if (pu_hour_qty == 2 && pu_hour_range != 'No Goal This Hour') {
                document.getElementById("pu_indicator").src = "/Images/green-up-arrow.png";
            }
            else  {
                document.getElementById("pu_indicator").src = "/Images/gray-disabled-large.png";
            }
    
            var pu_hour_range = data.listPD[0].pu_hour_range;
            $('#pu_hour_rng').text(data.listPD[0].pu_hour_range.toLocaleString());
    
            // //////////////////////////////
            // ///// delivery div code //////
            // //////////////////////////////
    
            $('#delv_stops_planned').text(data.listPD[0].delv_stops_plan.toFixed(0)).fadeIn(1500);          
            $('#delv_stops_completed').text(data.listPD[0].delv_stops_complete.toFixed(0)).fadeIn(1500);  
         
            pct = parseFloat(data.listPD[0].delv_rate * 100).toFixed(2);
            $('#pct_delv_plan_completed').text(pct + '%').fadeIn(1500);     
    
            $('#delv_pct_chg').text(data.listPD[0].delv_change).fadeIn(1500);    
    
            var delv_hour_qty = data.listPD[0].delv_hour_qty;
            var delv_hour_range = data.listPD[0].delv_hour_range;
    
            if (delv_hour_qty == 0) {
    
                     
                document.getElementById("delv_indicator").src = "/Images/red-down-arrow.png";
            }
            else if (delv_hour_qty == 1) {
                       
                document.getElementById("delv_indicator").src = "/Images/yellow-warning.png";
            }
            else if (delv_hour_qty == 2 && delv_hour_range != 'No Goal This Hour') {
                       
                document.getElementById("delv_indicator").src = "/Images/green-up-arrow.png";
            }
            else {
                document.getElementById("delv_indicator").src = "/Images/gray-disabled-large.png";
            }
    
            var delv_hour_range = data.listPD[0].delv_hour_range;
            $('#delv_hour_rng').text(data.listPD[0].delv_hour_range.toLocaleString());               
    
    
            // /////////////////////////////
            // ///// outbound div code /////
            // /////////////////////////////
                   
            $('#ob_shpts_plan').text(data.listPD[0].ob_shpts_plan).fadeIn(1500);
            $('#ob_shpts_in_gate').text(data.listPD[0].ob_shpts_in_gate).fadeIn(1500);
                   
            pct = parseFloat(data.listPD[0].ob_rate * 100).toFixed(2);       
            $('#pct_ob_plan_dec').text(pct + '%').fadeIn(1500);   
    
            $('#ob_pct_chg').text(data.listPD[0].ob_change).fadeIn(1500);
          
            var ob_hour_qty = data.listPD[0].ob_hour_qty;
            var ob_hour_range = data.listPD[0].ob_hour_range;
    
            if (ob_hour_qty == 0) {
                document.getElementById("ob_indicator").src = "/Images/red-down-arrow-small.png";
            }
            else if (ob_hour_qty == 1) {
                document.getElementById("ob_indicator").src = "/Images/yellow-warning-small.png";
            }
            else if (ob_hour_qty == 2 && ob_hour_range != 'No Goal This Hour') {
                document.getElementById("ob_indicator").src = "/Images/green-up-arrow-small.png";
            }
            else {
                document.getElementById("ob_indicator").src = "/Images/gray-disabled-small.png";
            }
    
            var ob_hour_range = data.listPD[0].ob_hour_range;
            $('#ob_hour_rng').text(data.listPD[0].ob_hour_range.toLocaleString());
    
    
            // ///////////////////////////////////////
            // ///// shipments per hour div code /////
            // ///////////////////////////////////////
                    
            var cur = parseFloat(data.listPD[0].sph_current).toFixed(2);
            $('#sph_current').text(cur).fadeIn(1500);
             
            var chg = parseFloat(data.listPD[0].sph_change).toFixed(2);
            $('#sph_pct_chg').text(chg).fadeIn(1500);
               
            trend = data.listPD[0].sph_trend.toString();
            if (trend == 'U') {
    
                document.getElementById("sph_indicator").src = "/Images/green-up-arrow-x-small.png";                  
                       
            }
            else {
                       
                document.getElementById("sph_indicator").src = "/Images/red-down-arrow-x-small.png";
            }
    
    
            // ///////////////////////////////////////
            // /////////// update times //////////////
            // ///////////////////////////////////////
    
            $('#sps_update_tmstp').text(data.listPD[0].sps_update_tmstp);
    
            playAudio();             
    
    }

    That's pretty much the whole project, I REALLY APPRECIATE YOUR RESPONSE AND DESIRE TO HELP! Thanks you for any advice you can give!

    Curt

    Tuesday, July 30, 2019 1:20 PM
  • User283571144 posted

    Hi Curt Kauffmann,

    According to your description, I found you have set disconnected function to re-call the  $.connection.start function.

    I guess this is the reason why you send the multiple ajax request to the server.

    Besides, I found you have already used the signalr, there is no need to use ajax to get the data again.

    You could directly send the data from the hub method to client side by using signalr.

    Best Regards,

    Brando

    Tuesday, August 6, 2019 9:41 AM
  • User1429161042 posted

    Brando,

    Thank you so much for your time! I had reworked much of this before your response, doing almost exactly what you have recommended. I am still seeing that if there are 10 users connected, that on every update each connected user will loop through 10 times. If the user count goes to 11, the next update will loop 11 times for each user. How can I stop this behavior? This is the way the update is passed to the4 client; 

    ///my query is here...then

    IHubContext context = GlobalHost.ConnectionManager.GetHubContext<TestHub>();
    context.Clients.All.getUpdates(listPD);

    Any help you can provide would be greatly appreciated. Thanks again for you time!

    Curt

    Tuesday, August 6, 2019 10:14 PM