title: getdate() performance issue sql server 2000
body: <p>I have a table with fallowing columns</p>
  
  <pre><code>symbol |market| bid_price | ask_price | update_time
  ------- ------ ----------- ----------- ------------
  ABC        US     123.00     675.00         20012-09-10 4:24:32.986
  CDE        SG     456.00     545.00         20012-09-10 4:26:32.986
  </code></pre>
  
  <p>And my application calling a SP for update this table.</p>
  
  <pre><code>update price_tbl 
  set bid_price=@bid_price, ask_price=@ask_price, update_time = getdate() 
  where market = @market and symbol = @symbol
  </code></pre>
  
  <p>But my application calling more than 1000 updates per second. so this SP not fast enough to update the table. I checked and found getdate() function is the bottleneck. but this system running with  sql server 2000. </p>
  
  <ol>
  <li>Can I use any other method to update time other than getdate() Any</li>
  <li>other proposal for increase performance of this update.</li>
  </ol>
  
  <p>FYI: this price_tbl having around 2000 records. </p>
  
  <hr>
  
  <p>Eddied with test result.</p>
  
  <p>Table Definition
  ..........................................................................................</p>
  
  <pre><code>CREATE TABLE [dbo].[GLDPrice](
      [Company_code] [varchar](10) NOT NULL,
      [Symbol] [varchar](10) NOT NULL,
      [SymbolA] [varchar](10) NULL,
      [SymbolB] [varchar](10) NULL,
      [Market] [char](2) NOT NULL,
      [ExchangeCode] [varchar](4) NULL,
      [Remark] [char](6) NULL,
      [Last_done] [numeric](19, 8) NULL,
      [Change] [numeric](19, 8) NOT NULL,
      [Open_Price] [numeric](19, 8) NULL,
      [Closing_Price] [numeric](19, 8) NULL,
      [Buy_Price] [numeric](19, 8) NULL,
      [Sell_Price] [numeric](19, 8) NULL,
      [Day_High] [numeric](19, 8) NULL,
      [Day_Low] [numeric](19, 8) NULL,
      [Time_done] [char](5) NULL,
      [Cumm_vol] [int] NOT NULL,
      [Buy_quantity] [int] NULL,
      [Sell_quantity] [int] NULL,
      [Per_Change] [numeric](19, 8) NULL,
      [GLDBid] [numeric](19, 8) NULL,
      [GLDAsk] [numeric](19, 8) NULL,
      [GlobalGLDBid] [numeric](19, 8) NULL,
      [GlobalGLDAsk] [numeric](19, 8) NULL,
      [GLDBuyLastDone] [numeric](19, 8) NULL,
      [GLDSellLastDone] [numeric](19, 8) NULL,
      [GLDBuyLDUptTime] [datetime] NULL,
      [GLDSellLDUptTime] [datetime] NULL,
      [UpdateTime] [datetime] NOT NULL,
   CONSTRAINT [PK_GLDPrice] PRIMARY KEY CLUSTERED 
  (
      [Company_code] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  SET ANSI_PADDING OFF
  GO
  ALTER TABLE [dbo].[GLDPrice] ADD  CONSTRAINT [DF_GLDPrice_SymbolA]  DEFAULT (' ') FOR [SymbolA]
  GO
  ALTER TABLE [dbo].[GLDPrice] ADD  CONSTRAINT [DF_GLDPrice_Last_done]  DEFAULT (0) FOR [Last_done]
  GO
  ALTER TABLE [dbo].[GLDPrice] ADD  DEFAULT (getdate()) FOR [UpdateTime]
  GO
  </code></pre>
  
  <p>SP with getdate()
  ..........................................................................................</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[update_test_one]      
   @Symbol varchar(10),      
   @Market varchar(5),       
   @BuyPrice money,      
   @SellPrice money,      
   @LastPrice money,      
   @High  money,      
   @Low  money      
  
  AS      
  DECLARE @GLDBidPrice money      
  DECLARE @GLDAskPrice money      
  DECLARE @GlobalGLDBid  money      
  DECLARE @GlobalGLDAsk  money      
  DECLARE @GLDBidAdjust money      
  DECLARE @GLDAskAdjust money      
  DECLARE @GlobalBidAdjust money      
  DECLARE @GlobalAskAdjust money    
  
  SELECT @GLDBidPrice = @BuyPrice + 5      
  SELECT @GLDAskPrice = @SellPrice + 5      
  SELECT @GlobalGLDBid = @BuyPrice + 5      
  SELECT @GlobalGLDAsk = @SellPrice + 5
  
  UPDATE dbo.GLDprice      
  SET Buy_price = @BuyPrice,      
  Sell_price = @SellPrice,      
  GLDBid = @GLDBidPrice,      
  GLDAsk = @GLDAskPrice,      
  Day_high = @High,      
  Day_Low = @Low,      
  GlobalGLDBid = @GlobalGLDBid,      
  GlobalGLDAsk = @GlobalGLDAsk,    
  UpdateTime=GetDate(),
  Last_Done = @LastPrice    
  WHERE  Symbol = @symbol AND Market = @Market
  </code></pre>
  
  <p>SP without getdata()
  ............................................................................................</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[update_test_two]      
   @Symbol varchar(10),      
   @Market varchar(5),       
   @BuyPrice money,      
   @SellPrice money,      
   @LastPrice money,      
   @High  money,      
   @Low  money      
  
  AS      
  DECLARE @GLDBidPrice money      
  DECLARE @GLDAskPrice money      
  DECLARE @GlobalGLDBid  money      
  DECLARE @GlobalGLDAsk  money      
  DECLARE @GLDBidAdjust money      
  DECLARE @GLDAskAdjust money      
  DECLARE @GlobalBidAdjust money      
  DECLARE @GlobalAskAdjust money    
  
  SELECT @GLDBidPrice = @BuyPrice + 5      
  SELECT @GLDAskPrice = @SellPrice + 5      
  SELECT @GlobalGLDBid = @BuyPrice + 5      
  SELECT @GlobalGLDAsk = @SellPrice + 5
  
  UPDATE dbo.GLDprice      
  SET Buy_price = @BuyPrice,      
  Sell_price = @SellPrice,      
  GLDBid = @GLDBidPrice,      
  GLDAsk = @GLDAskPrice,      
  Day_high = @High,      
  Day_Low = @Low,      
  GlobalGLDBid = @GlobalGLDBid,      
  GlobalGLDAsk = @GlobalGLDAsk,    
  Last_Done = @LastPrice    
  WHERE  Symbol = @symbol AND Market = @Market
  </code></pre>
  
  <p>Test Script
  ...........................................................................................</p>
  
  <pre><code>DECLARE @return_value int
  DECLARE @count int
  DECLARE @start datetime
  
  
  SET NOCOUNT ON
  SET @count = 0;
  set @start = CURRENT_TIMESTAMP
  
  WHILE (@count &lt; 10000)
  BEGIN
  SET @count = @count + 1
  EXEC    [dbo].[update_test_one]
          @Symbol = N'I9T',
          @Market = N'SG',
          @BuyPrice = 0.8,
          @SellPrice = 0.8,
          @LastPrice = 0.8,
          @High = 0.8,
          @Low = 0.8
  
  EXEC    [dbo].[update_test_one]
          @Symbol = N'0001.HK',
          @Market = N'HK',
          @BuyPrice = 112,
          @SellPrice = 112,
          @LastPrice = 112,
          @High = 112,
          @Low = 112
  END
  
  print 'Test 01 : ' + CONVERT(varchar(20),DATEDIFF(millisecond,@start,CURRENT_TIMESTAMP))
  
  SET @count = 0;
  set @start = CURRENT_TIMESTAMP
  WHILE (@count &lt; 10000)
  BEGIN
  SET @count = @count + 1
  EXEC    [dbo].[update_test_two]
          @Symbol = N'I9T',
          @Market = N'SG',
          @BuyPrice = 0.8,
          @SellPrice = 0.8,
          @LastPrice = 0.8,
          @High = 0.8,
          @Low = 0.8
  
  EXEC    [dbo].[update_test_two]
          @Symbol = N'0001.HK',
          @Market = N'HK',
          @BuyPrice = 112,
          @SellPrice = 112,
          @LastPrice = 112,
          @High = 112,
          @Low = 112
  END
  
  print 'Test 02 : ' + CONVERT(varchar(20),DATEDIFF(millisecond,@start,CURRENT_TIMESTAMP))
  GO
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Test 01 : 82310
  Test 02 : 12176
  </code></pre>
  
  <p>Result with reverse test.</p>
  
  <pre><code>Test 02 : 15413
  Test 01 : 81636
  </code></pre>