Table Matching with subquery

Hi there,

I have got a very difficult problem.

I have got a table (A) with around 4 million records. If new records are imported they get a status of 0.
Then I check if in another table (B) there are records which have got the same Identifiers. I need to match these two tables. In table A are Records with multiple Country locations e.g. USA and Germany for one Identifier and the splitt amount for each country.
In Table B Is only one record for that Identifier with the total amount.
When I found the Identifier in table B I add the ID of the record to a field in table A.
Now I need to check at first if there are records (more than one) which match to table B e.g. sum of splitt amount (in A) must be total amount (in B).

I did this without problems (SP takes 8 min). Now I need to find all records which are only once in table A and set their "edited" amount and status.
I created a query with two subs, which took 16 hours and found 70k matching records.
I splitted the huge query into two Views and tried to make an update to that query, but it is still running (since 2 and a halfe hours now)
If I only run the 2. View which uses the first as well it takes 16 minutes.

I need to check this, because ther are remaining records, which match e.g. 3 records of A match to one record in B but the sum doesn't fit.
Have you got any Idea how to tune this??

Regards

Athu

This is the first SP:

CREATE PROCEDURE [005_Update_records_where_Total_Units_equals_UiW_in _One_Nominee_by_ISIN_OLD] AS

UPDATE [GIS].[dbo].[Views_Holdings]

SET [Total_Units_VPR_Edited]= [Total_Units_WINS],
[EditType]= 'A',
[EditDate]= (Getdate()),
[Editor]=(Suser_sname()),
[Status] = 5

WHERE WINS_ID
in
(
SELECT [WINS_ID]
FROM [GIS].[dbo].[Views_Holdings]
GROUP BY
[GTI_or_Collateral],
[FileValueDate],
[ISIN],
[WINS_ID],
[SkipBilling],
[Total_Units_VPR_Edited],
[Status]
HAVING ([Status] = 1)
AND [Skip] <> 1
AND [WINS_ID] <> 0
AND (COUNT(WINS_ID) = 1)
AND Total_Units_VPR_Edited is NULL
)
GO


This are the splitted ones:


---- View 1
CREATE VIEW dbo.[005_T2View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN]
AS
SELECT WINS_ID
FROM dbo.Views_Holdings
GROUP BY GTI_or_Collateral, FileValueDate, ISIN, WINS_ID, SkipBilling, Total_Units_VPR_Edited, Status
HAVING (Status = 1) AND (Skip<> 1) AND (WINS_ID <> 0) AND (COUNT(WINS_ID) = 1) AND (Total_Units_VPR_Edited IS NULL)

---- View 2
CREATE VIEW dbo.[005_T3View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN]
AS
SELECT dbo.Views_Holdings.Rec_ID, dbo.[005_T2View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN].WINS_ID,
dbo.Views_Holdings.Total_Units_VPR_Edited, dbo.Views_Holdings.Total_Units_WINS, dbo.Views_Holdings.EditType,
dbo.Views_Holdings.EditDate, dbo.Views_Holdings.Editor, dbo.Views_Holdings.Status
FROM dbo.Views_Holdings INNER JOIN
dbo.[005_T2View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN] ON
dbo.Views_Holdings.WINS_ID = dbo.[005_T2View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN].WINS_ID

---- SP New
CREATE PROCEDURE [005_Update_records_where_Total_Units_equals_UiW_in _One_Nominee_by_ISIN]
AS
UPDATE [GIS].[dbo].[005_T3View_where_Total_Units_equals_UiW_in_One_Nom inee_by_ISIN]
SET [Total_Units_VPR_Edited]=[Total_Units_WINS],
[EditType]= 'A',
[EditDate]= (Getdate()),
[Editor]= (SUSER_SNAME()),
[Status]= 5