长时间执行请求

本文关键字：请求 执行 长时间 | 更新日期: 2023-09-27 18:32:22

美好的一天！ 

List<TotalServiseCalls> TSC = (
    from scall in contextOMNIDB.UserFields698.AsEnumerable()
    where scall.f2_creationd >= referenceDate
    group scall by scall.f2_creationd.Month into scalls
    select new TotalServiseCalls
    {
        mountN = (from sc in contextOMNIDB.UserFields698.AsEnumerable()
            where sc.f2_creationd.Month == scalls.Key
            select sc.f2_creationd).FirstOrDefault(),
        date = (from sc in contextOMNIDB.UserFields698.AsEnumerable()
            where sc.f2_creationd.Month == scalls.Key
            select sc.f2_creationd.ToString("MMMM yyyy")).FirstOrDefault(),
        totalCount = scalls.Count()
     }).OrderBy(p => p.mountN).ToList();

MSSQL服务器有很多应用程序，它的负载非常高。此查询执行 40 秒。这是因为服务器上的拥塞还是查询的复杂性？

这些表有大约一万条记录，大小为一兆字节。

长时间执行请求

很难从您提供的信息中说出是查询还是拥塞。您可以改进的两件事：

  1. 将contextOMNIDB.UserFields698.AsEnumerable()放入变量中
  2. 也把它变成可重用的形式：from sc in contextOMNIDB.UserFields698.AsEnumerable() where sc.f2_creationd.Month == scalls.Key select sc.f2_creationd

下面是一个可能的重构版本：

var userFields = contextOMNIDB.UserFields698.AsEnumerable();
List<TotalServiseCalls> TSC = (
    from scall in userFields
    where scall.f2_creationd >= referenceDate
    group scall by scall.f2_creationd.Month into scalls
    select new TotalServiseCalls
    {
      mountN = Helper(userFields, scalls.Key).FirstOrDefault(),
      date = Helper(userFields, scalls.Key).Select(o => o.ToString("MMMM yyyy")).FirstOrDefault(),
      totalCount = scalls.Count()
    }).OrderBy(p => p.mountN).ToList();

帮助程序方法是（我不得不使用object，因为我对您的业务对象不够了解）：

private IEnumerable<object> Helper(IEnumerable<object> userFields, object key)
{
  return from sc in userFields
         where sc.f2_creationd.Month == key
         select sc.f2_creationd;
}

您不必查询contextOMNIDB.UserFields698.AsEnumerable() 3 次，您只需从组中获取第一条记录，即可获取 Month 作为分组键：

List<TotalServiseCalls> TSC = (
    from scall in contextOMNIDB.UserFields698.AsEnumerable()
    where scall.f2_creationd >= referenceDate
    group scall by scall.f2_creationd.Month into scalls
    select new {
        mountN = scalls.Key,
        date = scalls.Select(x => x.ToString("MMMM yyyy").First(),
        // You can use scalls.OrderBy(x => x (or any other order)).First()
        // if you want to get specific date
        totalCount = scalls.Count()
    }
).OrderBy(p => p.mountN).ToList();

下面是一个简单的示例：

var list = new List<Tuple<string, string>>();
list.Add(new Tuple<string, string>("test1", "2"));
list.Add(new Tuple<string, string>("test1", "1"));
list.Add(new Tuple<string, string>("test2", "1"));
list.Add(new Tuple<string, string>("test2", "6"));
list.GroupBy(x => x.Item1)
    .Select(x => 
            new {
                a = x.Key,
                b = x.OrderByDescending(y => y).First(),
                c = x.Count()
            }
    ).ToList()
相关文章：