inserting 1 million records with sequence and select max

Question

i tried to insert 1 million rows in a test table using sequence and using the query (using select max) which i posted below. Surprisingly, the query with with select max created 1 million rows in 11:11 (11 mins 11 secs)

And the query with sequence create 1 million rows in 19:34 (19 mins 11 secs). now i am not sure which is good

Table : SitePage

CREATE TABLE [dbo].[SitePage](
    [PageID] [bigint] NOT NULL,
    [PageName] [nchar](50) NOT NULL,
 CONSTRAINT [PK_SitePage] PRIMARY KEY CLUSTERED 
(
    [PageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

here are the queries

1. Query to create Sequence

   create Sequence PageTableSequence
   START WITH 1
   INCREMENT BY 1
   NO CYCLE
   NO CACHE
   ;
   

2. Query to create 1 million records using Select max(id)

   DECLARE @intFlag INT
   SET @intFlag = 0
   Declare @maxrecords bigint
   set @maxrecords = 0
   while(@maxrecords<1000000)
   
   BEGIN
   WHILE (@intFlag =0)
   BEGIN
     BEGIN TRY
     Insert into SitePage (PageID, PageName) values ((Select max(PageID)+1  from SitePage),'Some Page Name');
     set @intFlag = @@rowcount
   
     END TRY
     BEGIN CATCH
     SET @intFlag=0
     END CATCH
   
   END
   set @maxrecords = @maxrecords+1
   set @intFlag=0
   END
   
   GO
   

3. Query using Sequence to insert 1 million records

   Declare @maxrecords bigint
   set @maxrecords = 0    
   
   while(@maxrecords<1000000)    
   
   BEGIN      
   
     Insert into SitePage (PageID, PageName) values (next value for PageTableSequence, 'some page name');
   
     set @maxrecords = @maxrecords+1
   
   END
   
   GO
   

why there is such a difference in sequence and select max

Answer 1

Try not using "NO CACHE", but use "CACHE 100", or even 1000 instead. This should give reduce IO and improve performance. See http://msdn.microsoft.com/en-us/library/ff878091.aspx