提高SQL查询性能SQL Server 2012 [英] Improve SQL query performance SQL server 2012

问题描述

我创建了一个视图并在存储过程中调用了它。当前执行时间是2秒。现在我需要将执行时间从2秒减少到1/2秒。我只需要获得前25个最新订单。

区别和订单引起问题。

我为订单ID创建了非聚集索引但没有用。

请帮我提高性能

问候，

Ramakrishna



我是什么尝试过：



查看：

Hi,
i created a view and called this in stored procedure. Current execution time is 2 Seconds.Now i need to reduce the execution time from 2 sec to 1/2 sec. i need to get only top 25 latest orders.
Distinct and Order By causing Issues.
I Created non-clustered index for Order Id but no use.
Please help me to improve performance
Regards,
Ramakrishna

What I have tried:

View:

SELECT RO.GenerateId, RO.CompanyId, RO.DeliveryDate, RO.DeliveryTime, RO.UserId, RO.Name, APD.AppName, L.LocationName, RO.CreatedDate, RO.ItemState, 
CONVERT(varchar, RO.TotalPrice, 1) AS TotalPrice, CONVERT(varchar, RO.DeliveryFee, 1) AS DeliveryFee, RO.LocationId, CONVERT(varchar, RO.Tax, 1) 
AS Tax, CONVERT(varchar, RO.Tip, 1) AS Tip, RO.EntityType, RO.PromotionCode, RO.PromotionType, RO.TotalWeight, RO.ItemWeight, CONVERT(varchar, 
RO.ConvenienceFeePercentage, 1) AS ConvenienceFee, RO.ConvenienceFee AS ConvenienceFeePercentage, RO.PromotionDiscount, RO.PhoneNumber, 
RO.PaymentType, RO.Note, A.Line1, A.Line2, A.City, A.State, A.Country, A.Zip, RI.CommissionPercentage, RO.Commission, RI.Currency, OCS.AssignedTo, 
RO.GroupId, RO.OrderType, UL.UserId AS UALUserId, RO.ZoneId, OCS.ClaimedBy
FROM  dbo.Locations AS L INNER JOIN
      dbo.Address AS A ON L.AddressId = A.Id INNER JOIN
      dbo.UserAccessLocations AS UL ON L.LocationId = UL.LocationId INNER JOIN
      dbo.AppDetails AS APD ON L.AppId = APD.AppId INNER JOIN
      dbo.RestaurantAppInfo AS RI ON L.AppId = RI.AppId AND 
      L.LocationId = RI.LocationId INNER JOIN
      dbo.OrderClaimStatus AS OCS INNER JOIN
      dbo.RestaurantOrders AS RO ON OCS.OrderId = RO.GenerateId 
      ON L.LocationId = RO.LocationId AND UL.LocationId = RO.LocationId
WHERE (OCS.OrderType IS NULL) AND (RO.DeliveryDate >= DATEADD(day, - 30, GETDATE())) AND (RO.OrderStatus = '1') AND (RO.ItemState <> 'Order Canceled')

存储过程：

stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER proc [dbo].[GetAllOrders]
as
begin
SET NOCOUNT ON;
select distinct top 25 GenerateId,CompanyId,DeliveryDate,DeliveryTime,UserId,Name,AppName,LocationName,CreatedDate,ItemState,TotalPrice,DeliveryFee,LocationId,Tax,Tip,EntityType,PromotionCode,PromotionType,null as ItemWeight,TotalWeight,
ConvenienceFee,ConvenienceFeePercentage,PromotionDiscount,PhoneNumber,
PaymentType,Note,Line1,Line2,City,State,Country,Zip,CommissionPercentage,Commission,Currency,AssignedTo,GroupId,OrderType
from vwOrdersforManageUsers Order By GenerateId desc
end

推荐答案

首先，检查连接是否到位。不确定，但似乎你可能放错了以下的 ON 条款

First of all, check if the joins are in place. Not sure but it seems that you may misplaced the ON clause for the following

...
INNER JOIN dbo.OrderClaimStatus AS OCS -- ON condition?
INNER JOIN dbo.RestaurantOrders AS RO ON OCS.OrderId = RO.GenerateId 
      ON L.LocationId = RO.LocationId AND UL.LocationId = RO.LocationId ...

完成后，尝试使用以下索引（您可能已经拥有其中一些索引）：

After you've done that, try having the following indexes (you already may have some of those):

Table               Index columns
----------------    --------------------------------------
Location            AddressId
Location            AppId
Address             Id
UserAccessLocations LocationId
AppDetails          AppId
RestaurantAppInfo   AppId
RestaurantOrders    GenerateId 
RestaurantOrders    LocationId
RestaurantOrders    OrderStatus, ItemState, DeliveryDate
OrderClaimStatus    OrderId
OrderClaimStatus    OrderType

什么是 RestaurantOrders 上包含三列的索引，如果你使用的 OrderStatus 和 ItemState 是常见的，那么考虑放置 DeliveryDate 列作为索引中的第一列。

What comes to the index on RestaurantOrders containing the three columns , if the OrderStatus and ItemState you use are common then consider placing the DeliveryDate column as the first column in the index.

这篇关于提高SQL查询性能SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！