Creating a large table with random data for performance testing Part 61 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

so welcome to freedom technologies I am
venket this is part 61 of signal server
video series in this video we'll discuss
about inserting large amounts of random
data into sequel server tables for
performance testing in the next video
session we'll use these large tables to
test the performance of queries that
uses joins and sub queries let's flip to
sequel server management studio there
are three parts to the sequel script the
first part will actually check if the
tables exist TBL products and products
sales if the tables exist they will be
dropped and in the second part we
recreate those tables and the final part
will insert random sample data into
those tables let's see how we are
checking for the existence of those
tables and dropping them so to check the
existence of tables I'm using the system
table called information underscore
schema dot
see what this table contains so let's
execute this query select star from
information and score schema or tables
and look at this this query has returned
me a list of table names where are these
tables present these are tables are
present in the sample database because
we executed this query within the
context of the sample database I got all
the tables that are present within that
sample database and we are checking here
if the table name is product sales table
so do we have product sales table within
that information underscore schema or
tables yes and we are passing that to
exist function this exists function will
return true if the table exists
otherwise it's going to return false so
if this function returns true that means
the table is present we want to drop it
so drop table TBL product sales okay so
that will drop TBL product sales and
this query will drop TBL products table
so at the moment look at this we have
TBL product sales and products table let
me execute this query command completed
successfully now if i refresh this we
shouldn't see those tables so that's the
first part of the query it checks if the
tables exist and if they do it's going
to drop them the second part will
actually recreate those tables create
table products table and products sales
table okay and the important thing to
notice here is
product ID is a four in K so this is
referring the ID column in TBL products
table okay so I'm going to create TBL
products table and TBL products sales
table let's refresh this tables folder
here we should have those tables back
now okay
so the third and final step is to
actually insert sample data inserting
sample data into this tables is very
simple and straightforward so if you
look at TBL products table it has got
three columns within that ID name and
description ID column is the identity
column when we are creating the table we
be told ID is an identity column meaning
when we insert a row into this table TBL
products we don't have to supply a value
for the identity column we only need to
supply values for name and description
columns which are of type and we're cat
okay so the name is going to be
dynamically computed so the name of the
product you know since this is a random
data I'm going to call my products as
product one product to product thousand
etc similarly a product description is
going to be product one description
product two description etc and how we
are calculating the values for those
names look at that I am doing this in a
while loop so while we declared a
variable of type integer at ID and the
ID is initialized to 1 so while that ID
is less than or equal to 10,000 which
means we are going to loop through this
10,000 times I'm going to insert 10,000
products into TBL products table so what
we are doing insert into table products
values so first we need to supply the
value for name column so this is the
hard coded text product - and to that
what we are doing we are concatenating
the ID value first time the ID value
will be 1 so 1 we are converting that to
an N where care because ID is an integer
variable but where since we are
concatenated that to another string we
need to typecast that to be of type and
worker which is what we are doing here
so that will give me product in a value
for the name column product one and this
one will give me product one description
which is going to be the value for
description column the same thing
product that
the hard-coded string to that we are
concatenated ID which is going to be one
the first time and then another
hard-coded string description okay so
this is going to be the value for
description column this is going to the
value for name column and we have this
insert statement inside this while loop
okay so it's going to run 10,000 times
and look at this we are printing ID this
is for you know you know tracking the
progress when the compendious query is
running and finally we are incrementing
the value by one this is most important
otherwise the loop will never end at
some point of time we want the ID to be
10,000 so that the loop can end
otherwise this will become a
never-ending loop okay so let's go ahead
and run this query and see what happens
so execute query executing query look at
the messages look at the rows are being
inserted ok 5,000 6,000 the query has
just completed successfully so when we
scroll down you should see 10,000 rows
now let's select data and see if it is
present so select star from TBL products
press f5 look at that here I get 10,000
rows in the status bar and look at the
name and description product one is the
name product one description is the
description ok so the next app is to
actually insert random data into TBL
product sales table now this is a bit
you know I wouldn't say it's complex
because here we have one two three
columns so this TBL product sales table
has again got total four columns within
that ID is again the identity column so
we don't have to supply a value for that
but whereas we have to supply values for
Product ID unit price and quantity sold
columns as you remember product ID is a
foreign key this value must match with
one of the values that are present you
know in TBL products sales table a
products table for this ID column so it
has to match up with that so if you
remember within TBL products we have ids
between 1 and 10,000 ok so when i insert
a row into this TBL product sales table
product ID has to be a number between 1
and 10
okay unit price can be any random number
quantity sold can also be any random
number but then unit price and quantity
sold I'm going to have a random number
between 1 and 10 but whereas product ID
will be a random number between 1 and
10,000 it can't fall outside of those
values that's because it's a foreign key
to the ID column in TBL products table
ok so to understand this all you have to
do is I mean we are going to use again
while loop look at that
so while and I'm going to insert 15,000
rows because I might have sold one
product maybe ten times it's possible
right so we are going to have 15,000
rows within TBL product sales table
look at this again insert into TBL
product sales I have this random product
ID random unit price and random quantity
sold
you know variables so what are these
these are the variables and what are
these variables we are using round and
round functions to generate a random
product ID random unit price and random
quantity so okay let's see how we are
generating and if you remember we spoke
about this the product ID should be a
random number between 1 and 10,000
whereas unit price and quantity sole
should be a random number between 1 and
10 now let's see how to generate a
random number using round and round
functions and that's very simple to do
all you have to do is know how to use
this land and round function we have
spoken about these random and round
functions in the previous sessions of
this video series please check those if
you haven't watched them already so
first let's see how to actually compute
the values let's say I want you to write
a script which will generate random
numbers between a given range for
example I want to generate a random
number between 1 and 10 so I'm going to
declare a variable of type integer at ll
lower limit and I'm going to set that to
1 so my lower limit is going to be 1 and
along the same lines I'm going to create
another variable of type integer so
declare at upper limit and that's going
to be integer I want to set that let's
say
five let's say I want to generate random
numbers between one and five okay so I
have created two variables of type
integer initialize them to one and five
so my lower limit is going to be one and
upper limit is going to be five so how
do i generate a random number between
one and five so I'm going to use this
land function for that but then look at
this what I'm going to do first I am
going to subtract from the upper limit
the lower limit okay and then I'm going
to multiply that with R and function
okay so random function okay let's
execute this as it is
okay let's execute this entire code if
you look at this I get a random number
look at that
i execute 0.9 i got 1 3 1 3 3 0 I don't
want 0 I want a number between one end
and all five so but with this piece of
code I'm getting number 0 1 2 3
you know 4 but I'm not getting 5 and I'm
getting 0 so what I basically can do to
this add 1 okay so now let's execute all
this together now I should always get a
number between 1 2 3 4 and 5 but then I
have decimal places we'll we'll get rid
of those decimal places in just a bit so
how to get rid of those decimal places
use their own function that's what their
own function is for so I'm going to use
the round function so which is the
number I want to round I want to round
this number okay whatever is returned by
this expression I want to round that
number and how many decimal places I
want I want zero decimal places that's
it so now let's execute this all
together because I get 4 2 1 3 etc just
to make sure that it's working you know
according to our expectations you know
let's put in a simple testing code for
this I want to quickly test this so I'm
going to use you know while a while loop
here while let's say 1 is equal to 1
just to convince you guys it always
generate numbers between 1 and 5 so
while one is equal to 1 that means this
condition will always
true I'm creating a never-ending loop
here so while one is equal to one begin
end so what do I want to do I want to
generate this random number in an
infinite loop okay
so generate random number so to hold
that number what I'm going to do I'm
going to take it declare a variable so
declare at R and I'm going to say
integer so let's take that variable so
I'm saying at random so we are
initializing this variable with whatever
random number we get and print that so
print that random number so we can see
it so print that random number and then
what I'm going to say I'm going to use a
if condition here if that at random
number is less than 0 so it cannot be
less than less than 1 it cannot be less
than 1 because we want a random number
between 1 and 5 so if it is less than 0
or less than 1 or if that random number
is greater than 5 that means our code is
wrong so what I'm going to do here I'm
going to have another begin and end I'm
going to print saying error and we want
to see what is that number that is
generated so I'm going to okay so
anytime if it comes to this you know if
block we know that that number is less
than 1 or it is greater than 5 in which
case our script this piece of script
which is generating the random number is
incorrect so what we are going to do
here we are going to cast that and print
that incorrect number so that's an
integer so I'm going to cast that to be
of type and we're care maybe 4 ok so
let's execute the script so if we
execute the script now it should never
end because I am confident this piece of
script here is going to always generate
a number between 1 and 5 it is never
going to be less than 1 or greater than
5 and what I want to do is after we
print we want to break out of the loop
okay so so if it comes here we know for
sure it prints error message and it
makes out of this while loop if it is
not coming out of that while loop then
we know for sure it's always going to
generate numbers between 1 and 5 a quick
test basically so I execute that look at
this executing query it's it's still
executing it's more than 5 seconds now
so if I go to the messages look at this
it's it's printing those numbers and
it's never going to end we have to
cancel that at some point of time look
at this it's still executing let me
cancel that okay and if you look at
these numbers these always will be
between 1 2 3 4 & 5 I mean between 1 & 5
basically okay now just to make this a
little bit interesting I'm going to say
we're at random is greater than or equal
to 5 now if we execute this look at that
we got 4 4 2 3 at some point of time it
ended okay now let me execute that look
at that to five and then error okay so
it's working it's only generating the
numbers between 1 & 5 so our script is
working as expected so we have just seen
how to generate random numbers between a
given range we are creating declaring
two variables
initializing them to one and five and it
is this simple piece of code which
generates random number between a given
range okay
so if we have understood that the rest
of the script should be very simple
forward straight forward so to insert
data into TBL product sales table we
need values for Product ID unit price
and quantity sold all of them of type
integers okay so I want so I have
created two variables upper limit for
Product ID lower limit for Product ID
now the lower limit for Product ID is
going to be one and upper limit is going
to be 8500 you can make it 10,000 if you
want but I have just chosen 8500 because
when I do a left to join you know if if
I I basically want to use this for the
next video session to demonstrate the
performance of sub queries and joins
okay so I don't want all the products in
TBL products sales table so I'm going to
have 1500 of them not present in TBL
product sales table okay and the unit
price you know is going to be a number
between 1 and 100 or it can be 1 and
and it's up to you and the same is the
quantity quantities again one and ten
okay so these are all declaration of
variables and initializing them and
finally what we are doing we are having
a while loop here which starts at one
and loops through 15,000 times so we're
going to have 15,000 records inserted
and obviously what is this script doing
here it is generating a random product
ID random unit price and random quantity
sold it's exactly the same thing that we
have seen how to Jen you know just now
how to generate a random number okay and
then we are inserting those you know
values into this table and we are
printing the counter incrementing the
counter by one okay so let's execute
this script and see if it inserts 15,000
rows so executing query so in the
messages you should see it's still
executing four thousand four thousand
nine hundred nine thousand four hundred
twelve thousand four hundred and query
completed successfully fifteen thousand
so now if we go back to the tables we
should see ten thousand records in
products and ten thousand
I mean fifteen thousand records in TBL
products sales table so look at that
10,000 and TBL products 15,000 and TBL
products sales table so if you look at
TBL products sales table we have product
ID unit price and quantity sold some
random numbers okay so we have seen how
to insert ten thousand and fifteen
thousand rows but if you want you can
even insert 1 million two million rows
all you have to change is the number for
this wire loop that's about it okay on
this slide you can find resources for
asp.net c-sharp and sequence of
interview questions that's it for today
thank you for listening have a great day