locked
Performance improvement approach RRS feed

  • Question

  • Hello,

    Our application does a lot of inserts into a table B which is a heap with nonclustered indexes (7 million inserts a day) and it does a bulk insert. when it does bulk insert I am thinking it is trying to acquire too many row locks and getting escalated to a table lock.

    Table B is dependent on Table A and every row in table A has a column size that can vary between 100 and 7000 and for one row in table A we have 26 times size  in table B. For instance if size value in table A is 20 then table B will have 26*20 rows inserted. 

    However I was thinking about having the app create tables (heap without any index) and does the insert and during this insert since it is a heap without any index it should be able to insert faster (do multiple inserts with multiple processes instead of one) and then have another procedure to move data from these dynamically created tables to the actual table. The purpose of actual table is just for persistence and no selects will be run against it.  

    Are there any cons for this approach?

    Thanks for your inputs

    Monday, April 4, 2016 11:47 AM

Answers

  • Bulk insert almost always does a full table lock when inserting rows.  With a heap and bulk insert, this is the fastest insert speed you are going to get. 

    Adding individual INSERT statements generates many "round trips" and overhead the process and will slow it down dramatically.

    Are you having an actual problem?


    Monday, April 4, 2016 11:57 AM
    Answerer
  • Read this article as well

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 4, 2016 12:06 PM
  • Hello - I built a similar sort of system. My story is:

    1. Created dynamic table (With some unique logic) for each concurrent user
    2. Bulk Inserted records into those tables from flat files ranging from few thousands to few millions
    3. Written a back-ground SSIS package to loop through new tables & work based on my business logic

    In my case, I did not have any index on these dynamic tables at the time of creation & insert, instead I used my back-ground processes to create those since they were mostly offline processes plus other updates

    When I tested this design, I realized that having index on a table and then populating the data decreases the speed of Insert manifolds as compared to not having them initially.

    But all these things will greatly depend upon your actual business logic and in my case it worked well !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, April 4, 2016 12:47 PM

All replies

  • Bulk insert almost always does a full table lock when inserting rows.  With a heap and bulk insert, this is the fastest insert speed you are going to get. 

    Adding individual INSERT statements generates many "round trips" and overhead the process and will slow it down dramatically.

    Are you having an actual problem?


    Monday, April 4, 2016 11:57 AM
    Answerer
  • Read this article as well

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 4, 2016 12:06 PM
  • Hello - I built a similar sort of system. My story is:

    1. Created dynamic table (With some unique logic) for each concurrent user
    2. Bulk Inserted records into those tables from flat files ranging from few thousands to few millions
    3. Written a back-ground SSIS package to loop through new tables & work based on my business logic

    In my case, I did not have any index on these dynamic tables at the time of creation & insert, instead I used my back-ground processes to create those since they were mostly offline processes plus other updates

    When I tested this design, I realized that having index on a table and then populating the data decreases the speed of Insert manifolds as compared to not having them initially.

    But all these things will greatly depend upon your actual business logic and in my case it worked well !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, April 4, 2016 12:47 PM
  • Thanks for all your inputs. I am still trying to find out a better way to do parallel inserts to a table. Any references from MS will be helpful.
    Monday, April 4, 2016 6:39 PM
  • That was not your original question.

    What exactly is the problem you are trying to resolve?

    Thursday, April 7, 2016 2:53 PM
    Answerer
  • That was not your original question.

    What exactly is the problem you are trying to resolve?

    a better way to do parallel inserts to a table
    Thursday, April 7, 2016 3:14 PM
  • Are you experiencing a blocking issue?
    Thursday, April 7, 2016 3:37 PM
    Answerer