Just Code‎ > ‎

SQL - Performance comparison NVARCHAR(4000) vs NVARCHAR(max)

posted Feb 16, 2011, 12:21 AM by Peter Henell   [ updated Feb 17, 2011, 6:57 AM ]
The results are clear: nvarchar(max) is much slower in all operations except DELETE.

Script updated to include a few additional tests.
DECLARE @BitBucket1 NVARCHAR(4000),
        @BitBucket2 NVARCHAR(MAX);

SET STATISTICS TIME ON;

PRINT '********** Test NVARCHAR(4000) INSERT **********';
 SELECT TOP 1000000
        CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS NVARCHAR(4000)) AS N
   INTO #Test1
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.All_Columns ac2;

PRINT '********** Test NVARCHAR(MAX) INSERT **********';
 SELECT TOP 1000000
        CAST(ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS NVARCHAR(MAX)) AS N
      INTO #Test2
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.All_Columns ac2;

PRINT '********** Test NVARCHAR(4000) SELECT **********';
 SELECT @BitBucket1 = N FROM #Test1;

PRINT '********** Test NVARCHAR(MAX) SELECT **********';
 SELECT @BitBucket2 = N FROM #Test2;
 
 
PRINT '********** Test NVARCHAR(4000) UPDATE **********';
 UPDATE #Test1 SET N = N;

PRINT '********** Test NVARCHAR(MAX) UPDATE **********';
 UPDATE #Test2 SET N = N;



PRINT '********** Test NVARCHAR(4000) COMPARISON LIKE **********';
 SELECT @BitBucket1 = N FROM #Test1 where N Like '9900%'

PRINT '********** Test NVARCHAR(MAX) COMPARISON LIKE **********';
 SELECT @BitBucket2 = N FROM #Test2 where N Like N'9900%'


PRINT '********** Test NVARCHAR(4000) COMPARISON EQUALS **********';
 SELECT @BitBucket1 = N FROM #Test1 where N = '9900'

PRINT '********** Test NVARCHAR(MAX) COMPARISON EQUALS **********';
 SELECT @BitBucket2 = N FROM #Test2 where N = N'9900'
 
 
PRINT '********** Test NVARCHAR(4000) DELETE **********';
 DELETE #Test1

PRINT '********** Test NVARCHAR(MAX) DELETE **********';
 DELETE #Test2 

SET STATISTICS TIME OFF;

--===== Cleanup
   DROP TABLE #Test1, #Test2;

********** Test NVARCHAR(4000) INSERT **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 609 ms,  elapsed time = 1527 ms.

(1000000 row(s) affected)
********** Test NVARCHAR(MAX) INSERT **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 1750 ms,  elapsed time = 1889 ms.

(1000000 row(s) affected)

********** Test NVARCHAR(4000) SELECT **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 193 ms.
********** Test NVARCHAR(MAX) SELECT **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 328 ms,  elapsed time = 328 ms.

********** Test NVARCHAR(4000) UPDATE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 922 ms,  elapsed time = 957 ms.

(1000000 row(s) affected)
********** Test NVARCHAR(MAX) UPDATE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 2344 ms,  elapsed time = 2357 ms.

(1000000 row(s) affected)

********** Test NVARCHAR(4000) COMPARISON LIKE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 219 ms,  elapsed time = 219 ms.
********** Test NVARCHAR(MAX) COMPARISON LIKE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 509 ms.
********** Test NVARCHAR(4000) COMPARISON EQUALS **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 129 ms.
********** Test NVARCHAR(MAX) COMPARISON EQUALS **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 516 ms,  elapsed time = 511 ms.

********** Test NVARCHAR(4000) DELETE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 1687 ms,  elapsed time = 1776 ms.

(1000000 row(s) affected)
********** Test NVARCHAR(MAX) DELETE **********
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 1641 ms,  elapsed time = 1779 ms.

(1000000 row(s) affected)

Comments