How to Speed Up Populating Tables with a Large Amount of Random Data for SQL Server Performance Testing

May 11th, 2021

Very often, developers and QAs need to load a large amount of test data to assess the speed of populating the database with generated data and analyze how the performance changes with the database growth.

For example, this may be helpful to generate sample data for database load testing when there is a need in simulating the expected behavior of the application on the client-side environment or investigate how fast the application responds under a given workload or specified conditions. This can be achieved with the dbForge Data Generator for SQL Server tool aimed at populating SQL tables with random and baseline data by using the predefined and customized column-based 200+ meaningful SQL data generators.

In the article, we are going to learn how to accelerate the data population of SQL tables containing a large amount of data and evaluate the performance in cases the database volume increases. The article covers the following:

  • Defining the time period for data generation and the volume of data to be populated for a SQL table.
  • Automating the data population for recurring scenarios via the command-line interface.

What is Test Data Generation

Test data is data that can be used as the execution preconditions or inputs to verify the expected behavior of the program or application and ensure their quality. Test data can be generated manually, imported from the file, copied from production to the testing environment, or generated by using the data generation tool, such as dbForge Data Generator for SQL Server.

dbForge Data Generator for SQL Server is the fast and less time and resource consuming way to generate dummy data and populate the table with a large amount of data. The process consists of two stages:

  • Data generation from the selected SQL database that can be customized with the specified data generation options.
  • Data population into a SQL table that depends on the amount of the data generated at the previous stage.

For demo purposes, we use the AdventureWorks2014 database that includes 71 tables.

Performing fast data generation with dbForge Data Generator for SQL Server

To start with, you need to establish the SQL Server connection and configure data generation options. For this, on the Standard toolbar or on the Start page, click New Data Generation.

Launch the data generation in dbForge Data Generator for SQL Server

In the Data Generator Project Properties window that opens, set up the SQL Server connection, choose the database for data generation, and click Next. On the Options tab, under Row distribution mode, do either of the following:

  • Select the By generation of data by time option and specify the time period in seconds to define how long it takes to generate data for each table.

    This option allows you to calculate the different time required for data generation for databases with a different number of tables. In addition, clear the check box Truncate data from table before generation if you don’t want the selected table to be truncated.
Generating random test data by time

Note
Generation time won’t correspond to the specified time if there are tables with dependencies. In this case, the tool cannot generate more than the specified number of valid rows.

  • Select the By specified number of option and set the number of rows to be populated for the table.

    This option allows you to control the amount of generated data. However, we should mention that the tool will generate a different number of records for different tables due to various types of columns in the table.
Generating data by the specified number of rows

Also, it should be noted that the speed of data generation depends directly on the data type, i.e. generating string values is much slower than numeric values. However, if you need to use different data types for each subsequent iteration of data population, under Value distribution mode, select Random by timestamp for all tables.

Populating data with different data types

After setting the data generation options, click Open. In the SQL document that opens, select the tables and columns to populate, preview the data to be generated, customize the default column and file settings, and launch the data population. For more information about the data population of the SQL tables, see the Populating Data documentation.

Customizing the table and column settings and starting the data population with dbForge Data Generator for SQL Server

Result

As a result, we examined how long it took to load data for the cases when data generation time for each table equals 30:

  • It took 38 minutes to generate data.
  • It took 20 minutes to populate data.

Automating the data population via the command-line interface

After setting the data generation options for the project, you can save the data generator project settings to a .bat file. This will allows you to automate recurring data population scenarios.

To create a .bat file, follow the steps:

1. In the SQL document, select the tables and columns to be populated and customize the default settings on the right, if needed.

2. Click Populate data to the target database to launch the data population process.

Starting the data population in dbForge Data Generator for SQL Server

3. In the Data Population Wizard that opens, set the data population options (1) and then click Save Command Line (2).

Configuring data population settings in the Data Population Wizard

4. In the Command line execution file settings window that opens, verify and configure the settings to manage the *.bat file text.

Tip: Hover over the question mark icon to identify what each parameter means.

Configuring command-line file settings to create a .bat file in dbForge Data Generator for SQL Server

5. Verify that the command line settings are valid and click Validate.

6. To save the project settings to the .bat file, click Save.

After the .bat file was created, we can schedule the data generation task using a Windows Task Scheduler by following the steps:

1. Open the Control Panel > Administrative Tools and select Task Scheduler.

2. In the Task Scheduler window, under the Actions pane, click Create Basic Task to create a scheduled task.

Creating a basic task for data population with Windows Task Scheduler

3. In the Create Basic Task Wizard, enter the name and description of the task, and then click Next.

4. On the Trigger tab, select Daily to run the data population task one a day and then click Next.

Selecting the Daily option to run the data population task one a day

5. On the Daily subtab, indicate the time and 1 day for the recurring ask in the Recur every … days box and then click Next.

Specifying time and the number of days for the recurring task

6. On the Action tab, select Start a program to schedule a program to start automatically and then click Next.

Specifying the action for the data population task

7. On the Start a Program subtab, click Browse to select the .bat file we previously created via the command-line interface of the Data Population Wizard, and then click Next.

Also, you can specify the additional parameters:

  • Add arguments to execute the task with specific conditions.
  • Start in to add the folder in which the program will start.
Selecting the previously created .bat file containing project settings

8. On the Finish tab, verify the settings and click Finish.

Verifying the settings for the data population task

The Windows Task Scheduler will start the data population with the 30 seconds duration for each table at 10 am every day.

Conclusion

In the article, we have covered how easy and fast it is to generate test realistic data and checked how long it takes to load test data using dbForge Data Generator for SQL Server. To sum up, we would like to mention that the performance and speed of data population depend on the following parameters:

  • Speed of the PC (CPU and hard disk) on which the Data Generator tool is installed
  • Structures of the databases
  • Speed of the server on which the database is created
  • Data transfer rate between the PC and server
  • Settings of Data Generator

Download a free 30-day trial version of the dbForge SQL tool pack, which includes dbForge Data Generator for SQL Server, to evaluate the features and capabilities that will help you perform SQL Server tasks easily and effectively.

Download a trial version of the SQL tools pack
Leave a Comment