Very fast test data generation using exponential INSERT

Introduction

This article (my first) will describe an algorithm that enables a large amount of data to be generated very quickly using a SQL query. The test data can be static or incremental, such as “Item Name” and “Item ID”, respectively, as shown below:

tableSample.png

Background

One of the tasks I did in a project involves generating a testing table with 103,680,000 records. The conventional method of data generation would take a month; hence, a fast method of data insertion was required. The new method took only 5 hours.

Using the code

Conventional method – Sequential INSERT

The conventional way of generating a list of numbers from 0…100000 would be using a loop and an INSERT statement as follows:

CREATE TABLE #tempTable([Item ID] [bigint], [Item Name] nvarchar(30))
DECLARE @counter int SET @counter = 1 WHILE (@counter < 100000) BEGIN         INSERT INTO #tempTable VALUES (@counter, 'Hammer')         SET @counter = @counter + 1 END SELECT * FROM #tempTable DROP TABLE #tempTable

Let's call this method of data generation “Sequential INSERT”.

New method – Exponential INSERT

The new method effectively makes a copy of the existing data and appends it as new data, and does so repeatedly until the desired amount of data is generated.

Here is the code for the exponential INSERT:


CREATE TABLE #tempTable([Item ID] [bigint], [Item Name] nvarchar(30)) INSERT INTO #tempTable VALUES (1, 'Hammer') WHILE((SELECT COUNT(*) FROM #tempTable) < 100000) BEGIN     INSERT INTO #tempTable ([Item ID], [Item Name])         (SELECT [Item ID] + (SELECT COUNT(*) FROM #tempTable),                                   'Hammer' FROM #tempTable) END SELECT * FROM #tempTable DROP TABLE #tempTable

Points of interest

The condition for the WHILE..LOOP is (SELECT COUNT(*)). This condition statement takes a long time to be evaluated. A faster method would be to calculate how many iterations are needed to generate the desired number of records, i.e., 100,000 records in this case, which is 2^17=131,072, so we can rewrite the code to stop after the 17th iteration.

It took 4 seconds to execute the number count from 1 to 100,0000; the exponential method took two seconds with the code below:


See full detail: http://www.codeproject.com/KB/database/ExponentialSQLGenerator.aspx

Comments

Post a Comment

Popular posts from this blog

Asynchronous Socket Programming in C#

Image
Objective The objective of this article is to demonstrate a socket-based client/server application that will allow two-way asynchronous communication between a server and multiple client applications. Because this example uses asynchronous methods, the server application does not use threads to communicate to multiple clients (although internally the asynchronous communication mechanism uses threads at the OS level). The Difference Between Synchronous and Asynchronous Communication in Network Programming The key difference between synchronous and asynchronous communication can be explained with an example. Consider a server application that is listening on a specific port to get data from clients. In synchronous receiving, while the server is waiting to receive data from a client, if the stream is empty the main thread will block until the request for data is satisfied. Hence, the server cannot do anything else until it receives data from the client. If another clien
Read more

Url Routing MVC TUTORIAL

Image
This tutorial covers an introduction to the MVC pattern using Visual Basic. It also includes a brief overview of the URL routing assumptions that are a foundation to the implementation of the pattern. A sample application is created to demonstrate the fundamentals of this new web-application model. Implementing business rules and testing is also covered. Finally, the use of two alternative view engines, Silverlight and XML Literals, is coverd.
Read more

WCF Chat Sample

Image
The Chat sample demonstrates how to implement a multiparty chat application by using PeerChannel. Messages sent by any instance of a chat application are received by all other instances. To get samples and instructions for installing them Do one or more of the following: On the Help menu, click Samples . The Readme displays information about samples. Visit the Visual Studio 2008 Samples Web site. The most recent versions of samples are available there. Locate samples on the computer on which Visual Studio is installed. By default, samples and a Readme file are installed in drive :\Program Files\Microsoft Visual Studio 9.0\Samples\ lcid . For Express editions of Visual Studio, all samples are located online. For more information, see Locating Sample Files . Security Note: This sample code is intended to illustrate a concept, and it shows only the code that is relevant to that concept. It may not meet the security requirements for a specific environment, a
Read more