Code Optimization

Interesting things about software development and code optimization

MS SQL Maintenance and Performance

Hello friends,


I want to share my experience with MS SQL maintenance and performance with regards to nonclustered Indexes and Fragmentation.


If you do delete unnecessary rows in tables from time to time then you definitely need to maintenance fragmented indexes.

When you delete a row in a table SQL Server modifies indexes and it leads to fragmentation that leads to slowing down query performances on that table.


To avoid any bottlenecks and be sure your fragmentation is minimal and performance is maximum you need to reorganize indexes or rebuild them.


I use the following piece of stored procedure code whenever I do any clean on a table:


    StoredProcedure

....

    -- Reorganize the NCIX_MyTable 
    -- index on the dbo.MyTable table.   
SELECT @frag = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'my_database'), OBJECT_ID(N'dbo.MyTable'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id and [name] = N'NCIX_MyTable';
if @frag>=50 begin --To rebuild a fragmented index ALTER INDEX NCIX_MyTable
ON dbo.MyTable REBUILD WITH (FILLFACTOR = 50); end; if @frag>=25 begin ALTER INDEX NCIX_MyTable ON dbo.MyTable REORGANIZE; end; ....


Conclusion - it keeps your DB size to minimum, removes unnecessary data and keeps sql performance to maximum.


Thank you and see you ;)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



SSL Time and Rating

Hello,


Today I'm going to share some experience about SSL rating, time, security, performance and why it is better to turn off the RC4 protocol.


Also I did find and would like to share two useful resources that you can use to check your SSL and Website overall performance:

https://www.dotcom-tools.com/website-speed-test.aspx to analyze your website from different world locations

https://www.ssllabs.com/ssltest/analyze.html to analyze your SSL certificate


Using that two tools I did find a few main issues: my IIS server were still using RC4 that is considered non-secure, my DNS resolving time was too long from some points of world and my SSL handshake time was not very fast.


DNS resolving time - is still an issue as it require non-server and non-application actions to be taken to resolve it :(

SSL handshake is not so easy to resolve as well but what I have noticed is that resolving RC4 did speedup overall website loading performance and increase overall security rating.


So first step I would suggest is disabling the RC4 protocol. Lets take a look how to disable it on Windows Server with IIS:

- Open the RegEdit (Win + R >> regedit) and find the following key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Ciphers


- Right-click on Ciphers >> New >> Key and name it RC4 40/128

Hardening_14.jpg


- Right-click on RC4 40/128 >> New >> DWORD (32-bit) Value and name the value Enabled

Hardening_15.jpg


- Double-click the created Enabled value and make sure that there is zero (0) in Value Data field then click OK

Hardening_16.jpg


- Repeat those steps and create two more keys with the names RC4 56/128 and RC4 128/128 in the Ciphers directory

Hardening_17.jpg


- Close the RegEdit


In my case it was not required to reboot my server so I hope you will see the result immediately as well using the ssllabs web-tool I mentioned before.


This will give your A Rating for your SSL website security and as I noticed it speed up your website overall loading time (including SSL time) by 1.2-1.5 times.




Hope that will help you as well and let me know if you can add some useful info in comments.


Thank you and see you :)




1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Top 1 Anti-Spam Contact Form

The Best Anti-Spam Contact Form for Your Website 


Hello friends,

today I'm going to share my experience with implementing the best, in my opinion, anti-spam mechanism for web contact form.

There is well known problem - when you have a contact form on your website then you will get a lot of spam submitted via the form action url on your website and this is very annoying as for me. So I started thinking on how to do it in a way so I would get no spam at all or as less as possible at least.

First thing that came in my mind was that we would need a dynamic and random form action url for the form submission that will return HTTP 404 not found in case user would not pass human-like verification with a captcha or would try to guess the url.

As I work with C#.NET + MVC I started creating such solution with ASP.NET MVC and actually here is what I came up to.

First thing we need is to register our website for google reCAPTCHA v2, get public and secret keys and add reCAPTCHA to an our contact form. Note that in our case we need to render reCAPTCHA by hand and here is the Contact View html:


@section head
{
    <script src="https://www.google.com/recaptcha/api.js?onload=renderRecaptcha&render=explicit" async defer></script>
}

...

    @using (Html.BeginForm("", "", FormMethod.Post, new { id="contactForm" }))
    {
        @Html.AntiForgeryToken()

        ...

        <div id="ReCaptchContainer"></div>

        ...
        
        <input type="submit" value="Send" />
    }

...

@section scripts
{
    <script>
        var your_site_key = '.....-....';
        var canProceed = false;

        var reCaptchaCallback = function (response) {
            //lets go to server, validate a token and return a random url if succeed
            $.ajax({
                method: "POST",
                url: "/Contact/IsReCaptchValid",
                cache: false,
                data: {
                    gRecaptchaResponse: response
                }
            }).done(function (html) {
                if (html != "none") {
                    $("#contactForm").attr("action", html);
                    canProceed = true;
                    $("#submit").removeClass("disabled");
                    $("#submit").addClass("btn-success");
                }
            });
        };

        var renderRecaptcha = function () {
            //lets render our captcha
            grecaptcha.render('ReCaptchContainer', {
                'sitekey': your_site_key,
                'callback': reCaptchaCallback
            });
        };

        $(document).ready(function () {
            $("input[type='submit']").on("click", function (e) {

                ...

                if (canProceed != true) {
                    //CAPTCHA has not been validated yet
                    e.stopPropagation();
                    return false;
                }
            });
        });
    </script>
}

Here we load google api javascript file, run captcha rendering and attach a handler for validation response that we will use to send validation token to our back-end, validate it and return a new dynamic random URL for our <form> to submit data to.

Here is the server side controller action for reCAPTCHA validation:

        public ActionResult IsReCaptchValid(string gRecaptchaResponse)
        { //check referrer url to see if this request came from our own website
            if (Request.UrlReferrer == null || string.IsNullOrEmpty(Request.UrlReferrer.AbsolutePath) ||
                     !(Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName + "/contact") ||
                     Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName2 + "/contact")))
            {
                return HttpNotFound("Resource was not found");
            }
            string result = "none";
            var captchaResponse = gRecaptchaResponse;
            
            var apiUrl = "https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}";
            var requestUri = string.Format(apiUrl, secretKey, captchaResponse);
            var request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(requestUri);
            using (System.Net.WebResponse response = request.GetResponse())
            {
                using (System.IO.StreamReader stream = new System.IO.StreamReader(response.GetResponseStream()))
                {
                    JObject jResponse = JObject.Parse(stream.ReadToEnd());
                    var isSuccess = jResponse.Value<bool>("success");
                    if (isSuccess)
                    { //store token in session to re-validate later
                        Session["g-recaptcha-response"] = gRecaptchaResponse; //generate random url
                        result = "/Contact/" + 
                            ((char)rnd.Next(0x61, 0x7b)) + "" + //random char a-z
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" ; //store the random url to validate later
                        Session["contactFormAction"] = result;
                    }
                }
            }
            return Content(result);
        }

As you may guessed this will validate google reCAPTCHA token and generate a random url like /Contact/edvbbgrq

Next step will be to accept POST request with all the data, validate and process it.
To be able to accept random urls like /Contact/edvbbgrq in one controller and one action you will have to define some routes, so lets define them now in our RouteConfig.cs:

            //route to accept /Contact/Contact get request and return our contact form/view
            routes.MapRoute(
                name: "Contact1",
                url: "Contact/Contact",
                defaults: new { controller = "Contact", action = "Contact" }
                ); //route to accept /Contact/IsReCaptchValid request and return our random urls
            routes.MapRoute(
                name: "Contact2",
                url: "Contact/IsReCaptchValid",
                defaults: new { controller = "Contact", action = "IsReCaptchValid" }
                ); //route to accept our random urls and submit contact requests
            routes.MapRoute(
                name: "CContact",
                url: "Contact/{*slug}",
                defaults: new { controller = "Contact", action = "CContact", slug = System.Web.Http.RouteParameter.Optional }
                );

And actually final step is implementing our POST action to accept random-url submit requests:

        //to return view Contact in Get and POSTback requests
        public ActionResult CContact(string slug = null)
        {
            return View("Contact");
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CContact(string slug, string name, string subject, string message, string email)
        { //check if this request is from our own website
            if (Request.UrlReferrer == null || string.IsNullOrEmpty(Request.UrlReferrer.AbsolutePath) ||
                     !(Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName + "/contact") ||
                     Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName2 + "/contact")))
            {
                return HttpNotFound("Resource was not found");
            }
            //get previously stored random url
            string view = Session["contactFormAction"] as string;
            if (string.IsNullOrEmpty(view))
            {
                return HttpNotFound("Resource was not found");
            }
            view = view.Replace("/Contact/", string.Empty); //get random part from route to compare
            if(RouteData.GetRequiredString("slug") != view)
                return HttpNotFound("Resource was not found"); //just in case let's check the token one more time
            var captchaResponse = Request.Form["g-recaptcha-response"];
            if((string)Session["g-recaptcha-response"]!= captchaResponse)
            {
                return HttpNotFound("Resource was not found");
            }

            ... //do whatever you need to process request ...
            ViewBag.Message = "Thank you, your request has been submitted.";

            return View("Contact");
        }

That's it. Now you have dynamic action form url and this url is generated after human-validation only so it will be impossible or almost impossible at least  for spammers to send spam requests.

Thank you and see you :)



1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



SSS - Simple Serverless Search for your website

Hello friends,


if you are in need of simple, your own search that will search through all your help documents then this may be a good case for you.

I will show my own simple and fast way if such implementation that I'm sure you will be able to use wherever you need: PHP, Java, Python, .NET, Ruby, Node.JS, etc.


So, lets start and see how it works!


1. First stage will be indexing your help files or DB records. In my case I had html files, so here is my code:

        
        char[] cToRemove = new char[] { '\r', '\n', '\t', ' ', ',', '/', '\\', '~', '–', '.', ':', '\'', '!', ';', '[', ']', '"', '{', '}', '=', '+', '_', ')', '(', '*', '&', '?', '%', '$', '#', '@', '`', '<', '>', '|' };
        char[] cToSplit = new char[] { ' ' };
        string[] toSkip = new string[] 
        {
            "i", "me", "you", "he", "she", "they", "them", "this", "that", "than", "then",  "it", "our", "their", "her", "his", "its", "it's",
            "was", "were", "is","are", "be", "being", "been", "can", "could", "should", "shall", "will", "would", "have", "has", "did", "do", "does",
            "may", "might", "must", "need", "better", "if", "else","also", "same", "now","new", "below", "above",
            "itself", "ourselves", "himself", "herself", "theirselves", "let", "get", "set", "done"
        };

        public ActionResult RunHelpCrawler()
        {

            string root = System.Web.HttpContext.Current.Server.MapPath("~/Views/Help");
            string json = System.Web.HttpContext.Current.Server.MapPath("~/shelp.json");

            AsyncManager.OutstandingOperations.Increment();
            System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                string suberrors = string.Empty;
                try
                {
                    string path = root;
                    string lastKeywrd = string.Empty;
                    //keywords, url, title
                    List<Models.Search> search = new List<Models.Search>();

                    foreach (string filePath in System.IO.Directory.EnumerateFiles(path))
                    {
                        //exclude everything you need
                        if (filePath.Contains("LeftSideMenu") || filePath.Contains("GetHelpTips"))
                        {
                            continue;
                        }

                        try
                        {
                            string url = Extensions.Extensions.DomainName + "/Help/" + System.IO.Path.GetFileNameWithoutExtension(filePath);
                            url = url.ToLowerInvariant();

                            //we are going to parse HTML to avoid unneeded text. tags, etc
                            var web = new HtmlAgilityPack.HtmlWeb();
                            var doc = web.Load(url);

                            var nH1 = doc.DocumentNode.Descendants("h1")
                             .FirstOrDefault();

                            string title = url;
                            if (nH1 != null)
                            {
                                title = nH1.InnerText;
                            }

                            string text = string.Empty;

                            //our HTMLs have marked sections with the 'shelp' class that we will use for indexing only
                            var nodes = doc.DocumentNode.Descendants()
                             .Single(x => x.Attributes["class"] != null && !string.IsNullOrEmpty(x.Attributes["class"].Value) && x.Attributes["class"].Value.Contains("shelp"))
                             .Descendants()
                            .Where(n =>
                               n.NodeType == HtmlAgilityPack.HtmlNodeType.Text &&
                               n.ParentNode.Name != "script" &&
                               n.ParentNode.Name != "style");
                            text = string.Empty;
                            foreach (var node in nodes)
                            {
                                text += node.InnerText;
                            }

                            foreach (var oc in cToRemove)
                            {
                                text = text.Replace(oc, ' ');
                            }
                            text = text.ToLowerInvariant();
                            //words to skip indexing like: he, she, this, that...
                            string[] keywords = text.Split(cToSplit, StringSplitOptions.RemoveEmptyEntries);
                            foreach (var keyword in keywords)
                            {
                                if (!string.IsNullOrEmpty(keyword) && keyword.Length > 2 && !toSkip.Any(a => a == keyword))
                                {
                                    lastKeywrd = keyword;
                                    Models.Search item = null;

                                    if (item == null)
                                    {
                                        item = search.FirstOrDefault(i => i.UrlTitle.Any(a => a.Item1 == url));
                                        if (item == null)
                                        {
                                            item = new Models.Search() { Keywords = "," + keyword + ",", UrlTitle = new List<Tuple<string, string>>() };
                                            search.Add(item);
                                        }
                                        else
                                        {
                                            if (!item.Keywords.Contains("," + keyword + ","))
                                            {
                                                item.Keywords = item.Keywords.TrimEnd(',') + "," + keyword + ",";
                                            }
                                        }
                                    }
                                    if (!item.UrlTitle.Any(a => a.Item1 == url))
                                    {
                                        item.UrlTitle.Add(new Tuple<string, string>(url, title));
                                    }
                                }
                            }
                            search = search.GroupBy(g => g.Keywords).Select(s => s.First()).ToList();
                        }
                        catch(Exception ex)
                        {
                            //LogException
                        }
                    }

                    using (System.IO.StreamWriter sw = new StreamWriter(json, false))
                    {

                        sw.Write(SimpleJson.SimpleJson.SerializeObject(search));
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            });


            return Content("ok");
        }


Whenever you add new help document just re-run this simple crawler that will re-index everything

2. UI and Javascript parts to allow users using this simple serverless search

UI

		<div class="row">
                    <div class="col-md-12">
                        <div class="input-group">
                            <span class="input-group-addon" id="shlpSearch" style="border: 1px solid #ccc;height: 26px;padding-top: 2px;padding-bottom: 2px;">Search</span>
                            <input type="search" id="hlpSearch" class="form-control" style="max-width:100%;height: 26px;padding: 6px;" title="Search help"
                                   placeholder="How to ...">
                        </div>
                    </div>
                </div>

JS

<script>
	var shelp;
        //load our index json and avoid caching
        $.getJSON("/shelp.json?antc="+new Date().getTime(), function (data) {
            shelp = data;
        });
		
	var prevHtml = "";
        function searchHelp(sh) {
            if (sh != "" && sh.length > 2) {
                if (prevHtml == "") {
                    prevHtml = $(".shelp").html();
                }
                var items = [];
		var lessitems=[];
                var ss = sh.replace(",", " ").split(" ");
				
		$.each(shelp, function (i) {
			var br=0;
			for(var f=0;f<ss.length;f++){
				br += (shelp[i].Keywords.indexOf(ss[f]) >= 0)?1:0;
			}
			$.each(shelp[i].UrlTitle, function (ii) {
				var el = "<a class='label label-default' style='font-size:125%;line-height:2' href='" + shelp[i].UrlTitle[ii].Item1 + "'>" + shelp[i].UrlTitle[ii].Item2 + "</a>";
				//most relevant first
                                if(br==ss.length){
					if ($.inArray(el, items) === -1){
						items.unshift(el);
					}
				}
				else if(br > 0){ //less relevant but containing at least one keyword
					if ($.inArray(el, items) === -1 && $.inArray(el, lessitems) === -1){
						lessitems.push(el);
					}
				}
			});
					
		});
		if(lessitems.length>0){
                        //split less relevant by horizontal line
			items.push("<hr style='margin: 0;padding: 0;'/>");
			$.each(lessitems, function (i) {
				items.push(lessitems[i]);
			});
		}
				
		$(".shelp").css("border", "1px solid");
		$(".shelp").css("box-shadow", "0 0 8px 1px");
                $(".shelp").html(items.join("<br/>"));
            }
            else if (prevHtml != "") {
		$(".shelp").css("border", "");
		$(".shelp").css("box-shadow", "");
                $(".shelp").html(prevHtml);
                prevHtml = "";
            }
        }
		
	$(document).ready(function () {
            //attach event for searching
            $("#hlpSearch").on("keyup mouseup input search touchend", function (e) {
                searchHelp($(this).val());
            });
        });
    </script>

So, we will store previous html and insert a new generated html with search items. First items will be more relevant that contains more than 1 keyword in chain. Horizontal line will split most relevant items from less relevant items.


You can add more specific logic for example to avoid plural forms, add importance of keyword order, etc. but as simple and fastest search this will be more than enough.


Thank you and see you


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



NopCommerce customization - Full cycle of product adding in batch

Are you in need of adding a lot +40K of products, attributes, images, slugs, tags, manufacturers in batch?

Then this post should be same useful for you as for me.


In my case I did require to implement a logic for adding more than 40,000 products with attributes, images, etc. feeding data from a 3rd party API. We will add products to all enabled categories and subcategories that are shown on home page/menu.


So less water more sense :) - here is the step by step explanation:



        [AuthorizeAdmin]
        [Area(AreaNames.Admin)]
        public IActionResult GetProgress()
        {
            //to keep progress
            string progress = string.Empty;
            var storeScope = _storeContext.ActiveStoreScopeConfiguration;
            var directory = "~/wwwroot/images/uploaded/";
            
            string curl = string.Empty;
            //Use direct repo access because of cache problem with re-reading data from settings
            Core.Data.IRepository<Core.Domain.Configuration.Setting> _settingRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Configuration.Setting>>();

            try
            {
                //get list of rest of categories we should add product for
                string ddcat = _settingRepository.Table.SingleOrDefault(s=>s.Name=="DDCategories" && s.StoreId== storeScope)?.Value;
                if (string.IsNullOrEmpty(ddCat))
                {
                    //seems this is the first run
                    var nopallcats = _categoryService.GetAllCategoriesDisplayedOnHomePage(false).Where(c=>c.Published).Select(s => new Category() { ID = s.Id }).ToList();
                    List<int> finalcategories = new List<int>();
                    foreach (var c1 in nopallcats)
                    {
                        var aa1 = _categoryService.GetAllCategoriesByParentCategoryId((int)c1.ID, true);
                        if (aa1.Count > 0)
                        {
                            foreach (var c2 in aa1)
                            {
                                var aa2 = _categoryService.GetAllCategoriesByParentCategoryId(c2.Id, true);

                                if (aa2.Count > 0 && c2.Published)
                                {
                                    foreach (var c3 in aa2)
                                    {
                                        var aa3 = _categoryService.GetAllCategoriesByParentCategoryId(c3.Id, true);
                                        if (aa3.Count <= 0 && c3.Published)
                                        {
                                            finalcategories.Add(c3.Id);
                                        }
                                        else
                                        {
                                            //we have only 3 level of sub categories
                                            throw new ApplicationException("Too many subcategories");
                                        }
                                    }
                                }
                                else
                                {
                                    finalcategories.Add(c2.Id);
                                }
                            }
                        }
                        else
                        {
                            finalcategories.Add((int)c1.ID);
                        }
                    }

                    nopallcats = null;
                    //store remind categories into the settings
                    _settingService.SetSetting<string>("DDCategories", Newtonsoft.Json.JsonConvert.SerializeObject(finalcategories, Newtonsoft.Json.Formatting.None,
                        new Newtonsoft.Json.JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                        }), storeScope, true);

                    progress = string.Format("Categories: {0} --", finalcategories.Count);
                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                }
                else
                {

                    progress = "Start working on category...";
                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                    List<int> categories = Newtonsoft.Json.JsonConvert.DeserializeObject<List<int>>(ddCat);
                    int products = 0;
                    //take first category to work on
                    var rcategory = categories.FirstOrDefault();
                    //if category id > 0 then lets work on it or exit otherwise
                    if (rcategory != 0)
                    {
                        try
                        {

                            List<Price> prices = null;

                            string responseText = string.Empty;
                            string json = string.Empty;
                            System.Net.HttpWebRequest r = null;
                            System.Net.WebClient webClient = new System.Net.WebClient();
                            lock (Feeder.fileLock)
                            {
                                string pricefile = _fileProvider.GetFileName("dd_prices.json");
                                pricefile = _fileProvider.Combine(_fileProvider.MapPath(dir2), pricefile);
                                //if price file does not exists or older than 60 mins then get new
                                if (!_fileProvider.FileExists(pricefile) || DateTime.UtcNow.Subtract(_fileProvider.GetLastWriteTimeUtc(pricefile)).TotalMinutes > 60)
                                {
                                    responseText = null;
                                }
                                else
                                {

                                    responseText = _fileProvider.ReadAllText(pricefile, Encoding.UTF8);

                                    prices = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Price>>(responseText);
                                    responseText = progress = "re-read prices from file;";
                                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                }
                                if (string.IsNullOrEmpty(responseText) && prices == null)
                                {
                                    curl = Feeder.apiUrl + Feeder.getPrices;
                                    r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(curl);
                                    r.ContentType = "application/x-www-form-urlencoded";
                                    r.Method = "POST";

                                    json = "key=" + Feeder.apiKey;

                                    using (System.IO.StreamWriter sw = new System.IO.StreamWriter(r.GetRequestStream()))
                                    {
                                        sw.Write(json);
                                        sw.Flush();
                                        sw.Close();
                                    }

                                    using (var reader = new System.IO.StreamReader(r.GetResponse().GetResponseStream()))
                                    {
                                        responseText = reader.ReadToEnd();


                                        dynamic dynJson3 = Newtonsoft.Json.JsonConvert.DeserializeObject(responseText);
                                        responseText = null;
                                        prices = new List<Price>();
                                        foreach (var pr in dynJson3.prices)
                                        {
                                            //get prices from JSON and save to own JSON file
                                            dynamic price = ((Newtonsoft.Json.Linq.JToken)pr).First;
                                            prices.Add(new Price()
                                            {
                                                code = price.sku,
                                                id = price.id,
                                                price = price.price,
                                                RPrice = price.RPrice,
                                                delivery = price.delivery,
                                                deliveryText = price.deliveryText
                                            });
                                        }

                                        _fileProvider.WriteAllText(pricefile, Newtonsoft.Json.JsonConvert.SerializeObject(prices, Newtonsoft.Json.Formatting.None,
                                                new Newtonsoft.Json.JsonSerializerSettings()
                                                {
                                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                                }), Encoding.UTF8);


                                        progress = "prices re-downloaded again;";
                                        _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                    }
                                }
                            }

                            
                            responseText = null;

                            var dateranges = _dateRangeService.GetAllDeliveryDates();

                            ProdTemp pt = null;
                            var category = rcategory;
                            int count = 0;
                            int offset = 0, limit = 100, prodProcessed = 0;
                            var ccc = _categoryService.GetCategoryById(category);

                            string temprod = _settingRepository.Table.SingleOrDefault(s => s.Name == "DDProdcts" && s.StoreId == storeScope)?.Value;
                            //create new or re-read last offset object
                            if (string.IsNullOrEmpty(temprod))
                            {
                                pt = new ProdTemp();
                                pt.StoreID = storeScope;
                            }
                            else
                            {
                                pt = Newtonsoft.Json.JsonConvert.DeserializeObject<ProdTemp>(temprod);
                                limit = pt.Limit;
                                offset = pt.Offset;
                                count = pt.TotalProducts;
                                prodProcessed = pt.ProcessedProducts;
                                storeScope = pt.StoreID.GetValueOrDefault();
                            }


                            curl = Feeder.apiUrl + Feeder.getItems;
                            r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(curl);
                            r.ContentType = "application/x-www-form-urlencoded";
                            r.Method = "POST";

                            //get 3rd party category id to get products for it
                            var ccatid = _genericAttributeService.GetAttribute<int>(ccc, Feeder.gAttrID, storeScope);

                            json = "key=" + Feeder.apiKey + "&limit=" + limit + "&offset=" + offset + "&modifiedDT=" +
                                DateTime.UtcNow.Date.AddMonths(-6).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) +
                                "&brand=" + "&category=" + ccatid;
                            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(r.GetRequestStream()))
                            {
                                sw.Write(json);
                                sw.Flush();
                                sw.Close();
                            }
                            responseText = string.Empty;
                            using (var reader = new System.IO.StreamReader(r.GetResponse().GetResponseStream()))
                            {
                                responseText = reader.ReadToEnd();
                            }

                            dynamic dynJson2 = Newtonsoft.Json.JsonConvert.DeserializeObject(responseText);

                            responseText = null;
                            string swm = string.Empty;

                            count = int.Parse((string)dynJson2.count);

                            progress = string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, count, prodProcessed);
                            _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));

                            Core.Domain.Catalog.Product pro = null;
                            dynamic product;
                            Price pprice;

                            var manufRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.Manufacturer>>();
                            var productRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.Product>>();
                            var productTagRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductTag>>();
                            var prodAttrRepo= Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttribute>>();
                            var spAttrRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.SpecificationAttribute>>();
                            var spOAttrRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.SpecificationAttributeOption>>();

                            var prodAttrMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttributeMapping>>();
                            var prodAttrValRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttributeValue>>();
                            var prodTagMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductProductTagMapping>>();
                            var prodCatMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductCategory>>();
                            var prodSAttrMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductSpecificationAttribute>>();

                            string entityName = typeof(Core.Domain.Catalog.Product).Name;
                            var urlRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Seo.UrlRecord>>();
                            //stopwatch to get timing
                            System.Diagnostics.Stopwatch swc = new System.Diagnostics.Stopwatch();
                            double totalTimeSec = 0;
                            foreach (var prodd in dynJson2.items)
                            {

                                totalTimeSec = 0;
                                swm = string.Empty;

                                _cache.Set<string>("Progress", string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, count, prodProcessed), TimeSpan.FromMinutes(30));

                                product = ((Newtonsoft.Json.Linq.JToken)prodd).First;

                                int pid = int.Parse((string)Newtonsoft.Json.Linq.JProperty.FromObject(prodd).Name);

                                progress = "pprice | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                pprice = prices.SingleOrDefault(p => p.id == pid);

                                if (pprice == null)
                                {
                                    pprice = prices.SingleOrDefault(p => p.code == pid.ToString("000000") + "-02");
                                }
                                string pname = product.name;

                                swc.Start();

                                pro = null;
                                //different products may have same SKUs so take care of it
                                pro = _productService.GetProductsBySku(new string[] { (string)product.article }).SingleOrDefault(sk=> sk.Name== pname && sk.ProductCategories.SingleOrDefault(pc=>pc.CategoryId==category)!=null);

                                swc.Stop();
                                totalTimeSec += swc.Elapsed.TotalSeconds;
                                swm = "GetProductBySku: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                _cache.Set<string>("Progress", progress + swm, TimeSpan.FromMinutes(30));
                                swc.Reset();
                                //we add products if price exists only
                                if (pprice != null && pro == null)
                                {
                                    decimal cost = pprice.price;
                                    decimal price = Feeder.CalculatePrice(cost, pprice.RPrice);

                                    if(price<cost)
                                    {
                                        price = pprice.RPrice;
                                    }
                                    //define max price for category to filter by later
                                    if (pt.MaxPrice < price)
                                        pt.MaxPrice = price;

                                    //manufacturer
                                    swc.Start();
                                    int manuid = product.brand;
                                    progress = "manufs | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    string bname = (string)product.brandName;
                                    var manuf = manufRepository.Table.FirstOrDefault(m => m.Name == bname);
                                    if (manuf == null)
                                    {
                                        manuf = new Core.Domain.Catalog.Manufacturer()
                                        {
                                            Name = product.brandName,
                                            CreatedOnUtc = DateTime.UtcNow,
                                            ManufacturerTemplateId = 1,
                                            MetaDescription = product.brandName,
                                            MetaKeywords = product.brandName,
                                            MetaTitle = product.brandName,
                                            PageSize = 20,
                                            Published = true,
                                            UpdatedOnUtc = DateTime.UtcNow
                                        };
                                        _manufacturerService.InsertManufacturer(manuf);
                                        _storeMappingService.InsertStoreMapping<Core.Domain.Catalog.Manufacturer>(manuf, storeScope);
                                        //store 3rd party manufacturer id to use later
                                        _genericAttributeService.SaveAttribute<int>(manuf, Feeder.gAttrID, manuid);
                                        entityName = typeof(Core.Domain.Catalog.Manufacturer).Name;
                                        //add slug if not exists
                                        if (urlRepository.TableNoTracking.SingleOrDefault(u => u.EntityId == manuf.Id && u.EntityName == entityName && u.LanguageId == 0) == null)
                                        {
                                            var slug1 = _urlRecordService.ValidateSeName(manuf, manuf.Name, manuf.Name, true);
                                            _urlRecordService.InsertUrlRecord(new Core.Domain.Seo.UrlRecord
                                            {
                                                EntityId = manuf.Id,
                                                EntityName = entityName,
                                                LanguageId = 0,
                                                IsActive = true,
                                                Slug = slug1
                                            });
                                        }

                                    }
                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", manufs: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //date ranges
                                    swc.Start();
                                    Core.Domain.Shipping.DeliveryDate daterange;
                                    progress = "daterange | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    {
                                        string dt = (string)pprice.deliveryText;
                                        daterange = dateranges.FirstOrDefault(d => d.Name == dt);
                                        if (daterange == null)
                                        {
                                            daterange = new Core.Domain.Shipping.DeliveryDate()
                                            {
                                                Name = pprice.deliveryText
                                            };
                                            _dateRangeService.InsertDeliveryDate(daterange);
                                            dateranges.Add(daterange);
                                        }
                                    }

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", daterange: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //product
                                    swc.Start();
                                    {
                                        //some formatting and length limitation
                                        string shortd = product.descriptionSnort;
                                        if (shortd.Length > 400)
                                        {
                                            shortd = shortd.Substring(0, 399);
                                        }
                                        
                                        if (pname.Length > 400)
                                        {
                                            pname = pname.Substring(0, 399);
                                        }

                                        pro = new Core.Domain.Catalog.Product()
                                        {
                                            ProductType = Core.Domain.Catalog.ProductType.SimpleProduct,
                                            VisibleIndividually = true,
                                            Sku = product.article,
                                            IsShipEnabled = true,
                                            MetaDescription = shortd,
                                            MetaKeywords = shortd,
                                            MetaTitle = pname,
                                            Price = price,
                                            ProductCost = cost,
                                            AdditionalShippingCharge = Feeder.CalculateShipping(price),
                                            CreatedOnUtc = DateTime.UtcNow,
                                            UpdatedOnUtc = ((DateTime)product.modifiedDT).ToUniversalTime(),
                                            Name = pname,
                                            ShortDescription = product.descriptionSnort,
                                            FullDescription = product.description,
                                            BackorderMode = Core.Domain.Catalog.BackorderMode.NoBackorders,
                                            MarkAsNew = true,
                                            AllowBackInStockSubscriptions = true,
                                            AllowCustomerReviews = true,
                                            Published = true,
                                            DeliveryDateId = daterange == null ? 0 : daterange.Id,
                                            LimitedToStores = true, OrderMaximumQuantity=int.MaxValue
                                           ,OrderMinimumQuantity=1, DisableBuyButton = true, ProductTemplateId = 1, ProductManufacturers =
                                        {
                                            new Core.Domain.Catalog.ProductManufacturer() { Manufacturer = manuf }
                                        }
                                            

                                        };
                                        if (!string.IsNullOrEmpty(pro.FullDescription))
                                            pro.FullDescription = pro.FullDescription.Replace("font-size: 1rem;", string.Empty);

                                    }
                                    {
                                        pro.ProductCategories.Add(new Core.Domain.Catalog.ProductCategory()
                                        {
                                            CategoryId = category
                                        });
                                    }

                                    _productService.InsertProduct(pro);

                                    _genericAttributeService.SaveAttribute<int>(pro, Feeder.gAttrID, pid, storeScope);
                                    _genericAttributeService.SaveAttribute<string>(pro, Feeder.gAttrCode, pprice.code, storeScope);

                                    products++;

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", product: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //tags
                                    swc.Start();
                                    progress = "ttag | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    foreach (var tt in pro.Name.Split(' ', StringSplitOptions.RemoveEmptyEntries))
                                    {
                                        string tag = tt.ToUpperInvariant().Trim().Trim(Feeder.trimChars);
                                        if (!string.IsNullOrEmpty(tag))
                                        {
                                            if (manufRepository.Table.Any(m => m.Name == tag))
                                                continue;

                                            var ttag = productTagRepository.Table.FirstOrDefault(t => t.Name == tag);
                                            if (ttag == null)
                                            {
                                                ttag = new Core.Domain.Catalog.ProductTag() { Name = tag };

                                                _productTagService.InsertProductTag(ttag);

                                                pro.ProductProductTagMappings.Add(new Core.Domain.Catalog.ProductProductTagMapping()
                                                {
                                                    ProductTag = ttag,
                                                });


                                                entityName = typeof(Core.Domain.Catalog.ProductTag).Name;

                                                    if (urlRepository.TableNoTracking.SingleOrDefault(u => u.EntityId == ttag.Id && u.EntityName == entityName && u.LanguageId == 0) == null)
                                                    {
                                                        var slug2 = _urlRecordService.ValidateSeName(ttag, ttag.Name, ttag.Name, true);
                                                        _urlRecordService.InsertUrlRecord(new Core.Domain.Seo.UrlRecord
                                                        {
                                                            EntityId = ttag.Id,
                                                            EntityName = entityName,
                                                            LanguageId = 0,
                                                            IsActive = true,
                                                            Slug = slug2
                                                        });
                                                    }
                                                
                                            }
                                            else if (!prodTagMapRepo.Table.Any(a => a.ProductTagId == ttag.Id && a.ProductId == pro.Id))//!pro.ProductProductTagMappings.Any(a => a.ProductTag.Id == ttag.Id))
                                            {
                                                if (!pro.ProductProductTagMappings.Any(a => a.ProductTag.Id == ttag.Id))
                                                {
                                                    pro.ProductProductTagMappings.Add(new Core.Domain.Catalog.ProductProductTagMapping()
                                                    {
                                                        ProductTag = ttag,
                                                    });
                                                }
                                            }
                                        }
                                    }

                                    _productService.UpdateProduct(pro);

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", ttag: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    //pictures
                                    swc.Start();
                                    AddPictures(product, pro);
                                    _productService.UpdateProduct(pro);


                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", pictures: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    bool colDef = true;

                                    //attributes
                                    swc.Start();
                                    Core.Domain.Catalog.ProductAttribute pattr = null;
                                    Core.Domain.Catalog.SpecificationAttribute spattr = null;
                                    Core.Domain.Catalog.SpecificationAttributeOption spao = null;
                                    List<Core.Domain.Catalog.ProductSpecificationAttribute> prodSAttrMapRepo_later = new List<Core.Domain.Catalog.ProductSpecificationAttribute>();

                                    dynamic chr;
                                    Newtonsoft.Json.Linq.JProperty cjp;
                                    string val, name, rgb;
                                    System.Drawing.Color c;
                                    progress = "pattr | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    string nlo;
                                    string spaovu;
                                    string spaonu;
                                    foreach (var ch in product.characteristics)
                                    {

                                        chr = ((Newtonsoft.Json.Linq.JToken)ch).First;
                                        cjp = Newtonsoft.Json.Linq.JProperty.FromObject(ch) as Newtonsoft.Json.Linq.JProperty;
                                        val = ((Newtonsoft.Json.Linq.JValue)chr).Value as string;
                                        name = cjp.Name.Trim().Trim(Feeder.trimChars);
                                        if (name.IndexOf('(') >= 0 && name.IndexOf(')') < 0)
                                        {
                                            name += ")";
                                        }
                                        if (name.IndexOf('[') >= 0 && name.IndexOf(']') < 0)
                                        {
                                            name += "]";
                                        }
                                        if (name.IndexOf('{') >= 0 && name.IndexOf('}') < 0)
                                        {
                                            name += "}";
                                        }
                                        nlo = name.ToLowerInvariant();
                                        spaovu = val.ToUpperInvariant();
                                        spaonu = name.ToUpperInvariant();

                                        //work on colors
                                        if (nlo == "цвет" || nlo == "color" || nlo == "колір" || (nlo.Contains("цвет ") || nlo.Contains("color ") || nlo.Contains("колір ")))
                                        {
                                            progress = "pattr | " + string.Format("Categories: {0}, Products: {1}/{2}", categories.Count, (string)dynJson2.count, prodProcessed);
                                            pattr = prodAttrRepo.Table.FirstOrDefault(a => a.Name == name);
                                            if (pattr == null)
                                            {
                                                pattr = new Core.Domain.Catalog.ProductAttribute()
                                                {
                                                    Name = name

                                                };

                                                _productAttributeService.InsertProductAttribute(pattr);

                                            }
                                            string[] colors = spaovu.Split(',', StringSplitOptions.RemoveEmptyEntries);

                                            foreach (var sc in colors)
                                            {
                                                rgb = "#ffffff";
                                                var ssc = sc.Replace(" ", string.Empty).Replace("-", string.Empty).Replace("/", string.Empty).Replace("+", string.Empty).ToLowerInvariant();
                                                var sccc = Feeder.colors.SingleOrDefault(s => s.Item2.Contains(ssc));
                                                if (sccc != null)
                                                    rgb = sccc.Item1;
                                                else
                                                {
                                                    c = System.Drawing.Color.FromName(val);

                                                    rgb = "#" + c.R.ToString("X2") + c.G.ToString("X2") + c.B.ToString("X2");
                                                }

                                                if (!prodAttrMapRepo.Table.Any(a => a.ProductAttributeId == pattr.Id && a.ProductId == pro.Id))
                                                    if (!pro.ProductAttributeMappings.Any(a => a.ProductAttribute.Id == pattr.Id
                                                        && a.ProductAttributeValues.Any(aa => aa.Name.ToUpper() == spaovu)))
                                                    {

                                                        pro.ProductAttributeMappings.Add(new Core.Domain.Catalog.ProductAttributeMapping()
                                                        {
                                                            AttributeControlType = Core.Domain.Catalog.AttributeControlType.ColorSquares,
                                                            IsRequired = colors.Length>1,

                                                            ProductAttribute = pattr,
                                                            ProductAttributeValues =
                                            {
                                                new Core.Domain.Catalog.ProductAttributeValue
                                                {
                                                    AttributeValueType = Core.Domain.Catalog.AttributeValueType.Simple,
                                                    Name = sc, IsPreSelected=colDef, ColorSquaresRgb=rgb
                                                }
                                            }

                                                        });
                                                        colDef = false;
                                                    }
                                            }
                                        }
                                        else //work on specification attributes
                                        {
                                            progress = "spattr | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                            _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                            spattr = spAttrRepository.Table.FirstOrDefault(a => a.Name == name);
                                            spao = null;
                                            if (spattr == null)
                                            {
                                                spattr = new Core.Domain.Catalog.SpecificationAttribute
                                                {
                                                    Name = name
                                                };
                                                _specificationAttributeService.InsertSpecificationAttribute(spattr);

                                                spao = new Core.Domain.Catalog.SpecificationAttributeOption()
                                                {
                                                    Name = val,
                                                    SpecificationAttribute = spattr
                                                };
                                                _specificationAttributeService.InsertSpecificationAttributeOption(spao);
                                            }
                                            else
                                            {
                                                progress = "spao | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                                _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));

                                                spao = spOAttrRepository.Table.FirstOrDefault(s => s.SpecificationAttributeId == spattr.Id && s.Name == val);
                                                if (spao == null)
                                                {
                                                    progress = "!!!" + progress;
                                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                                    
                                                    {
                                                        spao = new Core.Domain.Catalog.SpecificationAttributeOption()
                                                        {
                                                            SpecificationAttribute = spattr,
                                                            Name = val

                                                        };
                                                        _specificationAttributeService.InsertSpecificationAttributeOption(spao);
                                                    }

                                                   
                                                }
                                            }

                                            if (!prodSAttrMapRepo.Table.Any(a => a.SpecificationAttributeOptionId == spao.Id &&
                                             a.ProductId == pro.Id))
                                                if (!pro.ProductSpecificationAttributes.Any(a => a.SpecificationAttributeOptionId == spao.Id
                                                     && a.SpecificationAttributeOption.SpecificationAttributeId == spattr.Id))
                                                {
                                                    pro.ProductSpecificationAttributes.Add(new Core.Domain.Catalog.ProductSpecificationAttribute()
                                                    {
                                                        //disable filtering by SKU or whatever you need
                                                        AllowFiltering = !excludeParams.Any(a => nlo.ToUpper().Contains(a.ToUpper())),
                                                        ShowOnProductPage = nlo.ToUpper() != excludeParams[0].ToUpper() && nlo.ToUpper() != excludeParams[1].ToUpper(),
                                                        SpecificationAttributeOption = spao,
                                                        AttributeType = Core.Domain.Catalog.SpecificationAttributeType.Option
                                                    });
                                            }
                                        }
                                        _productService.UpdateProduct(pro);
                                    }

                                    entityName = typeof(Core.Domain.Catalog.Product).Name;
                                    var slugp = _urlRecordService.ValidateSeName(pro, pro.Name, pro.Name, true);
                                    urlRepository.Insert(new Core.Domain.Seo.UrlRecord
                                    {
                                        EntityId = pro.Id,
                                        EntityName = entityName,
                                        LanguageId = 0,
                                        IsActive = true,
                                        Slug = slugp
                                    });

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", pattr: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", totalTimeSec.ToString("0.###") + ":" + progress + " " +  swm, TimeSpan.FromMinutes(30));

                                }
                                else if (pro != null)
                                {
                                    //do whatever you need if product already exists
                                }

                                prodProcessed++;
                                offset++;

                                pt.Limit = limit;
                                pt.Offset = offset;
                                pt.ProcessedProducts = prodProcessed;
                                pt.TotalProducts = count;

                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                            }

                            int icount = dynJson2.items is Newtonsoft.Json.Linq.JArray ? ((Newtonsoft.Json.Linq.JArray)dynJson2.items).Count : ((Newtonsoft.Json.Linq.JObject)dynJson2.items).Count;
                            //check if we have done with that category
                            if (dynJson2 == null || icount < 1 || icount < limit)
                            {
                                if (pt.MaxPrice > 0)
                                {
                                    var cat = _categoryService.GetCategoryById(rcategory);
                                    if (pt.MaxPrice > 50000)
                                    {
                                        cat.PriceRanges = "0-9999;10000-24999;25000-49999;50000-";
                                    }
                                    else if (pt.MaxPrice > 20000)
                                    {
                                        cat.PriceRanges = "0-4999;5000-9999;10000-24999;25000-";
                                    }
                                    else if (pt.MaxPrice > 5000)
                                    {
                                        cat.PriceRanges = "0-4999;5000-9999;10000-";
                                    }
                                    else if (pt.MaxPrice > 1000)
                                    {
                                        cat.PriceRanges = "0-999;1000-3999;4000-";
                                    }
                                    else
                                    {
                                        cat.PriceRanges = "0-249;250-499;500-749;750-";
                                    }
                                    _categoryService.UpdateCategory(cat);
                                }
                                //remove category that we have finished
                                categories.Remove(rcategory);
                                // store offset and everything for the next run (I was re-runing it by an ajax calls)
                                pt.Offset = 0;
                                pt.MaxPrice = 0;
                                pt.TotalProducts = 0;
                                pt.ProcessedProducts = 0;
                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                                progress = string.Format("Categories: {0}, Products: {1}/{2} 'tbc", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                            }
                            else // seems there are more products so we will continue on the same category
                            {
                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                                progress = string.Format("Categories: {0}, Products: {1}/{2} 'tbc", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                            }
                        }
                        catch (System.Net.WebException wex)
                        {
                            //LogException
                        }
                        catch (Exception ex)
                        {
                            //LogException
                        }
                    }

                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                    //seems we have more categories to work on
                    if (categories != null && categories.Count > 0)
                    {
                        _settingService.SetSetting<string>("DDCategories", Newtonsoft.Json.JsonConvert.SerializeObject(categories, Newtonsoft.Json.Formatting.None,
                        new Newtonsoft.Json.JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                        }), storeScope, true);
                    }
                    else //seems we have done with all categories
                    {
                        progress = "Done";
                        _settingService.SetSetting<string>("DDProdcts", string.Empty, storeScope, true);
                        _settingService.SetSetting<string>("DDCategories", string.Empty, storeScope, true);
                    }
                }

            }
            catch(Exception ex)
            {
                //LogException
            }

            return Json(new { data = progress });
        }

