CODEWITHSUNDEEP
sqlsql-server
Edward Gizbreht
Edward Gizbreht

Reputation: 306

MS SQL Order By To long execute

i have a problem with sql execution time with Order By. The problem is if i am doing Order By, it is take a long time and i have a peak on my server. If there any way to take a max values without order by

Example of Code with Order By:

SELECT DISTINCT TOP 3 s.title, s.imageUrl, count(sv.saleid) as mostViewPeriod14Days, s.guid 
FROM dbo.Sales s 
INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON s.id = b_key.saleId 
INNER JOIN dbo.SaleView sv ON s.id = sv.saleId 
WHERE (sv.date <= GETDATE()) 
AND (sv.date >= GETDATE() - 14) 
AND s.isActive = 1 AND s.isHotsale = 1 
AND b_key.brandCategoryId = 28 AND s.id NOT IN (SELECT sv.saleId FROM dbo.SaleView sv WHERE sv.userId = 99114) 
GROUP BY s.title, s.imageUrl, s.guid 
ORDER BY mostViewPeriod14Days Desc

And what i what is work good but without order:

SELECT DISTINCT TOP 3 s.title, s.imageUrl, count(sv.saleid) as mostViewPeriod14Days, s.guid 
    FROM dbo.Sales s 
    INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON s.id = b_key.saleId 
    INNER JOIN dbo.SaleView sv ON s.id = sv.saleId 
    WHERE (sv.date <= GETDATE()) 
    AND (sv.date >= GETDATE() - 14) 
    AND s.isActive = 1 AND s.isHotsale = 1 
    AND b_key.brandCategoryId = 28 AND s.id NOT IN (SELECT sv.saleId FROM dbo.SaleView sv WHERE sv.userId = 99114) 
    GROUP BY s.title, s.imageUrl, s.guid

Upvotes: 0

Views: 76

Answers (1)

BenM
BenM

Reputation: 104

Try the query below and let me know if it helps. A few general points below

  • WHERE clauses that contain a calculated date are slower than those that don't.

  • If you can avoid it, never put a "sub-select" in a query. Change that to a left join and check for null records (it's way faster)

    DECLARE @Today DATETIME; 
DECLARE @TwoWeeksAgo DATETIME;

SET @Today = GETDATE();
SET @TwoWeeksAgo = DATEADD(DAY, -14, GETDATE());

SELECT TOP 3 
s.title, 
s.imageUrl, 
count(sv.saleid) as mostViewPeriod14Days, 
s.guid 
FROM dbo.Sales s 
INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON s.id = b_key.saleId 
INNER JOIN dbo.SaleView sv ON s.id = sv.saleId 
LEFT JOIN dbo.SaleView sv2 on sv2.id = sv.id and sv2.userId = 99114
WHERE sv.Date BETWEEN @TwoWeeksAgo AND @Today
AND sv2.id IS NULL     -- This is the check that handles your sub-select earlier
AND s.isActive = 1 AND s.isHotsale = 1 
AND b_key.brandCategoryId = 28 
GROUP BY s.title, s.imageUrl, s.guid 
ORDER BY mostViewPeriod14Days Desc

Upvotes: 1

Related Questions