SQL Server Performance

Row by Row operations

Discussion in 'T-SQL Performance Tuning for Developers' started by mjaganmr, Dec 26, 2002.

  1. mjaganmr New Member

    I am populationg a target table with 24K rows. It's taking 19 min to complete the process using row by row operation from a stored procwdure. Here I am populating an error table for FK violation and NOT NULL violation errors. Because of this I can't go for Set Based Operations. At the same time I am Updating the rows when ever the Unique Key violation occurs.

    Please suggest me a solution to improve the performance for the above process. There are no Indexes on target table other than PK and UK.

    Jagan
  2. satya Moderator

    Until this operation finishes disable all constraints and check what columns doesn't accept NULLs and replace with default values if any. What was the error referred during the operation?

    Satya SKJ
  3. mjaganmr New Member

    Actually I have to populate error table when ever the NOT NULL and Parent Key (FK) violation occurs (based on error code I am populating the error rows in corresponding error table). So I can't disable the FK and NOT NULL constraints. User will update the error rows based on error for reprocessing of these rows. Here I can't avoid the row by row process.

    But I need to speed up the process. Is there any DB system parameters need to set or else any workaround to achieve the same functionality.

    Jagan
  4. Chappy New Member

    You could try (bulk) inserting the rows first into a intermediate table with no constraints. Then the stored procedure could read from this table using a cursor, and insert each row into the destination table, or the error table. This would save you the round trip from client to server for each row, and should be a lot faster.

    Depending on whether this is supposed to be a generic process or not, the stored procedure could even select all rows from the intermediate table, where certain columns were null, add error rows for them, and then delete them, making an even bigger speedup.
  5. bradmcgehee New Member

    Without knowing more details, Chappy's suggestion is the one I would personally try.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. arb New Member

    quote:Originally posted by mjaganmr

    I am populationg a target table with 24K rows. It's taking 19 min to complete the process using row by row operation from a stored procwdure. Here I am populating an error table for FK violation and NOT NULL violation errors. Because of this I can't go for Set Based Operations. At the same time I am Updating the rows when ever the Unique Key violation occurs.

    Why can't you use set-based operations?

    Assumptions: Data is coming from some select statement. ie INSERT INTO tblFoo SELECT * FROM tblBar WHERE somecondition

    First, try put the data into an intermediary table (to simplify things - you can do this without an intermediate table but it can get a bit messy...)


    INSERT INTO #tmp SELECT * FROM tblBar WHERE somecondition

    Next, work out which rows are going to cause an error because of NULLs, add an entry to the error table for each and remove the bad rows from the intermediate table.


    INSERT INTO tblError (RowID, Error)
    SELECT ID, 'NULL not allowed'
    FROM #tmp
    WHERE col IS NULL

    DELETE FROM #tmp
    WHERE col IS NULL

    Do the same for FK violations:


    INSERT INTO tblError (RowID, Error)
    SELECT ID, 'FK violation'
    FROM #tmp
    WHERE NOT EXISTS (SELECT * FROM tblFK WHERE Key = #tmp.FK)

    DELETE FROM #tmp
    WHERE NOT EXISTS (SELECT * FROM tblFK WHERE Key = #tmp.FK)

    Then insert the remaining rows into your destination table...



    INSERT INTO tblFoo
    SELECT *
    FROM #tmp

    Unless you absolutely need to perform complex operations on a row by row basis, it should be possible to come up with a selection of set-based operations to do what you want.
  7. mjaganmr New Member

    Thanks alot all of you for your interest and involvement.

    I implemented the Chappy's suggestion and it was faster than the previous one. I created a temporary table and populated(Insert/Select) in Stored proc it self before opening the cursor and I opend the cursor on Temporary table. Now it's completing process with in 40% of the provious time.

    Once again Thanks to Chappy

    Jagan
  8. x002548 New Member

    Bulk insert works fine, but it is only for sysadmin or the owner of the object. If you have to release to production, and your using some kind of connection pooling with a defined user id then you will run into difficulty.

    Also is this a flat file? How did the data get their in the first place?

    Brett

Share This Page