and the add pictures method

        private void AddPictures(dynamic product, Core.Domain.Catalog.Product pro)
        {
            List<Tuple<string, byte[]>> bdata = new List<Tuple<string, byte[]>>();
            var iurls = ((Newtonsoft.Json.Linq.JArray)product.imageFiles).Select(s => (string)s).ToList();

            System.Threading.Tasks.Parallel.ForEach(iurls, iu =>
            {
                try
                {
                    
                    if (!bdata.Any(a => a.Item1 == iu))
                    {
                        System.Net.WebClient webClient = new System.Net.WebClient();
                        bdata.Add(new Tuple<string, byte[]>(iu, webClient.DownloadData(iu)));
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            }
            );
            
            foreach (var iu in iurls)
            {
                try
                {
                    var data = bdata.SingleOrDefault(s => s.Item1 == iu);

                    if (data != null)
                    {
                        string iufn = _fileProvider.GetFileName(iu);
                        string iufnl = iufn.ToLowerInvariant();
                        {
                            {
                                {
                                    
                                    _pictureService.StoreInDb = false;
                                    Core.Domain.Media.Picture propic = _pictureService.InsertPicture(data.Item2, "image/jpeg", iufn, pro.Name, pro.Name, true);

                                    if (propic != null)
                                    {
                                        pro.ProductPictures.Add(new Core.Domain.Catalog.ProductPicture()
                                        {
                                            Picture = propic
                                        });
                                    }
                                }

                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            }
            bdata.Clear();
        }


This is not really 100% optimized, especially the DB adding/updating part because of using _services instead of _repositories

but seem I had problems using _repos everywhere and not sure why.


Thank you and see you soon 


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Get stack trace of frozen processes especially for asp.net application pools

There are so many times you need to get a place where your frozen process stuck aren't?

Especially if this is a production server or remote environment. So what to do?


I have faced this problem myself and created a small tool that gets stack trace from process by its name.

We will need the following instruments:

- Microsoft.Diagnostics.Runtime.dll

- System.Management.dll


And some code to find a process and get its stack trace.

        private string CatchStacktrace(string domainName)
        {
            int pid = -1;
            var procs = System.Diagnostics.Process.GetProcessesByName("w3wp");

            var proc = procs.FirstOrDefault(p => GetProcessOwner(p.Id).StartsWith(domainName));
            if (proc == null)
                return string.Format("User Name not found: {0}", domainName);

            pid = proc.Id;

            if (pid < 1)
                return string.Format("Process w3wp.exe not found: {0}", domainName);

            StringBuilder sb = new StringBuilder();
            sb.AppendLine(domainName);
            sb.AppendLine(string.Empty);

            using (var tdata = DataTarget.AttachToProcess(pid, 3000))
            {
                // Dump CLR info
                var clrVersion = tdata.ClrVersions.First();
                var dacInfo = clrVersion.DacInfo;

                sb.AppendLine("# CLR Info");
                sb.AppendLine(string.Format("Version:       {0}", clrVersion.Version));
                sb.AppendLine(string.Format("Filesize:      {0:X}", dacInfo.FileSize));
                sb.AppendLine(string.Format("Timestamp:     {0:X}", dacInfo.TimeStamp));
                sb.AppendLine(string.Format("Dac file:      {0}", dacInfo.FileName));

                sb.AppendLine(string.Empty);

                var runtime = clrVersion.CreateRuntime();
                var appDomain = runtime.AppDomains.First();

                sb.AppendLine(string.Format("# Runtime Info"));
                sb.AppendLine(string.Format("AppDomain:     {0}", appDomain.Name));
                sb.AppendLine(string.Format("Address:       {0}", appDomain.Address));
                sb.AppendLine(string.Format("Configuration: {0}", appDomain.ConfigurationFile));
                sb.AppendLine(string.Format("Directory:     {0}", appDomain.ApplicationBase));

                sb.AppendLine(string.Empty);

                // Dump thread info
                sb.AppendLine("## Threads");
                sb.AppendLine(string.Format("Thread count:  {0}", runtime.Threads.Count));
                sb.AppendLine(string.Empty);
                foreach (var thread in runtime.Threads)
                {
                    sb.AppendLine(string.Format("### Thread     {0}", thread.OSThreadId));
                    sb.AppendLine(string.Format("Thread type:   {0}",
                                            thread.IsBackground ? "Background"
                                          : thread.IsGC ? "GC"
                                          : "Foreground"));
                    var blocks = thread.BlockingObjects;
                    if (blocks != null)
                    {
                        foreach(var block in blocks)
                        {
                            sb.AppendLine(string.Format("Blocked by:  {0}   reason: {1}", block.ToString(), block.Reason.ToString()));
                        }
                    }

                    sb.AppendLine(string.Empty);
                    sb.AppendLine("Stack trace:");
                    foreach (var stackFrame in thread.EnumerateStackTrace())
                    {
                        sb.AppendLine(string.Format("* {0}", stackFrame.DisplayString));
                    }

                    sb.AppendLine(string.Empty);
                    sb.AppendLine(string.Empty);
                }
            }

            return sb.ToString();
        }

        string[] argList = new string[] { string.Empty, string.Empty };
        ManagementObjectCollection processList = null;
        ManagementObjectSearcher searcher = null;
        string query = "Select * From Win32_Process Where ProcessID = ";
        public string GetProcessOwner(int processId)
        {
            searcher = new ManagementObjectSearcher(query + processId);
            processList = searcher.Get();

            foreach (ManagementObject obj in processList)
            {
                argList[0] = string.Empty;
                argList[1] = string.Empty;
                int returnVal = Convert.ToInt32(obj.InvokeMethod("GetOwner", argList));
                if (returnVal == 0)
                {
                    return argList[0];
                }
            }

            return "NO OWNER";
        }


As you know asp.net applications all have w3wp process name and run under its own user name usually with the same name as website name. So to find w3wp process we will pass its username like "ok.unsode.com" to the CatchStacktrace method.

If appropriate w3wp process has been found then you will get something like that:



ok.unsode.com


# CLR Info

Version:       v4.7.2650.00

Filesize:      9E8000

Timestamp:     5AB1C520

Dac file:      mscordacwks_Amd64_Amd64_4.7.2650.00.dll


# Runtime Info

AppDomain:     DefaultDomain

Address:       2017397101184

Configuration: w3wp.exe.config

Directory:     c:\windows\system32\inetsrv\


## Threads

Thread count:  85


### Thread     8824

Thread type:   Background


Stack trace:



### Thread     7484

Thread type:   Background


Stack trace:

* DebuggerU2MCatchHandlerFrame



### Thread     9036

Thread type:   Background


Stack trace:



### Thread     7744

Thread type:   Background


Stack trace:



### Thread     13420

Thread type:   Background


Stack trace:



### Thread     6336

Thread type:   Background


Stack trace:

* System.Collections.Generic.Dictionary`2[[System.__Canon, mscorlib],[System.__Canon, mscorlib]].Insert(System.__Canon, System.__Canon, Boolean)

* MrCMS.Indexing.Management.GetLuceneIndexSearcher.Get(System.String)

* MrCMS.Indexing.Querying.Searcher`2[[System.__Canon, mscorlib],[System.__Canon, mscorlib]].Search(Lucene.Net.Search.Query, Int32, System.Nullable`1<Int32>, Lucene.Net.Search.Filter, Lucene.Net.Search.Sort)

* MrCMS.Web.Apps.Ecommerce.Services.Products.ProductSearchIndexService.SearchProducts(MrCMS.Web.Apps.Ecommerce.Models.ProductSearchQuery)

* MrCMS.Web.Apps.Ecommerce.Controllers.ProductSearchController.ProductsIndexSearch()

* DynamicClass.lambda_method(System.Runtime.CompilerServices.Closure, System.Web.Mvc.ControllerBase, System.Object[])

* System.Web.Mvc.ReflectedActionDescriptor.Execute(System.Web.Mvc.ControllerContext, System.Collections.Generic.IDictionary`2<System.String,System.Object>)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionDescriptor, System.Collections.Generic.IDictionary`2<System.String,System.Object>)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(System.IAsyncResult, ActionInvocation)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncResult`2[[System.__Canon, mscorlib],[System.Web.Mvc.Async.AsyncControllerActionInvoker+ActionInvocation, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker+<>c__DisplayClass21+<>c__DisplayClass2b.<BeginInvokeAction>b__1c()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+<>c__DisplayClass21.<BeginInvokeAction>b__1e(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(System.IAsyncResult)

* System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(System.IAsyncResult, ExecuteCoreState)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.Web.Mvc.Controller+ExecuteCoreState, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecuteCore(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.__Canon, mscorlib]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecute(System.IAsyncResult)

* System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(System.IAsyncResult, ProcessRequestState)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.Web.Mvc.MvcHandler+ProcessRequestState, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.MvcHandler.EndProcessRequest(System.IAsyncResult)

* System.Web.Mvc.HttpHandlerUtil+ServerExecuteHttpHandlerWrapper+<>c__DisplayClass4.<Wrap>b__3()

* System.Web.Mvc.HttpHandlerUtil+ServerExecuteHttpHandlerWrapper.Wrap[[System.__Canon, mscorlib]](System.Func`1<System.__Canon>)

* System.Web.HttpServerUtility.ExecuteInternal(System.Web.IHttpHandler, System.IO.TextWriter, Boolean, Boolean, System.Web.VirtualPath, System.Web.VirtualPath, System.String, System.Exception, System.String)

* System.Web.HttpServerUtility.Execute(System.Web.IHttpHandler, System.IO.TextWriter, Boolean, Boolean)

* System.Web.HttpServerUtility.Execute(System.Web.IHttpHandler, System.IO.TextWriter, Boolean)

* System.Web.Mvc.Html.ChildActionExtensions.ActionHelper(System.Web.Mvc.HtmlHelper, System.String, System.String, System.Web.Routing.RouteValueDictionary, System.IO.TextWriter)

* System.Web.Mvc.Html.ChildActionExtensions.RenderAction(System.Web.Mvc.HtmlHelper, System.String, System.String)

* ASP._Page_Apps_Core_Views_Shared__BaseLayout_cshtml.Execute()

* System.Web.WebPages.WebPageBase.ExecutePageHierarchy()

* System.Web.Mvc.WebViewPage.ExecutePageHierarchy()

* System.Web.WebPages.WebPageBase.ExecutePageHierarchy(System.Web.WebPages.WebPageContext, System.IO.TextWriter, System.Web.WebPages.WebPageRenderingBase)

* System.Web.WebPages.WebPageBase+<>c__DisplayClass3.<RenderPageCore>b__2(System.IO.TextWriter)

* System.Web.WebPages.WebPageBase.Write(System.Web.WebPages.HelperResult)

* System.Web.WebPages.WebPageBase.RenderSurrounding(System.String, System.Action`1<System.IO.TextWriter>)

* System.Web.WebPages.WebPageBase.PopContext()

* System.Web.Mvc.ViewResultBase.ExecuteResult(System.Web.Mvc.ControllerContext)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, Int32, System.Web.Mvc.ResultExecutingContext, System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionResult)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(System.Web.Mvc.ControllerContext, System.Collections.Generic.IList`1<System.Web.Mvc.IResultFilter>, System.Web.Mvc.ActionResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker+<>c__DisplayClass21.<BeginInvokeAction>b__1e(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(System.IAsyncResult)

* System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(System.IAsyncResult, ExecuteCoreState)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.Web.Mvc.Controller+ExecuteCoreState, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecuteCore(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.__Canon, mscorlib]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecute(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncResultBase`1[[System.Web.Mvc.Async.AsyncVoid, System.Web.Mvc]].Begin(System.AsyncCallback, System.Object, Int32)

* System.Web.Mvc.Controller.BeginExecute(System.Web.Routing.RequestContext, System.AsyncCallback, System.Object)

* MrCMS.Website.Routing.MrCMSStandardRouteHandler.Handle(System.Web.Routing.RequestContext)

* MrCMS.Website.Routing.MrCMSHttpHandler.ProcessRequest(System.Web.Routing.RequestContext)

* System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

* System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep)

* System.Web.HttpApplication.ExecuteStep(IExecutionStep, Boolean ByRef)

* System.Web.HttpApplication+PipelineStepManager.ResumeSteps(System.Exception)

* System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext, System.AsyncCallback)

* System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest, System.Web.HttpContext)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)

* DomainNeutralILStubClass.IL_STUB_ReversePInvoke(Int64, Int64, Int64, Int32)

* InlinedCallFrame

* InlinedCallFrame

* DomainNeutralILStubClass.IL_STUB_PInvoke(IntPtr, System.Web.RequestNotificationStatus ByRef)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)

* DomainNeutralILStubClass.IL_STUB_ReversePInvoke(Int64, Int64, Int64, Int32)

* ContextTransitionFrame



### Thread     9976

Thread type:   Background


Stack trace:



### Thread     11760

Thread type:   Background


Stack trace:



### Thread     6112

Thread type:   Background


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     16772

Thread type:   Background


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     8280

Thread type:   Background


Stack trace:



### Thread     12476

Thread type:   Background


Stack trace:

* System.Collections.Generic.Dictionary`2[[System.__Canon, mscorlib],[System.__Canon, mscorlib]].Remove(System.__Canon)

* MrCMS.Indexing.Management.GetLuceneIndexSearcher.Reset(System.String)

* MrCMS.Indexing.Management.IndexManager`2[[System.__Canon, mscorlib],[System.__Canon, mscorlib]].<Optimise>b__35_0()

* MrCMS.Indexing.Management.IndexResult.GetResult(System.Action)

* MrCMS.Tasks.OptimiseIndexesController.Execute(System.String)

* DynamicClass.lambda_method(System.Runtime.CompilerServices.Closure, System.Web.Mvc.ControllerBase, System.Object[])

* System.Web.Mvc.ReflectedActionDescriptor.Execute(System.Web.Mvc.ControllerContext, System.Collections.Generic.IDictionary`2<System.String,System.Object>)

* System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(System.Web.Mvc.ControllerContext, System.Web.Mvc.ActionDescriptor, System.Collections.Generic.IDictionary`2<System.String,System.Object>)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(System.IAsyncResult, ActionInvocation)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncResult`2[[System.__Canon, mscorlib],[System.Web.Mvc.Async.AsyncControllerActionInvoker+ActionInvocation, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+AsyncInvocationWithFilters+<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker+<>c__DisplayClass21+<>c__DisplayClass2b.<BeginInvokeAction>b__1c()

* System.Web.Mvc.Async.AsyncControllerActionInvoker+<>c__DisplayClass21.<BeginInvokeAction>b__1e(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(System.IAsyncResult)

* System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(System.IAsyncResult, ExecuteCoreState)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.Web.Mvc.Controller+ExecuteCoreState, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecuteCore(System.IAsyncResult)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.__Canon, mscorlib]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.Controller.EndExecute(System.IAsyncResult)

* System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(System.IAsyncResult, ProcessRequestState)

* System.Web.Mvc.Async.AsyncResultWrapper+WrappedAsyncVoid`1[[System.Web.Mvc.MvcHandler+ProcessRequestState, System.Web.Mvc]].CallEndDelegate(System.IAsyncResult)

* System.Web.Mvc.MvcHandler.EndProcessRequest(System.IAsyncResult)

* System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

* System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep)

* System.Web.HttpApplication.ExecuteStep(IExecutionStep, Boolean ByRef)

* System.Web.HttpApplication+PipelineStepManager.ResumeSteps(System.Exception)

* System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext, System.AsyncCallback)

* System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest, System.Web.HttpContext)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)

