How to Make Your Inserts Faster
To make your inserts faster, the obvious solution is replacing the repetitive one-row inserts with many-rows inserts. Using the example in Listing 2
, this section demonstrates how to do that.
To trace the inserts, I created the INSERT trigger on the table tmpInserts. Every time a trigger is fired, it just prints the word Hello. To transform one-row inserts into many-rows inserts, I ran an
INSERT... SELECT statement, where the SELECT part consists of many simple SELECT statements connected by UNION (ALL). I placed everything in the string variable and executed it dynamically. As you can see, for row-by-row inserts, the trigger was fired as many times as inserts I made (three, in this example). For the many-rows insert, the trigger was fired only once.
So, how can you apply this inserts technique to an application for a control measuring system or a Web site (e.g., a survey site with very high volume of transactions)? Well, when a user submits the form, the application (Web) server receives it as a sequence of name-value pairs, corresponding to the controls (elements) on the form. All you need to do now is slightly modify that sequence and forward it to a database server, which will take care of the inserts.
The examples in Listing 3 and Listing 4 show how the string, transferred to a database server, should look and how it can be processed and inserted into the table(s).
I used the table testInserts that I created and loaded in Listing 1 (Batch 1). The value of the variable
@numElements defines the number of name-value pairs, which is the length of the string that will be generated. The letter x serves as a placeholder. (I'll explain its purpose later.)
Listing 4 is a stored procedure that will process and insert data submitted to a database server.
Here's the whole trick. You need to replace each placeholder (x) in the string-parameter with the phrase UNION ALL SELECT, and then execute this modified string. Now you can test the solution as follows:
- Create and load the table testInserts, if you don't have it yet (see Listing 1, Batch 1).
Run the script in Listing 3.
- Create the test table t3. Create the stored procedure
spu_insertStrings (Listing 4).
- Copy and paste the result of step 2 into the new Query Analyzer (Management Studio) window. You will get something like the following script:
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
spu_InsertStrings "a=1,b=10249x2,10251x . . . . . x249,11071x250,10250"
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
spu_InsertStrings "a=3251,b=44050x3252,44053x . . . x3319,44289x3320,44292"
Don't forget to include two SET statements in the very beginning of the script for NOCOUNT and QUOTED_IDENTIFIER. Then run the script and make a note of the execution time.
Using the string-inserts technique, I was able to insert 3,320 rows into the table t3 in 2 seconds. That was 18 times faster than in the repetitive row inserts. (These numbers relate to SS2000. With SS2005, I saw improvement in the 60-70 percent range.)