Improve SQL query performance SQL server 2012

Question

0.00/5 (No votes)

See more:

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:

SQL

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:

SQL

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

Posted 14-Oct-16 19:21pm

.net333

Updated 15-Oct-16 0:26am

v3

Add a Solution

Comments

Wendelius 15-Oct-16 2:34am

If I understand your question correctly the vwOrdersforManageUsers is a view. If that's true, please post the query that is used to create the view.

.net333 15-Oct-16 6:04am

Hi Mika,
Thanks for your reply. yes vwOrdersforManageUsers is a view. I posted view

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2016-10-15T00:26:00

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

SQL

...
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

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.