locked
Am trying cache a api response data but sometimes its very slow to fetch RRS feed

  • Question

  • User1888635512 posted

    Hi,
    After executing my sql query regarding the data and i got the data into a list and i have added the data into cache in memory method.

    var cacheKey = "mainCategory " + page;
                    if (!_memoryCache.TryGetValue(cacheKey, out _details))
                    {
                        _details = _services.LoadMainCategoryWithMainServicesLimitedBy2(page);
                        var cacheExpirationOptions = new MemoryCacheEntryOptions
                        {
                            AbsoluteExpiration = DateTime.Now.AddHours(18),
                            Priority = CacheItemPriority.High,
                            SlidingExpiration = TimeSpan.FromHours(18)
                        };
                        _memoryCache.Set(cacheKey, _details, cacheExpirationOptions);
                    }

    Issue is that when i try to call the api call first time its takes more than 10 second to load the data. After again i call it comes out within 150 ms. after a few minute i again call the api its taking more than 10 second some time. i have added expire time for 18 hour and SlidingExpiration also 18 hour. 
    So when my first call is initiated then response is cached right, So after a while i again call the api still its taking a lot time to respond even thought cached. 

    Thursday, July 9, 2020 11:02 AM

All replies

  • User475983607 posted

    The "page" variable is part of the cache key.  I assume the page variable changes which executes LoadMainCategoryWithMainServices().  

    Troubleshooting this code is trivial.  Place a break point on the LoadMainCategoryWithMainServices line and run the code.  Monitor the page variable.  

    If you still need community assistance, then share all the relevant code so the community is not forced to guess how you code works.

    Thursday, July 9, 2020 11:22 AM
  • User1888635512 posted

    My one doubt is that. Key is unique right. Suppose if there is a cache key already exists with name "mainCategory 0". Then the cache data with this key is returned right ?.
    Here they page is page number.
    In controller i have written

    [Route("home_categories")]
            [HttpGet]
            public IActionResult Get_Main_Service_From_Main_CategoryLimitedBy2(string page)
            {
                try
                {
                    ServicesModel _services = new ServicesModel(_config.GetConnectionString("DefaultConnection"), _config, _protector, _httpContextAccessor, _memoryCache);
                    List<ResponseMessage> _resmsg = new List<ResponseMessage>();
                    List<Main_Category> _details = new List<Main_Category>();
                    if (null == page || page == "")
                    {
                        page = "0";
                    }
                    var cacheKey = "mainCategory " + page;
                    if (!_memoryCache.TryGetValue(cacheKey, out _details))
                    {
                        _details = _services.LoadMainCategoryWithMainServicesLimitedBy2(page);
                        var cacheExpirationOptions = new MemoryCacheEntryOptions
                        {
                            AbsoluteExpiration = DateTime.Now.AddHours(18),
                            Priority = CacheItemPriority.High,
                            SlidingExpiration = TimeSpan.FromHours(18)
                        };
                        _memoryCache.Set(cacheKey, _details, cacheExpirationOptions);
                    }
                    var json = JsonConvert.SerializeObject(_details);
                    string ResultJson = json.Trim();
                    return new ObjectResult(ResultJson);
                }
                catch (Exception ex)
                {
    
                    throw (ex);
                }
            }

    In class

    internal List<Main_Category> LoadMainCategoryWithMainServicesLimitedBy2(string page)
            {
                List<Main_Category> _details = new List<Main_Category>();
                int pageLimitValue = 10, pageOffsetValue = 0; if (Convert.ToInt32(page) == 0) { pageOffsetValue = Convert.ToInt32(page); } else { pageOffsetValue = Convert.ToInt32(page) * pageLimitValue; }
                ConnectionModel _selectmaincategorydata = new ConnectionModel(_connectionString);
                ConnectionModel _selectmainservicedata = new ConnectionModel(_connectionString);
                var status = "Active"; var Main_Category_Uniqueid = ""; var Main_Category_Name = "";
                _selectmaincategorydata.SelectData("Select distinct mc.Main_Category_Uniqueid, mc.Main_Category_Name, mc.Main_Category_Photo " +
                            "from tbl_maincategory mc " +
                            "INNER JOIN tbl_vendormaincategory vmc on vmc.Vendor_Main_Category_Uniquieid = mc.Main_Category_Uniqueid and vmc.Vendor_Main_Category_Status = '" + status + "' " +
                            "INNER JOIN tbl_vendor v on v.Vendor_uniqueid = vmc.Vendor_Main_Category_Vendor_Id and v.Vendor_Status = '" + status + "' " +
                            "INNER JOIN tbl_mainservices ms on ms.Main_Services_Main_Category = mc.Main_Category_Uniqueid and ms.Main_Services_Status = '" + status + "' " +
                            "INNER JOIN tbl_vendormainservices vms on vms.Vendor_Main_Service_Uniquieid = ms.Main_Services_Uniqueid and vms.Vendor_Main_Service_Status = '" + status + "' and vms.Vendor_Main_Service_Vendor_Id = v.Vendor_uniqueid " +
                            "INNER JOIN tbl_subservices ss on ss.Sub_Services_Main_Service = vms.Vendor_Main_Service_Uniquieid and ss.Sub_Services_Status = '" + status + "' " +
                            "INNER JOIN tbl_vendorsubservices vss on vss.Vendor_Sub_Service_Uniquieid = ss.Sub_Services_Uniqueid and vss.Vendor_Sub_Service_Status = '" + status + "' and vss.Vendor_Sub_Service_Vendor_Id = v.Vendor_uniqueid " +
                            "INNER JOIN tbl_vendorpackages vp on vp.Package_Sub_Service_Id = vss.Vendor_Sub_Service_Uniquieid and vp.Package_Status = '" + status + "' and vp.Package_Vendor_Id = v.Vendor_uniqueid " +
                            "INNER JOIN tbl_vendoremployeepackage vep on vep.Vendor_Employe_Package_uniqueid = vp.Package_Uniqueid and vep.Vendor_Employe_Package_Status = '" + status + "' " +
                            "INNER JOIN tbl_vendoremployee ve on ve.Vendor_Employee_Uniqueid = vep.Vendor_Employe_Package_Employee_uniqueid and ve.Vendor_Employee_Status = '" + status + "' and ve.Vendor_Employee_Vendor_Id = v.Vendor_uniqueid " +
                            "Where mc.Main_Category_Status = '" + status + "' " +
                            "Order by mc.Main_Category_Name ASC OFFSET " + pageOffsetValue + " ROWS FETCH NEXT " + pageLimitValue + " ROWS ONLY");
                if (_selectmaincategorydata._dataTable.Rows.Count > 0)
                {
                    for (int i = 0; i < _selectmaincategorydata._dataTable.Rows.Count; i++)
                    {
                        Main_Category_Uniqueid = _selectmaincategorydata._dataTable.Rows[i]["Main_Category_Uniqueid"].ToString();
                        Main_Category_Name = _selectmaincategorydata._dataTable.Rows[i]["Main_Category_Name"].ToString();
                        _details.Add(new Main_Category
                        {
                            maincategoryid = Main_Category_Uniqueid,
                            category_name = Main_Category_Name,
                            services = Get_Main_Service_From_Main_CategoryLimitedBy2(Main_Category_Uniqueid)
                        });
                    }
                }
                return _details;
            }
    
    internal List<Main_Services> Get_Main_Service_From_Main_CategoryLimitedBy2(string Main_Category_Id)
            {
                List<Main_Services> _details = new List<Main_Services>();
                ConnectionModel _selectmainservices = new ConnectionModel(_connectionString);
                CommonModel _load = new CommonModel(_connectionString, _config, _httpContextAccessor);
                var Main_Services_Uniqueid = ""; var status = "Active";
                _selectmainservices.SelectData("Select distinct ms.Main_Services_Name, ms.Main_Services_Photo, ms.Main_Services_Uniqueid " +
                                "from tbl_maincategory mc " +
                                "INNER JOIN tbl_vendormaincategory vmc on vmc.Vendor_Main_Category_Uniquieid = mc.Main_Category_Uniqueid and vmc.Vendor_Main_Category_Status = '" + status + "' " +
                                "INNER JOIN tbl_vendor v on v.Vendor_uniqueid = vmc.Vendor_Main_Category_Vendor_Id and v.Vendor_Status = '" + status + "' " +
                                "INNER JOIN tbl_mainservices ms on ms.Main_Services_Main_Category = mc.Main_Category_Uniqueid and ms.Main_Services_Status = '" + status + "' and ms.Main_Services_Main_Category = '" + Main_Category_Id + "' " +
                                "INNER JOIN tbl_vendormainservices vms on vms.Vendor_Main_Service_Uniquieid = ms.Main_Services_Uniqueid and vms.Vendor_Main_Service_Status = '" + status + "' and vms.Vendor_Main_Service_Vendor_Id = v.Vendor_uniqueid " +
                                "INNER JOIN tbl_subservices ss on ss.Sub_Services_Main_Service = vms.Vendor_Main_Service_Uniquieid and ss.Sub_Services_Status = '" + status + "' " +
                                "INNER JOIN tbl_vendorsubservices vss on vss.Vendor_Sub_Service_Uniquieid = ss.Sub_Services_Uniqueid and vss.Vendor_Sub_Service_Status = '" + status + "' and vss.Vendor_Sub_Service_Vendor_Id = v.Vendor_uniqueid " +
                                "INNER JOIN tbl_vendorpackages vp on vp.Package_Sub_Service_Id = vss.Vendor_Sub_Service_Uniquieid and vp.Package_Status = '" + status + "' and vp.Package_Vendor_Id = v.Vendor_uniqueid " +
                                "INNER JOIN tbl_vendoremployeepackage vep on vep.Vendor_Employe_Package_uniqueid = vp.Package_Uniqueid and vep.Vendor_Employe_Package_Status = '" + status + "' " +
                                "INNER JOIN tbl_vendoremployee ve on ve.Vendor_Employee_Uniqueid = vep.Vendor_Employe_Package_Employee_uniqueid and ve.Vendor_Employee_Status = '" + status + "' and ve.Vendor_Employee_Vendor_Id = v.Vendor_uniqueid " +
                                "Where mc.Main_Category_Status = '" + status + "' " +
                                "Order by ms.Main_Services_Name ASC OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY");
                if (_selectmainservices._dataTable.Rows.Count > 0)
                {
                    for (int i = 0; i < _selectmainservices._dataTable.Rows.Count; i++)
                    {
                        Main_Services_Uniqueid = _selectmainservices._dataTable.Rows[i]["Main_Services_Uniqueid"].ToString();
                        _details.Add(new Main_Services
                        {
                            thumbnail = _selectmainservices._dataTable.Rows[i]["Main_Services_Photo"].ToString(),
                            name = _selectmainservices._dataTable.Rows[i]["Main_Services_Name"].ToString(),
                            service_uniqueid = _selectmainservices._dataTable.Rows[i]["Main_Services_Uniqueid"].ToString(),
                            description = Get_Main_Service_Providers_Count(Main_Services_Uniqueid),
                        });
                    }
                }
                return _details;
            }




    Thursday, July 9, 2020 11:31 AM
  • User475983607 posted

    Come on man!  The community only sees code with many potential problems. Use the debugger rather than guessing how your code is functioning,  The community cannot do this for you...

    The first step is checking the state of the "page" variable across multiple requests.  Place a break point here.

    var cacheKey = "mainCategory " + page;

    And 

    _details = _services.LoadMainCategoryWithMainServicesLimitedBy2(page);

    This allow you to check the state of page and determine if LoadMainCategoryWithMainServicesLimitedBy2 runs more times than expected.

    Once you figure out what happening then you can move on to the next troubleshooting step.

    Thursday, July 9, 2020 12:17 PM
  • User-474980206 posted

    rather than caching, you should fix the underlying problem. with proper design and indexes this sql query should run under 100ms.

    Thursday, July 9, 2020 2:56 PM
  • User753101303 posted

    Hi,

    I suspect the query is not optimal:
    - you have few columns coming from a single table
    - you have quite a number of joins (maybe with multiple "branches") generating likely a high number of rows
    - and then you need to use DISTINCT to remove duplicates introduced by using those joins

    I would start by using SELECT COUNT(*) FROM <samequery> to see how many rows you generated compared with how many rows you finally keep.

    If you have quite a difference it is likely you should rewrite the query without duplicating/deduplicating rows (using an EXISTS clause or maybe a COUNT for each "branch") so that you can check directly for each mainservice rows if you have related active data or not

    Thursday, July 9, 2020 3:06 PM