RE: SQL Tuning Question

Prembr_834_lockupdt_ky – 2,979
Distinct mbr id: 2,483 <- a high level of uniqueness

Prembr_834_data_ky – 2,966
Distinct mbr id – 2,648

Ouchies!

With a half hour run time, I was expecting – at least - in the hundreds of thousands of rows.

Even with a hash join and a full table scan of both, I wouldn’t expect a half hour for merging a total of 6k rows.

I think I’m going to have to agree with Norm that your issue lies outside this query… unless – I suppose – you’re running this on a server with very low resources available.

Perhaps there’s another process that’s locking the rows that your query is trying to update and so your query is in a waiting state for a large portion of the time.

If you set timing on and run the following query, how quickly does it come back with the count?

SELECT COUNT(*)
FROM   (SELECT mbr_age, class_value, pu1.mbr_medicaid_id
FROM   prembr_834_data_ky pd1, prembr_834_lockupdt_ky pu1
WHERE  pu1.mbr_medicaid_id = pd1.mbr_medicaid_id
AND    TRIM(pu1.class_value_834) = TRIM(pd1.class_value)
);

This would be a pure query time, but it would process all the rows that the update query would touch.

Roger S.

________________________________
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.