* DomainNeutralILStubClass.IL_STUB_ReversePInvoke(Int64, Int64, Int64, Int32)

* InlinedCallFrame

* InlinedCallFrame

* DomainNeutralILStubClass.IL_STUB_PInvoke(IntPtr, System.Web.RequestNotificationStatus ByRef)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)

* System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)

* DomainNeutralILStubClass.IL_STUB_ReversePInvoke(Int64, Int64, Int64, Int32)

* ContextTransitionFrame



### Thread     10172

Thread type:   Background


Stack trace:



### Thread     14028

Thread type:   Background


Stack trace:



### Thread     10924

Thread type:   Background


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     0

Thread type:   Foreground


Stack trace:



### Thread     8412

Thread type:   Background


Stack trace:





Hope it will help you and makes your life much easier :) You can easily change it to debug any process.


Thank you


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



How to handle SQL Server problems on shared and other hostings

Does your hosting have sql server problems all the time or from time to time? Do you lose data because of sql server issues? If yes then this post may be good case for you.


I use my own technique to backup critical data to HDD.

If there is any sql server issue then my code does backup of critical data to HDD and then restores that data from HDD when sql server back and running well.


Sure, just storing data into file(s) on hdd is really not good case (or even worse). You will need a lot of code to handle concurrency, navigation, select and find data, ordering and much more, and it is the place where LiteDB comes in to scene.


