Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    28

    Unanswered: Large insert ruining my transaction log

    Hi all

    I have a multithread application that use 10 connections of the database, each of this on the first stage create table with the cluster keys of the tables, the problem its that the transaction log got full almost inmediately, I already setup the database to truncate the log with the commit but its not enough. I am trying to make smaller transactions but I haven´t succeed. All the tables have cluster or non cluster indexs and the Query Process Plan shows that are taking the index. By the way the tables that I use as a souce dont have any kind of id.

    Any suggestion?

    The only decent approach that I have its with the rowcount but its taking too long, if I run the process of one of the multi threat takes 24 secs with the regular Insert but I use the follow code takes 16 hrs (I left the process last night)
    Takes 24 secs with regualr insert
    Code:
    INSERT INTO MAIN1..DESTINATION
    	(
    	NAME,
    	POSITION_ID
    	)
    SELECT  NAME,
    	   POSITION_ID
    FROM FROM MAIN..SOURCE

    Takes 16 hours with rowcount
    Code:
    DECLARE @total INT
    DECLARE @current INT 
    
    
    SELECT @total = count(*) FROM MAIN..SOURCE
    SET @current = 0
    
    WHILE @current <= @total
    BEGIN 
    	set rowcount 9000
    	BEGIN TRANSACTION 
    	INSERT INTO MAIN1..DESTINATION
    		(
    		NAME,
    		POSITION_ID
    		)
    	SELECT  NAME,
    		   POSITION_ID
    	FROM FROM MAIN..SOURCE S
    	WHERE NOT EXISTS 
    	(
    		SELECT 1
            FROM MAIN1..DESTINATION
            WHERE POSITION_ID = S.POSITION_ID
        )
        COMMIT TRANSACTION 	
        SELECT @current = count(*) FROM FROM MAIN..SOURCE
    END

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    I think i understood your problem here.

    First off all i've change a bit your code, nothing much:

    Code:
    DECLARE @total INT
    DECLARE @current INT 
    
    SELECT @total = count(POSITION_ID) FROM MAIN..SOURCE
    SET @current = 0
    
    set rowcount 9000
    
    WHILE @current <= @total
    BEGIN 
    	--set rowcount 9000  -- don't need to set the rowcount every time, moved it to before the while.
    	BEGIN TRANSACTION 
    		INSERT INTO MAIN1..DESTINATION ( NAME, POSITION_ID)
    		SELECT  NAME, POSITION_ID
    		FROM MAIN..SOURCE S --FROM FROM MAIN..SOURCE S -- error 2 from
    		WHERE NOT EXISTS (
    			SELECT 1				
    			FROM MAIN1..DESTINATION
    			WHERE POSITION_ID = S.POSITION_ID
    			)
        COMMIT TRANSACTION 	
        --SELECT @current = count(*) FROM FROM MAIN..SOURCE   -- this way would only run once and error 2 from
    	SELECT @current = count(POSITION_ID) FROM MAIN1..DESTINATION
    END
    
    set rowcount 0    -- added to undo the previous modification
    You say if you run the single instruction to update all at once, the insert takes a total of 24 secs. But if you try multiple threads it takes 16 hours.

    What type of locking scheme does the table has?

    If your scheme is 'allpages' you are having lock contention.
    If you have 'datarows' or datapages' you probably having a deadlock, diferent processes have diferent pages of your table and requesting each others, a stand still.
    These locked transactions, become long running transactions, thus filling the transaction log.

    But why do you have 10 connections trying to create and insert on the same table?
    This creation and 'feeding' should be done prior and only one time, then after, all the multiple connections can read from it with no problems.

    That's why the single insert runs ok, its the only process requesting the table.

    Hope it helps, if i'm way off i'm sorry.
    Last edited by Catarrunas; 06-20-12 at 11:30.

  3. #3
    Join Date
    Jun 2011
    Posts
    28
    Hi

    I have datarows and yes the ten process take information from that table, I beleive you are ritgth with the deadlocks and that why I want to do a small transaction, by example @total its 1,800,000.00 ant thats why I do the 9000 rowcount in order to do small transactions and release the transaction log.

    Regards

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello again,

    I get what your trying to do, parallelize the insert. But the only parallelism on inserts that i know is on partition tables.

    That code works great if only one thread is called. You will sequencially insert the values into the table, but only one thread. If you try to add more you will create that locking problem.

    'Commands that modify data (insert, update, and delete), and cursors do not run in parallel. The inner, nested blocks of queries containing subqueries are never executed in parallel, but the outer block can be executed in parallel.'
    Retrieved from Sybase.

    So in my opinion that code wont work in multiple instances, only one.

    If you or somebody find a way, let me know please.

  5. #5
    Join Date
    Jun 2011
    Posts
    28
    I keep trying the wayt o do smaller transactions and If I find the anwer I will post it

  6. #6
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello again eldeleon,

    I'm sorry to keep pushing but smaller transactions? The transaction that you have is an insert. Each insert is a transaction.
    Code:
    begin transaction .... Insert.... commit transaction
    So smaller than what you have i'm not seeing it.

    If you try less transactions, you might improve your time. One tran , multiples inserts.
    But again, always will have locking problem, an insert does an exclusive lock on the page, no process can access to it.
    The single process is the most performant solution. Less locking, thus less time.

    If you are trying not to fill the log, you can do is divide the big insert into small blocks of 9000 like you have in the code.
    Code:
    begin transaction
      set rowcount 9000
      . 
      .
      while  ...
      .
      .
    commit transaction
    Like this you will make one transaction and insert 9000 rows, not filling the transaction log and not filling the tempdb.

  7. #7
    Join Date
    Jun 2011
    Posts
    28
    the only problem its that I need to insert 1,800,000 recors and with this toke 16 hrs to complete something that takes usually between 24-34 secs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •