17

I am performing some test on sql server and I want to get the best insert speed possible. The statement I use is something like this: 

INSERT INTO db_Test_databse..tbl_test with(rowlock)  
   (  joinid, date_key,   
      col1, col2,col3,col4, col5, col6, col7, col8, col9, col10,  ...     
   )  

SELECT  tabid as joinid, 
        date_key,
        rec_type,  
        col2,  
        dbo.udf_convert_hex_to_dec(col3),  
        col4, col5, col6, col7, col8,  
        dbo.udf_convert_hex_to_dec(col9),  
        dbo.udf_convert_hex_to_dec(col10),  
        ...
from source_table f

There are 25 columns; most of them are of type bigint or int.

I dropped all indexes from the destination table except the primary key which is an identity field.

Any tips on how to improve the performance more?

P.s. In this form I have an average speed of 16.000 rows / sec.

Sanushi Salgado
  • 1,195
  • 1
  • 11
  • 18
Corovei Andrei
  • 1,646
  • 6
  • 28
  • 42
  • `dbo.udf_convert_hex_to_dec` is killing you. Which version of SQL Server are you using? There's built-ins to do this conversion. – Yuck Aug 17 '11 at 09:05
  • I'm looking suspiciously at the `udf_convert_hex_to_dec` function. Inserts can only be made faster up to a point, some factors are indexes and physical IO performance. Run a query analyser against it, I bet the select is the slow part because of the function. – Adam Houldsworth Aug 17 '11 at 09:07
  • 2
    User Defined Functions can be slow. Can you not cast the hex directly to decimal - CAST (col9 AS Decimal(4)) for example>? – Simon Aug 17 '11 at 09:08
  • the cost of the select is 9% the cost of table spool is 32% the cluster insert costs 58% – Corovei Andrei Aug 17 '11 at 09:11

3 Answers3

25

To get the best possible performance you should:

  • Remove all triggers and constraints on the table
  • Remove all indexes, except for those needed by the insert
  • Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
  • Set the fill factor to 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
  • Change the recovery model of the database to Simple, reducing the overhead for the transaction log.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or via the Database Engine Tuning Advisor. You should do this to make sure you haven't removed an index which SQL Server was using to speed up the INSERT.

If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERT instead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

Also see Optimizing Bulk Import Performance - much of the advice in that article also applies to "normal" inserts.

Justin
  • 84,773
  • 49
  • 224
  • 367
  • As I said I have only one index which is the primary key and no triggers. I shall try to use the BULK INSERT method. – Corovei Andrei Aug 17 '11 at 09:14
  • Sort the source query to the clustered index on the target. – paparazzo Feb 14 '12 at 21:00
  • Instead of changing recovery model to Simple, change it to [BULK_LOGGED](http://msdn.microsoft.com/en-us/library/ms189275(v=sql.110).aspx). You can change it back after the bulk operation. – CFreitas May 30 '14 at 10:43
  • I'm testing this. Setting fill factor seems to slow down the insert speed. Intuitively this makes some sense. I'm guessing (didn't check), that the data will take up less space on disk this way... – Wouter Nov 07 '16 at 16:16
1

Have you considered using SqlBulkCopy? You need to build a DataTable and pass it to the WriteToServer routine.

It's FAST!

GilShalit
  • 6,175
  • 9
  • 47
  • 68
0

you can use in your store procedure before end

 OPTION(RECOMPILE)
Mohammad Atiour Islam
  • 5,380
  • 3
  • 43
  • 48
  • This would force SQL Server to recreate the execution plan for each run, which I believe could also have negative impact? Though it is worth the shot. – Karel-Jan Misseghers Oct 06 '22 at 23:53