So my logic is simple:

- is there an exception while saving data into SQL Database?

- take that data and store it into LiteDB

- create a scheduled task to check if SQL Server issue has gone and do insert/update your SQL Database

- successfully inserted/updated? remove the data from LiteDB otherwise keep trying until success.


LiteDB uses single HDD file to store data and provides SQL-like functionality to operate your data.


Really simple and useful technique to avoid losing critical and important data. Also I show notification to related user that there are some pending data so users would not be disappointed.


Thank you :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Javascript and jQuery - creating a lot of elements like a big grid


Hello dear friends,


Last time I did create a big grid using jQuery and Javascript, and faced with the performance problem.




My task was to create a big calendar grid that should have 20 rows at least and 180 columns (each column for each day for the 6 month period). While developing this grid I had noticed that Microsoft Edge and FireFox browsers hung during this creation cycle for a few seconds (Google Chrome seemed much better). My first method did create each cell for every column and row.

So how to optimize this? I did some googling and found a suggestion to use setTimeout function to avoid browser hanging, but it didn't help a lot in my case. Ugh! What should I do with it. Just in case, here is the piece of code I used to create my grid:

.....

var row, cell;

for(var r=0;r<20;r++)

{

    row = $("<div class='row'></div>");

    for(var i=0; i<180;i++)

    {

         cell = $("<div class='cell'></div>");

         ..........

        row.append(cell);

    }

    ....

    grid.append(row);

}


So, looks pretty simple? I was thinking for a few days on it trying to understand what could I do with it.

At some point I just had thought "what if create one row only and then just clone it as much as needed?". And yes, I was completely right! :) it did reduce time from 4-5 seconds to less than 1 second:

.....
var row, cell;


{
    row = $("<div class='row'></div>");

    for(var i=0; i<180;i++)
    {
         cell = $("<div class='cell'></div>");
         ..........
        row.append(cell);
    }
    ....
    grid.append(row);
}

for (var c = 1; c < 20; c++) {
   grid.append(row.clone(false).off());
}

So now its clear that the clone method grid.append(row.clone(false).off()); much much faster than creating a new elements.



Thank you and see you next time :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



MS SQL - Speed up Order By with OFFSET FETCH paging

UPDATE:

So adding nonclustered index, as the MS SQL Execution Plan suggests, seems reduced the query time even more, here is the suggested nonclustered index:

USE [myDB]

GO


CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX2_tblPictures

ON [dbo].[tblPictures] ([RatingTotal],[shape])

INCLUDE ([PictureID],[OwnerID],[PictureTypeID])

GO

but using only nonclustered index, without my #temp table technic, seems does not help too much.


Hello,

This time I'm going to share my solution on how I did reduce time of my SQL query with Order By clause by almost x2 times.

