SQL Server Performance Forum – Threads Archive
Row by Row operationsI 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
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
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
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.
Without knowing more details, Chappy’s suggestion is the one I would personally try.
Brad M. McGehee
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’
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’
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
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.
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
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