如何提高在ASP.NET MVC中从SQL Server数据库获取数据的速度（复制）

问题描述：

我是新来的asp.net mvc5。如何提高在ASP.NET MVC中从SQL Server数据库获取数据的速度（复制）

我有一个模型Shipping我dbo.Shippings.sql如下所示：

CREATE TABLE [dbo].[Shippings] 
(
    [Id]    INT    IDENTITY (1, 1) NOT NULL, 
    [TrackingNumber] NVARCHAR (MAX) NOT NULL, 
    [FromCompany]  NVARCHAR (MAX) NULL, 
    [FromContactName] NVARCHAR (MAX) NULL, 
    [From]    NVARCHAR (MAX) NOT NULL, 
    [FromCity]   NVARCHAR (MAX) NOT NULL, 
    [FromState]   NVARCHAR (MAX) NOT NULL, 
    [FromZipCode]  NVARCHAR (MAX) NOT NULL, 
    [FromCountry]  NVARCHAR (MAX) NOT NULL, 
    [FromPhoneNumber] NVARCHAR (MAX) NULL, 
    [FromFaxNumber]  NVARCHAR (MAX) NULL, 
    [Commodity]   NVARCHAR (MAX) NOT NULL, 
    [OrderDateTime]  DATETIME  NOT NULL, 
    [OrderByName]  NVARCHAR (MAX) NOT NULL, 
    [PickUpDateTime] DATETIME  NOT NULL, 
    [PickUpRefNum]  NVARCHAR (MAX) NOT NULL, 
    [DestCompany]  NVARCHAR (MAX) NULL, 
    [DestContactName] NVARCHAR (MAX) NULL, 
    [Destination]  NVARCHAR (MAX) NOT NULL, 
    [DestCity]   NVARCHAR (MAX) NOT NULL, 
    [DestState]   NVARCHAR (MAX) NOT NULL, 
    [DestZipCode]  NVARCHAR (MAX) NOT NULL, 
    [DestCountry]  NVARCHAR (MAX) NOT NULL, 
    [DestPhoneNumber] NVARCHAR (MAX) NULL, 
    [DestFaxNumber]  NVARCHAR (MAX) NULL, 
    [DeliveryDateTime] DATETIME  NOT NULL, 
    [Price]    FLOAT (53)  NOT NULL, 
    [Category]   NVARCHAR (MAX) NOT NULL, 
    [FreightClass]  INT    NOT NULL, 
    [Pkgs]    INT    NOT NULL, 
    [Weight]   REAL   NOT NULL, 
    [Length]   REAL   NOT NULL, 
    [Width]    REAL   NOT NULL, 
    [Height]   REAL   NOT NULL, 
    [IsDelivered]  BIT    NOT NULL, 
    [ApplicationUserId] NVARCHAR (128) NOT NULL, 
    [AcceptOffer]  BIT    DEFAULT ((0)) NOT NULL, 
    [TruckerId]   NVARCHAR (MAX) NULL, 
    [Description]  NVARCHAR (MAX) NULL, 
    [FileName]   NVARCHAR (255) NULL, 
    [ContentType]  NVARCHAR (100) NULL, 
    [Content]   VARBINARY (MAX) NULL, 

    CONSTRAINT [PK_dbo.Shippings] 
     PRIMARY KEY CLUSTERED ([Id] ASC), 
    CONSTRAINT [FK_dbo.Shippings_dbo.AspNetUsers_ApplicationUserId] 
     FOREIGN KEY ([ApplicationUserId]) 
      REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE 
); 
GO 

CREATE NONCLUSTERED INDEX [IX_ApplicationUserId] 
    ON [dbo].[Shippings]([ApplicationUserId] ASC);

而且我的第一页必须表明，并非所有售出运送（if shipping.truckerId == null意味着尚未出售）

在我ShippingController我有如下：

// GET: Shipping 
    public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page) 
    { 
     //below is sorting 
     ViewBag.CurrentSort = sortOrder; 
     ViewBag.FromSortParm = String.IsNullOrEmpty(sortOrder) ? "from_desc" : ""; 
     ViewBag.DestSortParm = sortOrder == "Destination" ? "dest_desc" : "Destination"; 

     if (searchString != null) 
     { 
      page = 1; 
     } 
     else 
     { 
      searchString = currentFilter; 
     } 

     ViewBag.CurrentFilter = searchString; 

     //below is searching state and zipcode 
     var shippings = from s in db.Shippings 
         where s.TruckerId == null 
         select s; 

     if (!String.IsNullOrEmpty(searchString)) 
     { 
      shippings = shippings.Where(s => s.FromState.Contains(searchString)//from state 
            || s.DestState.Contains(searchString)//dest state 
            || s.FromZipCode.Contains(searchString)//from zipcode 
            || s.DestZipCode.Contains(searchString));//dest zipcode 
     } 

     switch (sortOrder) 
     { 
      case "from_desc": 
       shippings = shippings.OrderByDescending(s => s.From); 
       break; 
      case "Destination": 
       shippings = shippings.OrderBy(s => s.Destination); //not working 
       break; 
      case "dest_desc": 
       shippings = shippings.OrderByDescending(s => s.Destination); //not working 
       break; 
      default: 
       shippings = shippings.OrderByDescending(s => s.OrderDateTime); 
       break; 
     } 

     int pageSize = 10; 
     int pageNumber = (page ?? 1); 
     return View(shippings.ToPagedList(pageNumber, pageSize)); 
     //return View(shippings.ToList()); 

    }

我想：

var shippings = from s in db.Shippings 
       where s.TruckerId == null 
       select s;

是不聪明的方式得到运送，这是很慢，如果分贝变得更大..

请帮帮忙！

你有许多'nvarchar（max）'列......为什么？ – SqlZim

@SqlZim我只是设置它们'字符串'没有给予限制。我应该给他们最大的价值？ –

我不认为你真正的问题在于你建议的代码。我认为是在那些包含。这是分析所有这些价值的实际成本。一个问题：你真的在视图中显示所有这些列吗？ –

答

显然你需要检查你的数据类型。它只是不可行的，你实际上需要很多VARCHAR(MAX)列。所以首先要从这个意义上重塑数据库。我强烈建议你通过SQL Server Central看看Joe Celko的Stairway to Data系列，它会给你一个数据类型及其正确用法的清晰画面。

在另一方面，如果你想只从使用LINQ某些字段，你可以用像去：

using (var ctx = new MyDataContext()) 
{ 
    var theDataINeed = (from myData in ctx.Shipping 
         where myData.TruckerId == null 
         select new { 
          myData.FromCity, 
          myData.FromState, 
          myData.FromZipCode, 
          myData.PickUpDateTime 
         }); 
}

然后你把它传递给一个List<>左右，你会得到什么只你需要。

如何提高在ASP.NET MVC中从SQL Server数据库获取数据的速度（复制）

相关推荐