Firstly let me show my original query:

select @rowstotal = count(*)

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])



Select

@rowstotal as TotalCount

, PictureID

, OwnerID

, PictureName

, [Description]

, Description2

, Description3

, aspectRatio

, [min]

, [max]

, [percent]

, thumbWidth

, thumbHeight

, processState

, keywords

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])

Order By RatingTotal Desc

OFFSET @pageNumber ROWS FETCH NEXT @pageSize ROWS ONLY;


So to be able to calculate pages we have to know the total number of rows in the DB. Each row will contain TotalCount - total number of rows


(pay attention, that the techinc COUNT(*) OVER () as TotalCount is slower than just select @rowstotal = count(*) From that I use in my queries )


Then we select all needed columns with the order and paging technic.

Everything looks great, simple and fast unless you got more than 300 000 rows in the table.

Main problem here is that the Order By clause takes 98% of the whole stored procedure and in my case it takes 5 sec in total.

How to speed it up? 

I did google a lot of posts and almost everyone suggests to use indexes or nonclustered indexes, or other things.

As I'm not DBA and do not know a lot about all these things I decided to check what if I would select only one column instead of all of them? 

And when I got my 40 rows for one page I would select the rest of columns?

I did write some test query and had been surprised that it took almost x2 time less than before.


So here is the new query:

IF OBJECT_ID(N'tempdb..#tempPage', N'U') IS NOT NULL

DROP TABLE #tempPage;


create table #tempPage

(

TotalCount int null

, PictureID int

, OwnerID int null

, PictureName nvarchar(500) null

, [Description] nvarchar(500) null

, Description2 nvarchar(500) null

, Description3 nvarchar(max) null

, aspectRatio decimal(18,2) null

, [min] money null

, [max] money null

, [percent] decimal(18,2) null

, thumbWidth decimal(18,2) null

, thumbHeight decimal(18,2) null

, processState varchar(50) null

, keywords varchar(500) null

)


insert into #tempPage

Select

null --@rowstotal

,PictureID

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

,null

From [dbo].[tblPictures]

where (@OwnerId = 0 OR @OwnerId = [OwnerID])

and (@FilterBy = 0 OR @FilterBy = PictureTypeID)

and (@Shape = '' OR @Shape = [shape])

Order By RatingTotal Desc

OFFSET @pageNumber ROWS FETCH NEXT @pageSize ROWS ONLY


Update t

Set t.OwnerID = p.OwnerID

,t.TotalCount = @rowstotal

,t.PictureName=p.PictureName

,t.[Description]=p.[Description]

,t.Description2=p.Description2

,t.Description3=p.Description3

,t.aspectRatio=p.aspectRatio

,t.[min]=p.[min]

,t.[max]=p.[max]

,t.[percent]=p.percent

,t.thumbWidth=p.thumbWidth

,t.thumbHeight=p.thumbHeight

,t.processState=p.processState

,t.keywords=p.keywords

From #tempPage as t

INNER JOIN [dbo].[tblPictures] as p on p.PictureID = t.PictureID;

select * from #tempPage;

DROP TABLE #tempPage;


 Now we use a temp sql table to select only IDs firstly and then we update all 40 rows and set all other columns' values.

Also do not forget to drop temp table before and after to avoid existing table errors.


So this is the way that reduced my query time from 5 seconds to almost 2 seconds in total.


(SQL Server 2012 version: x64 11.0.3156.0)


Of course, if I would add nonclustered index, as MS SQL Graphical Execution Plan suggests, it may be even faster, but to be able to add them we have to understand what it is and how to use it. So maybe next step will be to learn and add nonclustered index ;)



Thank you and see you soon :)

 







1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y