Why is using Table Spool slower than not?

Question

There are two similiar sqls running in sql server,in which the table TBSFA_DAT_CUST has millons rows and no constraint(no index and primary key), the other two has just a few rows and normal primary key:

s for slower one:

SELECT A.CUST_ID, C.CUST_NAME, A.xxx  --and several specific columns
FROM TBSFA_DAT_ORD_LIST A JOIN VWSFA_ORG_EMPLOYEE B ON A.EMP_ID = B.EMP_ID
     LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID
     JOIN VWSFA_ORG_EMPLOYEE D ON A.REVIEW_ID = D.EMP_ID
WHERE ISNULL(A.BATCH_ID, '') != ''

execution plan of slower one

f for faster one:

SELECT *
FROM TBSFA_DAT_ORD_LIST A JOIN VWSFA_ORG_EMPLOYEE B ON A.EMP_ID = B.EMP_ID
     LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID
     JOIN VWSFA_ORG_EMPLOYEE D ON A.REVIEW_ID = D.EMP_ID
WHERE ISNULL(A.BATCH_ID, '') != ''

execution plan of faster one

f(above 0.6s) is much faster than s(above 4.6s).

Otherwise,I found two ways to make s fast as f:

1.Add constaint and primary key in table TBSFA_DAT_CUST.CUST_ID;

2.Specific more than 61 columns of table TBSFA_DAT_CUST(totally 80 columns).

My question is why sql optimizer uses Table Spool when I specific columns in SELECT clause rather than '*',and why is using Table Spool one executes slower?

My question is about sql-server table-spool

user_1733651 · Answer

In the slower query you are limiting your result set to specific columns. Since this is an un-indexed un constrained table the optimizer is creating a temporary table from the original table scan with only the specific columns required. It is then running through the nested loop operator on the temporary table. When it knows its going to need every column on the table (Select *) it can run the nested loop operator directly off the table scan because the result set of the scan will be joined in full to the top table.

Outside of that your query has a couple other possible problems:

LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID

you aren't joining to anything here, you are joining the entire table to every record. Did mean a.cust_id = c.cust_id or b.cust_id = c.cust_id or a.cust_id = c.cust_id and b.cust_id = c.cust_id?

Also, this function in the where clause is pointless and can degrade performance:

WHERE ISNULL(A.BATCH_ID, '') != ''

change it to:

WHERE A.BATCH_ID is not null and A.Batch_ID <> ''

Why is using Table Spool slower than not?

1 Answers