如何提高从ASP.NET MVC中的SQL Server数据库获取数据的速度 [英] How to improve speed of getting data from SQL Server database in ASP.NET MVC

问题描述

我是asp.net mvc5的新手.我有一个模型 Shipping ，而我的 dbo.Shippings.sql 是这样的:

I am new to asp.net mvc5. I have a model Shipping and my dbo.Shippings.sql is like this:

CREATE TABLE [dbo].[Shippings] 
(
    [Id]                INT             IDENTITY (1, 1) NOT NULL,
    [TruckerId]         NVARCHAR (MAX)  NULL,
.........more there...

    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
);

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

并且我的第一页必须显示所有未售出的货运( if shipping.truckerId == null 表示尚未售出)

And my first page must shows all shippings that not sold (if shipping.truckerId == null means not sold yet)

在我的 ShippingController 中，我有以下内容:

In my ShippingController I have below:

// 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;

不是一种明智的运输方式，如果数据库变大，它会非常慢.

is not clever way to get shippings and it is very slow if the db gets bigger..

我只需要显示它的FromCity，FromState，FromZipCode，PickUpDateTime，DestCity，DestState，DestZipCode，Price，Content和TruckerId即可知道是否出售.

I only need to show its FromCity, FromState, FromZipCode, PickUpDateTime, DestCity, DestState, DestZipCode, Price, Content, and TruckerId which tells it is sold or not.

请帮助！

已编辑并更新:当时我在问这个问题，我对Web开发和数据库还很陌生.通过为db创建索引，我可以提高性能.如此快速的查找成为可能.另外，我重新制作了Web应用程序的体系结构.我还创建了一个未售出的运输视图.因此，始终只要Web从db中获得视图，就不必花费时间进行过滤.

Edited and Update: At the time I was asking this question I was fairly new to web development and database. I was able to make better performance by creating indexes for db. So quickly lookup possible. Also I re made the architecture of web app. Also I created a View for shipping that were not sold. So always if the web hit the view from db, it will does not have to spend time for filtering out.

推荐答案

信誉不足以发表评论，我将其删除，或重新制作以回答!!!

Not enought reputation to comment, i'll delete this, or remake to answer !!!

那:

1. 添加一些枚举状态(出售，活动，租金等)，或者仅在不需要更多状态时才使bool变卖价
2. 或者某些最优化(例如，将NVARCHAR(MAX)减小为可能的最长值，例如，属性名称为NVARCHAR(20)

1. adding some enum status(sold,active,in rent,...) or only making bool value for sold if you dont need more statuses
2. Or maybe some optimalization (like reduce NVARCHAR(MAX) maybe to possible longest value so for example attribute name as NVARCHAR(20)

P.S.:我认为，此选择不会减慢很多速度....

P.S. : I think, that this select insn't slowing down a lot ....

这篇关于如何提高从ASP.NET MVC中的SQL Server数据库获取数据的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！