Browse DevX
Sign up for e-mail newsletters from DevX


Speed Up Your SQL Inserts : Page 3

Traditionally, database performance has been represented by the speed of data retrieval, but that is not always the case. In some situations, database performance relies on the speed of data inserts.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:

  1. Create and load the table testInserts, if you don't have it yet (see Listing 1, Batch 1). Run the script in Listing 3.
  2. Create the test table t3. Create the stored procedure spu_insertStrings (Listing 4).
  3. 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 GO SET QUOTED_IDENTIFIER OFF spu_InsertStrings "a=1,b=10249x2,10251x . . . . . x249,11071x250,10250" GO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . spu_InsertStrings "a=3251,b=44050x3252,44053x . . . x3319,44289x3320,44292" GO

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.)

Thanks for your registration, follow us on our social networks to keep up-to-date