Analyze the cause of inefficiency when the TOP statement is placed outside the table Value Function

SQLSERVER's table value function is a new feature of SQLSERVER 2005. It is easy to use, just like a separate table, and is widely used in our system. There is an SQL Server Table value function for obtaining customer data. If you log on as an administrator, this function will return rows of records, which takes about 30 seconds, however, if the TOP statement is placed outside the table value function, the efficiency is very low. It takes about 3 minutes:

 
 

  
  
select top 20  * from GetFrame_CustomerSerch('admin','1') 
 
 

The following is the definition of the stored procedure:

 
 

  
  
Alter function [dbo]. [GetFrame_CustomerSerch]
  
  
(
  
  
-- Add the parameters for the function here
  
  
@ WorkNo varchar (38)
  
  
, @ SerchChar varchar (500)
  
  
)
  
  
RETURNS TABLE
  
  
AS
  
  
RETURN
  
  
(
  
  
-- Add the SELECT statement with parameter references here
  
  
Select a. GUID, a. CustomerName, a. CustomerIDcard, a. CustomerPhone, a. CustomerMobile from
  
  
(
  
  
-- The specific subquery is omitted
  
  
)
  
  
) A union all
  
  
Select B. GUID, B. CustomerName, B. CustomerIDcard, B. CustomerPhone, B. CustomerMobile from WFT_ManagerCollectUsers a left join WFT_Customer B on a. FundAccount = B. FundAccount
  
  
-- Where a. WorkNo = @ WorkNo
  
  
WHERE a. WorkNo IN
  
  
(
  
  
-- The specific subquery is omitted
  
  
)
  
  
)
 
 

This statement is placed in the SQL-MAP file of the PDF. NET data development framework, initially thought it was caused by the Framework, the statement is directly queried in the query analyzer, still very slow.

Extract the SQL statement in GetFrame_CustomerSerch and add the Top query directly. It takes only 6 seconds, N times faster:

 
 

  
  
Declare @ WorkNo varchar (38)
  
  
Declare @ SerchChar varchar (500)
  
  
Set @ WorkNo = 'admin'
  
  
Set @ SerchChar = '1'
  
  
Select top 20 a. GUID, a. CustomerName, a. CustomerIDcard, a. CustomerPhone, a. CustomerMobile from
  
  
(
  
  
-- The specific subquery is omitted
  
  
)
  
  
) A union all
  
  
Select B. GUID, B. CustomerName, B. CustomerIDcard, B. CustomerPhone, B. CustomerMobile from WFT_ManagerCollectUsers a left join WFT_Customer B on a. FundAccount = B. FundAccount
  
  

  
  
WHERE a. WorkNo IN
  
  
(
  
  
-- The specific subquery is omitted
  
  
)
 
 

Why is there such a big difference?

My analysis may be due to the following reasons:

1. If Top or other conditions are used outside the table value function, the SQL Server Query Optimizer cannot optimize the query. For example, all records are returned first, then, select the first 20 records in the temporary table;

2. Although the table value function uses "Table variables", it is in memory, but if the "table" results are very large, it is very likely that the memory will not be retained, but the results will be stored in the physical memory, and the database will reserve some memory space for other queries), using virtual memory, the virtual memory is actually a disk page file. When there are too many records, frequent page exchanges will occur, resulting in a very low query efficiency.

It seems that the "Table value function" is not as good as the legend. I don't know what you think.

Recently, I also encountered a strange problem. There was a stored procedure that had always become extremely slow after 1-2 days of system operation, but it was very fast to modify it again, just adding a space and so on ), I don't know if you have met me. Why?