Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Creating a large table with random data for performance testing - Part 61

Suggested Videos
Part 58 - Transaction Acid Tests
Part 59 - Subqueries
Part 60 - Correlated subquery

In this video we will discuss about inserting large amount of random data into sql server tables for performance testing. 



-- If Table exists drop the tables
If (Exists (select
            from information_schema.tables 
            where table_name = 'tblProductSales'))
Begin
Drop Table tblProductSales
End

If (Exists (select
            from information_schema.tables 
            where table_name = 'tblProducts'))
Begin
Drop Table tblProducts
End



-- Recreate tables
Create Table tblProducts
(
[Id] int identity primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)

Create Table tblProductSales
(
Id int primary key identity,
ProductId int foreign key references tblProducts(Id),
UnitPrice int,
QuantitySold int
)

--Insert Sample data into tblProducts table
Declare @Id int
Set @Id = 1

While(@Id <= 300000)
Begin
Insert into tblProducts values('Product - ' + CAST(@Id as nvarchar(20)), 
'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')

Print @Id
Set @Id = @Id + 1
End

-- Declare variables to hold a random ProductId, 
-- UnitPrice and QuantitySold
declare @RandomProductId int
declare @RandomUnitPrice int
declare @RandomQuantitySold int

-- Declare and set variables to generate a 
-- random ProductId between 1 and 100000
declare @UpperLimitForProductId int
declare @LowerLimitForProductId int

set @LowerLimitForProductId = 1
set @UpperLimitForProductId = 100000

-- Declare and set variables to generate a 
-- random UnitPrice between 1 and 100
declare @UpperLimitForUnitPrice int
declare @LowerLimitForUnitPrice int

set @LowerLimitForUnitPrice = 1
set @UpperLimitForUnitPrice = 100

-- Declare and set variables to generate a 
-- random QuantitySold between 1 and 10
declare @UpperLimitForQuantitySold int
declare @LowerLimitForQuantitySold int

set @LowerLimitForQuantitySold = 1
set @UpperLimitForQuantitySold = 10

--Insert Sample data into tblProductSales table
Declare @Counter int
Set @Counter = 1

While(@Counter <= 450000)
Begin
select @RandomProductId = Round(((@UpperLimitForProductId - @LowerLimitForProductId) * Rand() + @LowerLimitForProductId), 0)
select @RandomUnitPrice = Round(((@UpperLimitForUnitPrice - @LowerLimitForUnitPrice) * Rand() + @LowerLimitForUnitPrice), 0)
select @RandomQuantitySold = Round(((@UpperLimitForQuantitySold - @LowerLimitForQuantitySold) * Rand() + @LowerLimitForQuantitySold), 0)

Insert into tblProductsales 
values(@RandomProductId, @RandomUnitPrice, @RandomQuantitySold)

Print @Counter
Set @Counter = @Counter + 1
End

Finally, check the data in the tables using a simple SELECT query to make sure the data has been inserted as expected.
Select * from tblProducts
Select * from tblProductSales

In our next video, we will be using these tables, for performance testing of queries that uses subqueries and joins.

