SQL stored procedure temporary memory issue

Question

We have the following simple stored procedure that runs as an SQL agent job overnight. It usually starts after 20 minutes, but recently MatchEvent and MatchResult tables have grown to more than 9 million rows. This led to the storage procedure taking more than 2 hours, and all 8 GB of memory in our SQL box was used up. This makes the database inaccessible to regular queries trying to access it.

I assume that the problem is that the temporary table is too large and causes memory and database problems.

How can I rewrite a stored procedure to make it more efficient and less memory intensive?

Note. I edited SQL to indicate that a condition appeared on the original SELECT statement. I previously left this for simplicity. In addition, when the request is launched using the processor, it is 1-2%, but the memory, as indicated above, is maximized

CREATE TABLE #tempMatchResult
(
    matchId VARCHAR(50)
)

INSERT INTO #tempMatchResult SELECT MatchId FROM MatchResult WHERE SOME_CONDITION

DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM #tempMatchResult)

DELETE FROM MatchResult WHERE MatchId In (SELECT MatchId FROM #tempMatchResult)

DROP TABLE #tempMatchResult

Answer 1

There is probably a lot going on here, and this is not your request.

Firstly, I agree with the other posters. Try rewriting this without a temporary table, if at all possible.

But assuming you need a temp table here, you have a BIG problem that you do not have the PK defined on it. This will greatly expand the amount of time it takes for your requests to run. Create a table like this:

CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

, , TempDB . SQL- , . , , . .

Answer 2

, ?

DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM MatchResult)


DELETE FROM MatchResult
-- OR Truncate can help here, if all the records are to be deleted anyways.

Answer 3

, . ( , , ?) :

• . (, .)
• N , LIMIT 100 .
• , - WHERE M <= x AND x < .
• " " . , 5 , . ( , .)

Postgres . , , . , "" , . YMMV.

, . EXPLAIN .

(, , - , .)

Answer 4

-, Dave M.

, - , , , sp_rename . , .

. tempdb, temp.

,

CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

set transaction isolation level serializable
begin transaction 

create table MatchEventT(columns... here)

insert into MatchEventT
select * from MatchEvent m
left join #tempMatchResult t on t.MatchId  = m.MatchId 
where t.MatchId is null 

-- create all the indexes for MatchEvent

drop table MatchEvent
exec sp_rename 'MatchEventT', 'MatchEvent'

-- similar code for MatchResult

commit transaction 


DROP TABLE #tempMatchResult

Answer 5

.
:

DELETE MatchEvent
FROM MatchEvent  e , 
     MatchResult r
WHERE e.MatchId = r.MatchId 

: , , temp , , ( ). .

.
, , , .

-

SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent 
    WHERE  Match_Id = @MatchId 

    SELECT @MatchId = min(MatchId) FROM MatchResult WHERE MatchId > @MatchId 
END

-

CREATE TABLE #tmp ( MatchId Varchar(50) ) 

/* get list of lowest 1000 MatchIds: */ 
INSERT #tmp 
SELECT TOP (1000) MatchId 
FROM MatchResult 
ORDER BY MatchId 

SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent
    FROM MatchEvent e , 
         #tmp       t
    WHERE e.MatchId = t.MatchId 

    /* get highest MatchId we've procesed: */  
    SELECT @MinMatchId = MAX( MatchId ) FROM #tmp  

    /* get next 1000 MatchIds: */  
    INSERT #tmp 
    SELECT TOP (1000) MatchId 
    FROM MatchResult 
    WHERE MatchId > @MinMatchId
    ORDER BY MatchId 

END

1000 .
, , ( !). , , 1000.

Answer 6

DELETE FROM MatchResult WHERE
MatchId In (SELECT MatchId FROM #tempMatchResult)

can be replaced by

DELETE FROM MatchResult WHERE SOME_CONDITION

Answer 7

Can you just enable cascading deletes between matchresult and matchevent? Then you only need to worry about identifying one dataset for deletion and let SQL take care of the other.

An alternative would be to use the OUTPUT clause, but this is definitely more scripted.

Both of them will allow you to remove from both tables, but you only need to specify (and execute) the filter predicate once. It may still not be as effective as the batch approach, as suggested by other posters, but worth considering. Ymmv