SQL Optimization

  SQL Optimization 

1. Add where on your query 

2. If you remove some data after the data return then remove the remove condition in the select statement where the clause

3. Most important is to Add an Index on the column where you use the where clause.

4. Remove multiple call databases from code and call once and find all data at once to reduce the number of roundtrips. 

5. Remove the index on the temp table if the data is in limit.

6. Remove subquery use Join. 

7. Remove the query that returns the subset of results, instead use a query that can return full results.

Kendo Cascade dropdown

It is easy to make dropdownlist cascade. You just have to add ".CascadeFrom()" function and need to pass the control name in it's parameter. Please follow the below example.

EX :
Parent control is Country:-

@(Html.Kendo().DropDownListFor(m => m.Customer)

.Name("Country")                                                                .DataTextField("Text")                                                            .DataValueField("Value")

.OptionLabel("All")                                                                     .MinLength(0)                                                                 .HtmlAttributes(new { @class = "", id = "Country" })

 .BindTo(Model.Country)

 .Filter(FilterType.StartsWith)

 .Value(Model.SelectedCountry??"")

)

And cascade control is State:-

@(Html.Kendo().DropDownList()

.Name("State")

.DataSource(source => source.Read(read =>

{

read.Action("GetState", "Common")

.Data("CountryFetch");

}).ServerFiltering(true);

})

.OptionLabel("All")

.HtmlAttributes(new { @class = "", id = "state" })

.Filter(FilterType.StartsWith)

.DataValueField("Value")

.DataTextField("Text")

.AutoBind(false)

.CascadeFrom("Country")

)

<script>

   function CountryFetch () {

return {

Country: $("#Country").val(),

text: $('input[aria-owns="state_listbox"]').val()

};

   }

</script>

Here we can see 'ServerFiltering(true)' extend function which denote filtration parameter will pass to api and data which need to pass is mention by Data('CountryFetch') function.

CountryFetch is a js model which is written below the code in script tag.

Bind Kendo Grid With Dynamic Column

We are bind dynamic columns and there values as a rows to Kendo UI grid,

To do this we are using a Json file as a data and column list.

The only restriction is that column name has to be same as Data Column name.

Please  Click to Download the Application

  

Json format like :

[["Id","Name","BranchName","RateBase","RateBaseDate"], {"CrrierId":"1","CarrierName":"AAA COOPER TRANSPORTATION","BranchName":"ALL","RateBase":"ABF50401","RateBaseDate":"06-25-2012","PricingFromDate":"01-01-2014" ,"PricingToDate":"09-02-2015","Currency":"USD","CargoTypeId":"1","OriginCode":"1111","DestinationCode":"1101"}]

 Control code : 

public class HomeController : Controller

    {

        [HttpGet]

        public ActionResult CarrierSelection()

        {

           

         

            List<string> HeaderList = new List<string>();

            List<dynamic> CustomerList = new List<dynamic>();

            List<dynamic> CarrierList = new List<dynamic>();

            ArrayList proList = new ArrayList();

            dynamic Obj;

            try

            {

                StreamReader sr1 = new StreamReader(Server.MapPath("~/Content/JesonDataCarrierNew.json"));

                string JsonStr = sr1.ReadLine();

                JToken tokenObj = JRaw.Parse(JsonStr);

                if (tokenObj is JArray)

                {

                    JArray JarrayObj = JArray.Parse(JsonStr);

                    if (JarrayObj.Count > 0)

                    {

                        // If column want hide then user may add column list in array

                        if (JarrayObj[0].Type == JTokenType.Array)

                        {

                            foreach (string objMemberInfo in JarrayObj[0])

                            {

                                proList.Add(objMemberInfo);

                            }

                            JarrayObj.RemoveAt(0);

                            //Grid Data Data Source

                            JsonStr = JarrayObj.ToString(Newtonsoft.Json.Formatting.None);

                        }

                        else if (JarrayObj[0].Type == JTokenType.Object)

                        {

                            JObject obj = (JObject)JarrayObj[0];

                            foreach (KeyValuePair<string, JToken> objMemberInfo in obj)

                            {

                                proList.Add(objMemberInfo.Key);

                            }

                        }

                    }

                }

                else if (tokenObj is JObject)

                {

                    // Process JObject

                }

                CarrierList = JsonConvert.DeserializeObject<List<dynamic>>(JsonStr);

                CarrierList = CarrierList.ToList();

                ViewBag.ColumnNames = proList.ToArray();

                ViewBag.HeaderListValues = HeaderList;

                return View(CarrierList);

            }

            catch (Exception ex)

            {

                throw;

            }

          

        }

    }

View Page :

<link href="Styles/kendo.common.min.css" rel="stylesheet" type="text/css" />

<link href="Styles/kendo.default.min.css" rel="stylesheet" type="text/css" />

<script src="js/jquery.min.js" type="text/javascript"></script>

<script src="js/kendo.web.min.js" type="text/javascript"></script>

  @(Html.Kendo().Grid(Model)

                .Name("Grid")

                .Columns(col =>

                            {

                                foreach (var name in ViewBag.ColumnNames)

                                {

                                    col.Bound(name).Visible(true);

                                }

                }

                       

                )

                .Pageable(pageable => pageable

                                            .PageSizes(true)

                                            .ButtonCount(5))

                                            .Groupable()

                                            .Sortable()

                                            .Resizable(resize => resize.Columns(true))

                                            .BindTo(Model)

                                            .Reorderable(reorder => reorder.Columns(true))

                                            .Filterable(filterable => filterable

                                                                            .Extra(true)

                                                                                .Operators(operators => operators

                                                                                .ForString(str => str.Clear()

                                                                                    .StartsWith("Starts with")

                                                                                    .IsEqualTo("Is equal to")

                                                                                    .IsNotEqualTo("Is not equal to")

                                                                                ))

                                                                            )

                                            .DataSource(dataSource => dataSource

                                            .Ajax().PageSize(15)

                                            )

               

                                          

         )