5 comments:

  1. Hi Venkat, thank you for the videos its great resources full for me

    i been trying to run the queries taking from ur blog with changes from my end, however for this part of video i been able to fill in details for product tables by random but am not able to fill in details for sales table getting error message, below is the query i copied from this blog and edited as mentioned in the video

    -- Declare variables to hold a random ProductId,
    -- UnitPrice and QuantitySold
    declare @RandomProductId int
    declare @RandomUnitPrice int
    declare @RandomQuantitySold int

    -- Declare and set variables to generate a
    -- random ProductId between 1 and 8500
    declare @UpperLimitForProductId int
    declare @LowerLimitForProductId int

    set @LowerLimitForProductId = 1
    set @UpperLimitForProductId = 8500

    -- Declare and set variables to generate a
    -- random UnitPrice between 1 and 100
    declare @UpperLimitForUnitPrice int
    declare @LowerLimitForUnitPrice int

    set @LowerLimitForUnitPrice = 1
    set @UpperLimitForUnitPrice = 100

    -- Declare and set variables to generate a
    -- random QuantitySold between 1 and 10
    declare @UpperLimitForQuantitySold int
    declare @LowerLimitForQuantitySold int

    set @LowerLimitForQuantitySold = 1
    set @UpperLimitForQuantitySold = 10

    --Insert Sample data into tblProductSales table
    Declare @Counter int
    Set @Counter = 1

    While(@Counter <= 15000)
    Begin
    select @RandomProductId = Round(((@UpperLimitForProductId - @LowerLimitForProductId) * Rand() + @LowerLimitForProductId), 0)
    select @RandomUnitPrice = Round(((@UpperLimitForUnitPrice - @LowerLimitForUnitPrice) * Rand() + @LowerLimitForUnitPrice), 0)
    select @RandomQuantitySold = Round(((@UpperLimitForQuantitySold - @LowerLimitForQuantitySold) * Rand() + @LowerLimitForQuantitySold), 0)

    Insert into tblproductsales1
    values(@RandomProductId, @RandomUnitPrice, @RandomQuantitySold)

    Print @Counter
    Set @Counter = @Counter + 1
    End

    ReplyDelete
  2. Dear Mr Hindu,
    Follow the steps below to populate tables.
    Note my table names are ProdSales and Prod.

    Run one step at a time and check for the operation carried.

    If you have any questions please feel free to let me know.

    Thanks, Kris.Maly@Gmail.com
    ============SCRIPT STARTS==================================
    -- PLEASE NOTE my table names are "tblProdSales" and "tblProd"
    --STEP 1 Checking and DROPPING Tables
    --====================================
    -- Script to create Tables and insert large number of records
    -- If Table Exists then drop and recreate
    ------------------------------------------
    -- select TOP 2 * from tblProdSales
    -- select TOP 2 * from tblProd

    If (Exists (select *
    from information_schema.tables
    where table_name = 'tblProdSales'))
    Begin
    Drop Table tblProdSales
    End

    If (Exists (Select *
    From information_Schema.tables
    Where table_name = 'tblProd' ))
    Begin
    Drop Table tblProd
    End

    --STEP 2 Creating Tables
    --==========================
    Create Table tblProd
    (
    [Id] int identity primary key,
    [Name] nvarchar(50),
    [Description] nvarchar(250)
    )

    Create Table tblProdSales
    (
    [Id] int identity primary key,
    [ProductId] int foreign key references tblProd,
    [UnitPrice] int,
    [QuantitySold] int
    )

    --STEP 3 Poulating Prod table
    --==============================
    --select count(*) from tblProdSales
    --select count(*) from tblProd

    -- Insert sample data into tblProd table
    Declare
    @Id int
    Set @Id = 1

    While (@Id <= 10000)
    Begin
    Insert into tblProd
    Values ('Prod - ' + CAST(@Id as nvarchar(20)),
    'Prod - ' + CAST(@Id as nvarchar(20)) + 'Description')

    Print @Id
    Set @Id = @Id + 1
    End

    --STEP 4 Polulating ProdSales table
    --==================================
    -- Declare variables to hold a random ProdId, UnitPrice, QuantitySold
    Declare @RandProdId int
    Declare @RandUnitPrice int
    Declare @RandQuantitySold int

    -- Declare and Set variables to generate a random ProductId between 1 and 10,0000
    Declare @ULForProdId int
    Declare @LLForProdId int

    Set @ULForProdId = 1
    Set @LLForProdId = 8500

    -- Declare and Set variables to generate a random UnitPrice between 1 and 100
    Declare @ULForUnitPrice int
    Declare @LLForUnitPrice int

    Set @ULForUnitPrice = 1
    Set @LLForUnitPrice = 100

    -- Declare and Set variables to generate a random QuantitySold between 1 and 10
    Declare @ULForQuantitySold int
    Declare @LLForQuantitySold int

    Set @ULForQuantitySold = 1
    Set @LLForQuantitySold = 10

    -- Insert sample data in to ProdSales table
    Declare @Cnt int
    Set @Cnt = 1

    While (@Cnt <=15000)
    Begin
    Select @RandProdId = Round(((@ULForProdId - @LLForProdId) * RAND() + @LLForProdId), 0)
    Select @RandUnitPrice = Round(((@ULForUnitPrice - @LLForUnitPrice) * RAND() + @LLForUnitPrice), 0)
    Select @RandQuantitySold = Round(((@ULForQuantitySold - @LLForQuantitySold) * RAND() + @LLForQuantitySold), 0)

    Insert into tblProdSales
    Values(@RandProdId, @RandUnitPrice, @RandQuantitySold )

    print @Cnt
    Set @Cnt = @Cnt + 1
    End

    --STEP 5 Checking the Counts in the tables
    --=========================================
    select count(*) from tblProd
    select count(*) from tblProdSales

    --STEP 6 Checking the Records data
    --=================================
    select TOP 10 * from tblProd
    select TOP 10 * from tblProdSales

    ============SCRIPT ENDS==================================

    ReplyDelete
  3. This is exactly what i needed, thank you :)

    ReplyDelete
  4. What if the tblProdSales has a Parent table called tblProdSalesHeader?

    ReplyDelete
  5. how much time these random queries will take to execute

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.