Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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.

6 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
    Replies
    1. approx 1 min 48 seconds from above code,and it depends on values

      Delete

It would be great if you can help share these free resources