Sunday, February 25, 2018

T-SQL: MAX vs. TOP 1 - Which is better??

By Steve Endow

If you need to get the largest value for a field, should you use MAX in your query?  Or should you use TOP 1 with ORDER BY?



Which is better?  Which is faster?  Is that always true?

Do you think you know the answer?

Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.

The results may surprise you!



Did I miss anything or make any mistakes in my testing?  Are there other considerations when choosing between MAX vs. TOP 1?



Here are the queries that I used in my testing.  Note that your results will vary depending on how much data you have in your tables and your SQL Server version.


--MAX vs TOP 1 with ORDER BY

SET STATISTICS IO ON

SELECT MAX(DOCDATE) AS DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'

SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC    

SET STATISTICS IO OFF


SET STATISTICS IO ON;

WITH cteMaxDate (DOCDATE) AS
(
       SELECT MAX(DOCDATE) FROM PM10000 WHERE VENDORID = 'ACETRAVE0001'
       UNION 
       SELECT MAX(DOCDATE) FROM PM20000 WHERE VENDORID = 'ACETRAVE0001'
       UNION
       SELECT MAX(DOCDATE) FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'
)
SELECT MAX(DOCDATE) AS DOCDATE FROM cteMaxDate;

WITH cteMaxDate2 (DOCDATE) AS
(
       SELECT TOP 1 DOCDATE FROM PM10000 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC
       UNION 
       SELECT TOP 1 DOCDATE FROM PM20000 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC
       UNION
       SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC
)

SELECT MAX(DOCDATE) AS DOCDATE FROM cteMaxDate2;

SET STATISTICS IO OFF;



SELECT COUNT(*) FROM SEE30303  --73,069 records

SELECT TOP 10 * FROM SEE30303


SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE')

SELECT TOP 1 DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE') ORDER BY DATE1 DESC

SET STATISTICS IO OFF;



SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303

SELECT TOP 1 DATE1 FROM SEE30303 ORDER BY DATE1 DESC

SET STATISTICS IO OFF;



SET STATISTICS IO ON;

SELECT MAX(DATE1) AS DATE1 FROM SEE30303 OPTION (MAXDOP 1)

SELECT TOP 1 DATE1 FROM SEE30303 ORDER BY DATE1 DESC OPTION (MAXDOP 1)

SET STATISTICS IO OFF;



SELECT COUNT(*) AS Rows FROM IV30500
SELECT TOP 100 * FROM IV30500

SET STATISTICS IO ON;

SELECT MAX(POSTEDDT) AS POSTEDDT FROM IV30500 --OPTION (MAXDOP 1)

SELECT TOP 1 POSTEDDT FROM IV30500 ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)

SET STATISTICS IO OFF;



SET STATISTICS IO ON;

SELECT MAX(POSTEDDT) AS POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN '2017-01-01' AND '2017-12-31' --OPTION (MAXDOP 1)

SELECT TOP 1 POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN '2017-01-01' AND '2017-12-31' ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)

SET STATISTICS IO OFF;



SET STATISTICS IO ON;

SELECT MAX(TRXSORCE) AS POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') 

SELECT TOP 1 TRXSORCE FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') ORDER BY TRXSORCE DESC

SET STATISTICS IO OFF;



USE [TWO]
GO
CREATE NONCLUSTERED INDEX NCI_IV30500_ITEMNMBR
ON [dbo].[IV30500] ([ITEMNMBR])
INCLUDE ([TRXSORCE])
GO


USE [TWO]
GO
DROP INDEX IV30500.NCI_IV30500_ITEMNMBR
GO




Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+




No comments: