Maximizing Performance with the Entity Framework [

问题:

This question already has an answer here:

• How to “warm-up” Entity Framework? When does it get “cold”? 5 answers

I am developing travel web site. When user input a location for search(autocomplete) my action return all cities, that cities regions, regions, regions translations, hotels..... which start with user input

I used Entity code first. But it is response time is too much. How can I optimize this? How can I decrease time?

public JsonResult AutoComplateCityxxxxxxxx(string culture, string q)
    {

        List<Tuple<string, int, int>> result = new List<Tuple<string, int, int>>();

        using (var db = new TourismContext())
        {

            ModelState.Remove(q);

            var query = SearchWordFunctions.WordFunctions(q);


            var ListCity = db.CityTranslations.Where(
                    c => (c.Slug.StartsWith(query) || c.Name.StartsWith(query))
                        &&
                        c.City.Latitude.HasValue
                ).GroupBy(x => x.CityID).Select(g => g.FirstOrDefault()).Take(10);

            var ListRegion = db.RegionTranslations.Where(
                    r => (r.Slug.StartsWith(query) || r.Name.StartsWith(query))
                        &&
                        r.Region.Latitude.HasValue
                        &&
                        r.Region.RefID == 0 && r.Region.IsShow > 0
                ).GroupBy(x => x.RegionID).Select(g => g.FirstOrDefault()).Take(10);

            var LandMark = db.CityLandMarks.Where(l => l.Translations.Any(t => t.Name.StartsWith(query)) && l.Latitude.HasValue).Take(10);



            var hotel = db.HotelTranslations.Where(t => t.Url.Contains(query) && t.Hotel.Status > 0 && t.Culture.Code == culture).ToList();

            result.Clear();

            foreach (var item in ListCity.OrderBy(o => o.Name.Length))

            {
                result.Add(new Tuple<string, int, int>(string.Concat(item.Name, " - <b>", item.City.Country.Translations.Single(t => t.CultureID == 1).Name, "<b>"), item.CityID, 1));

                if (db.Regions.Any(r => r.CityID == item.CityID))
                {
                   var regions = db.Regions.Where(r => r.CityID == item.CityID && r.Latitude.HasValue && r.RefID == 0 && r.IsShow > 0).GroupBy(g => g.ID).Select(x => x.FirstOrDefault()).ToList().OrderByDescending(o => o.SearchRating).Take(10);

                    foreach (var regItem in regions)
                    {
                      result.Add(new Tuple<string, int, int>(string.Concat(regItem.Translations.FirstOrDefault().Name, " - <b>", item.Name, "</b> - <b>", regItem.City.Country.Translations.FirstOrDefault().Name, "<b>"), regItem.ID, 2));
                    }
                }
            }
            if (ListCity.Count() <= 0)
            {
                foreach (var item in ListRegion)
                {
                    result.Add(new Tuple<string, int, int>(string.Concat(item.Name, " - <b>", item.Region.City.Translations.Single(t => t.Culture.Code == culture).Name, "</b> - <b>", item.Region.City.Country.Translations.Single(t => t.Culture.Code == culture).Name, "</b>"), item.RegionID, 2));
                }
            }

            foreach (var item in LandMark)
            {
                result.Add(new Tuple<string, int, int>(string.Concat(item.Translations.FirstOrDefault().Name, " - <b>", item.City.Translations.FirstOrDefault().Name, "</b> - <b>", item.City.Country.Translations.FirstOrDefault().Name, "</b>"), item.ID, 3));
            }

            foreach (var item in hotel)
            {
                result.Add(new Tuple<string, int, int>(string.Concat(item.Name, " - <b class=\"refid\" data=\"" + item.HotelID + "\">", item.Hotel.Region.City.Translations.First().Name, "</b>"), item.Hotel.Region.CityID, 1));
          }

        }

        return Json(result, JsonRequestBehavior.AllowGet);
    }

回答1:

Without seeing your generated DB schema or knowing anything about the DB engine or server configuration, it is difficult to say with any certainty what will improve your query performance the most. Reviewing your code, though, I would recommend ensuring that the following attributes have indexes associated with them:

1. CityTranslations.Slug
2. CityTranslations.Name
3. RegionTranslations.Slug
4. RegionTranslations.Name
5. CityLandmarks.Name

That should give you an immediate boost, since StartsWith should generate a clause in the form LIKE 'xxx%', so an index should significantly improve performance.

HotelTranslations may need to be revisited to some extent, since Contains will generate a clause of the form LIKE '%xxx%' which will not benefit from a simple index.

If there are already indexes on those fields, then please provide additional information about your configuration (DB, server config, generated schema, etc).