create 50,000 items.sql

基于微软的 ASP.NET+C#开发的PETSHOP(网上宠物店)项目,在性能及开发效率上明显优于基于SUN J2EE框架开发的PETSHOP. 项目包括所有源码及数据库建库脚本,是不错的学习 AS

/******************************************************************************
**        File: Create 50,000 Items.sql
**        Name: Benchmark Data Load
**        Desc: Adds 50,000 items to the Item table. Sample Output:
**
**        itemid     productid  listprice    unitcost     supplier    status attr1  
**        ---------- ---------- ------------ ------------ ----------- ------ ------------
**        EST-1      AV-CB-1    15.00        10.00        1           P      Attribute 1 
**
**        Date: 11/6/2001
**
*******************************************************************************/

-- SET NOCOUNT to ON and no longer display the count message
SET NOCOUNT ON

DECLARE @itemid char(10)
DECLARE @productid char(10)
DECLARE @listprice decimal(10, 2)
DECLARE @unitcost decimal(10, 2)
DECLARE @supplier int
DECLARE @status char(2)
DECLARE @attr1 varchar(80)
DECLARE @attr2 varchar(80)
DECLARE @attr3 varchar(80)
DECLARE @attr4 varchar(80)
DECLARE @attr5 varchar(80)

-- counters
DECLARE @petCategory int
DECLARE @nBirds int
DECLARE @nFish int
DECLARE @nCats int
DECLARE @nDogs int
DECLARE @nReptiles int

-- initialize counters
SELECT @petCategory = 1
SELECT @nBirds = 1
SELECT @nFish = 1
SELECT @nCats = 1
SELECT @nDogs = 1
SELECT @nReptiles = 1

DECLARE @i int
DECLARE @nProduct int
SELECT @i = 1
SELECT @nProduct = 1

PRINT 'Inserting into the Item table: ' + convert(varchar(255), getdate());

-- the case statements below are used to evenly distribute the new products
WHILE (@i <= 50000)
BEGIN    
    -- unique item id
    select @itemid = rtrim('EST-' + convert(varchar(10), @i))
   
    -- generate a unique ProductID
    select @productid = CASE
         WHEN @petCategory = 1 THEN rtrim('AV-CB-' + convert(varchar(10), @nBirds))
         WHEN @petCategory = 2 THEN rtrim('FI-FW-' + convert(varchar(10), @nFish))
         WHEN @petCategory = 3 THEN rtrim('FL-DLH-' + convert(varchar(10), @nCats))
         WHEN @petCategory = 4 THEN rtrim('K9-BD-' + convert(varchar(10), @nDogs))
         WHEN @petCategory = 5 THEN rtrim('RP-LI-' + convert(varchar(10), @nReptiles))
      END
        
      
    select @unitcost = convert(money, ((@nProduct % 4) +1) * 5)
    select @listprice = convert(money, @unitcost * 1.5) 
    select @supplier = 1
    select @status = 'P'
    select @attr1 = rtrim('Attribute ' + convert(varchar(10), @nProduct))
 
	-- insert the new product
    EXEC upItemAdd @itemid, @productid, @listprice, @unitcost, @supplier, @status, @attr1, @attr2, @attr3, @attr4, @attr5

    -- update the counters    	
    IF @petCategory = 1 SELECT @nBirds = @nBirds + 1
    IF @petCategory = 2 SELECT @nFish = @nFish + 1
    IF @petCategory = 3 SELECT @nCats = @nCats + 1
    IF @petCategory = 4 SELECT @nDogs = @nDogs + 1
    IF @petCategory = 5 SELECT @nReptiles = @nReptiles + 1
            	
    IF @petCategory % 5 = 0 
        SELECT @petCategory = 1
    ELSE
        SELECT @petCategory = @petCategory + 1
	
	-- we are only inserting 1,000 products, so we must keep the individual
	-- product variants in this range
	IF @i % 1000 = 0 
        BEGIN
          SELECT @nProduct = 1
          SELECT @nBirds = 1
          SELECT @nFish = 1
          SELECT @nCats = 1
          SELECT @nDogs = 1
          SELECT @nReptiles = 1
        END	
	
    -- output a progress message    
    IF @i % 1000 = 0 PRINT @i

    SELECT @i = @i + 1
END

-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF

PRINT 'Completed: ' + convert(varchar(255), getdate());