Performance problem.. 1

[SSesham]

I have a source table
tim_clmHdr (claim_code varchar(20), diag varchar(6))
with 1356080 rows. claimcode repeats but not diag.

I have a target table
tbl_claimmaster(claim_code varchar(20), diag1 varchar(6), diag2 varchar(6), diag3 varchar(6), diag4 varchar(6)) with 1203607

I need to find upto 4 diagnosis codes from the source table and update them in the target table matching on the claim_code. it seems most of the claims have only one diagnosis code but i have to look in the source if it has multiple and update if they exist.

My solutions seems to be slow..i have indexed the sourcetable on those 2 columns and running a cursor (with order by claim_code) on itand updating the (Appropriate Diag )target by matching on claimcode. i have indexed the target table on claim_code so that the matching will be faster.

does anybody has a solution where in i can avoid cursors for this situation of multiple rows (single column) getting updated into a single row (multiple columns)...especially dealing with a million rows..

i Appreciate any suggestions in this regard..

~Many Thanks in advance

 

[JohnDTampaBay]

Here is a way to do it without using a cursor. It still takes several passes, but may be less resource-intensive as the cursor. HTH, Good luck!

SELECT * INTO #staging FROM tim_clmHdr

UPDATE m
SET diag1 = s.diag
FROM tbl_claimmaster m
  INNER JOIN #staging s
    ON m.claim_code = s.claim_code

DELETE FROM #staging
FROM #staging s
  INNER JOIN tbl_claimmaster m
    ON m.claim_code = s.claim_code
    AND m.diag1 = s.diag

UPDATE m
SET diag2 = s.diag
FROM tbl_claimmaster m
  INNER JOIN #staging s
    ON m.claim_code = s.claim_code

DELETE FROM #staging
FROM #staging s
  INNER JOIN tbl_claimmaster m
    ON m.claim_code = s.claim_code
    AND m.diag2 = s.diag

UPDATE m
SET diag3 = s.diag
FROM tbl_claimmaster m
  INNER JOIN #staging s
    ON m.claim_code = s.claim_code

DELETE FROM #staging
FROM #staging s
  INNER JOIN tbl_claimmaster m
    ON m.claim_code = s.claim_code
    AND m.diag3 = s.diag

UPDATE m
SET diag4 = s.diag
FROM tbl_claimmaster m
  INNER JOIN #staging s
    ON m.claim_code = s.claim_code

DELETE FROM #staging
FROM #staging s
  INNER JOIN tbl_claimmaster m
    ON m.claim_code = s.claim_code
    AND m.diag4 = s.diag

DROP TABLE #staging

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[vongrunt]

For "run once" kind of query, it should work. Otherwise - beware of DELETE anomalies. Scenario:

- claim_code X has 3 diags. diag1-diag3 are NOT NULL, diag4 is NULL
- diag3 for that claim_code is removed from source table
- run update procedure again
- diag3 will not be UPDATEd to NULL because of INNER JOIN clause

 

[SSesham]

THANKS A TON BUDDY!! Seems like this is the correct solution, Running it right now will let you know...

THANKS SO MUCH AGAIN!!!

 

[SSesham]

overall it took only 2:min 37sec for first table (1.2mil rows) and just 22 sec for a next small file (153000 rows)....GREAT performance improvement from the suggestion of 'JohnDTampaBay' !! Hearty Thanks to John again!!