Consult a very strange question-CodePudding

 DECLARE @ PersonId int=111111, 
@ BeginDate int=0 

DECLARE @ UpdateDate AS TABLE 
(
[FlowId] [int] NOT NULL PRIMARY KEY, 
[UpateDate] [int] NOT NULL, 
[the RowId] [int] NOT NULL INDEX Idx_RowId 
) 
The BEGIN 
INSERT the @ UpdateDate ([FlowId], [UpateDate], the RowId) 
The SELECT FlowId, UpdateDate, ROW_NUMBER () OVER (ORDER BY FlowId ASC) FROM 
(
The SELECT UpdateDate, MAX (FlowId) AS FlowId 
The FROM [_DetailIndex] WHERE PersonId=@ PersonId 
GROUP BY UpdateDate 
) a 
- to perform to this @ UpdateDate table has about 20 record 
END 

DECLARE @ Pool AS TABLE (PersonId int, WeId int, BeginDate int, EndDate int) 
INSERT INTO @ Pool (PersonId, WeId, BeginDate, EndDate) - if note off the INSERT, execution will soon, don't note down the execution time increases 10 times 
SELECT @ PersonId as PersonId, 
Aleem walji eId, 
B.U pateDate, 
ISNULL (C.U pateDate, 99991231) 
The FROM _WePool A 
INNER JOIN @ UpdateDate B ON A.F lowId=b. lowId 
LEFT the JOIN @ UpdateDate C ON B.R owId + 1=C.R owId 
WHERE ISNULL (C.U pateDate, 99991231) & gt; @ BeginDate AND Amy polumbo ersonId=@ PersonId 
Article - _WePool there are tens of millions of data, data related to @ PersonId article 30 w, made with PersonId and FlowId keyword

Now to insert data execution efficiency especially slow, @ Pool would cost more than 16 seconds, logic of _WePool reads 900 w, but if the inserts, injection time is less than 1 SEC, _WePool logical reads only 30 w, don't know what reason is this,
If change the @ Pool into a temporary table, won't have this problem,
If you remove the last LEFT JOIN @ UpdateDate C ON B.R owId + 1=C.R owId also won't have this problem, the great god under the guidance

CodePudding user response:

Not strange, table variables are not suitable for large amounts of data,
Using a temporary table can solve calculate, don't tangle

CodePudding user response:

reference 1/f, gypsy song response:

not weird, table variables are not suitable for large amounts of data,
Use temporary tables can solve calculate, do not tangle

sure is not a problem of large amounts of data, I insert article hundreds of thousands of data to the table variables have no pressure, the screening result also just tens of thousands of article, the problem is the join order, but don't know why have this problem,

CodePudding user response:

Top post,,,,

CodePudding user response:

Temporary table table variables mainly is a relatively more I/O time, but less memory resources utilization,
Large amount of data, due to less memory resources consumption, using a temporary table has better performance than table variables,

Use standard is one of the few according to quantity (thousands or less) use table variables, large amount of data in the temporary table,
Your memory is smaller?

CodePudding user response:

reference 4 floor gypsy song response:

temporary table table variables mainly is a relatively more I/O time, but less memory resources utilization,
Large amount of data, due to less memory resources consumption, using a temporary table has better performance than table variables,

Use standard is one of the few according to quantity (thousands or less) use table variables, large amount of data in the temporary table,
Your memory is smaller?

server memory no problem, but the way of SQL server in the link, there are a lot of differences, I see, no insert, using hash match, but have the insert with nested loops, don't understand why on the optimized way is not the same

CodePudding user response:

 INSERT INTO @ Pool (PersonId, WeId, BeginDate, EndDate) - if note off the INSERT, execution will soon, don't note down the execution time increases 10 times 
SELECT @ PersonId as PersonId, 
Aleem walji eId, 
B.U pateDate, 
ISNULL (C.U pateDate, 99991231) 
The FROM _WePool A 
INNER JOIN @ UpdateDate B ON A.F lowId=b. lowId 
LEFT the JOIN @ UpdateDate C ON B.R owId + 1=C.R owId 
WHERE ISNULL (C.U pateDate, 99991231) & gt; @ BeginDate AND Amy polumbo ersonId=@ PersonId 
OPTION (RECOMPILE)

The last sentence, at the back to add OPTION (RECOMPILE) ,