CODEWITHSUNDEEP
sqlsql-server
Mobin
Mobin

Reputation: 4920

SQL query optimization

I have a query that takes too long to execute. I need to know if there is any optimum way of executing that to reduce execution time.

my query is 

SELECT     TOP (2) ID,
                   (
                    SELECT     SUM(CurrentStock) AS SimilarItemQuantity
                    FROM          Inventory AS T1
                    WHERE      (Inventory.ProductName = ProductName)
                   ) 
                   AS Expr1
FROM         Inventory

Consider that for 20 records and it takes 15 seconds. Is there a more faster way of doing it.

Upvotes: 1

Views: 117

Answers (4)

Andriy M
Andriy M

Reputation: 77657

How about using an aggregate window function (SUM(…) OVER …)?

SELECT TOP (2)
  ID,
  SUM(CurrentStock) OVER (PARTITION BY ProductName) AS SimilarItemQuantity
FROM Inventory

As others have already mentioned, TOP (n) doesn't make much sense without an ORDER BY. For consistency, use a fixed order in such cases, e.g. ORDER BY ID.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You might consider rewriting this as a group by:

select top 2 id, sum(CurrentStock)
from Inventory i
group by id, ProductName
order by 2 desc

This is presuming that you want the two id's with the largest current stock.

Normally, you use TOP with an order by.

And, as others have mentioned, having an index on (id, ProductName) would help:

create index idx_inventory_id_ProductName on inventory(id, ProductName)

Upvotes: 0

SliverNinja - MSFT
SliverNinja - MSFT

Reputation: 31641

Add an index on Inventory.ProductName (key column) and include the CurrentStock and ID (included column) columns.

CREATE NONCLUSTERED INDEX [Inventory_IDX_ProductName] ON [dbo].[Inventory]
(
  [ProductName] ASC
)
INCLUDE ( [CurrentStock],[ID]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Correlated subquerties are a SQl antipattern, they can almost always be replaced by joins and speed up the process. You should add an order by clause anytime you are selecting top X or the results will not be consistent.

SELECT TOP 2 I1.ID, SUM(I2.CurrentStock) AS SimilarItemQuantity  
FROM   Inventory I1
join   Inventory I2 on I1.ProductName = I2.ProductName
GROUP BY I1.ID
ORDER BY I1.ID

Upvotes: 3

Related Questions