Why is using Table Spool slower than not?
There are two similiar sqls in sql server, in which the TBSFA_DAT_CUST table has millions of rows and without constraints (no index and primary key) the other two have only a few rows and a normal primary key:
s for the 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, '') != ''
f for faster:
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, '') != ''
f (above 0.6s) is much faster than s (above 4.6s).
Otherwise, I found two ways to make s as fast as f:
1. Add a constant and a primary key in the TBSFA_DAT_CUST.CUST_ID table;
2. Specifically more than 61 columns of the TBSFA_DAT_CUST table (80 columns in total).
My question is, why does sql optimizer use Table Spool when I use certain columns in SELECT clause and not '*', and why is using Table Spool slower?
My question is about sql-server table-spool
source to share
In a slower query, you restrict your result set to specific columns. Since this is a non-indexed table with no constraints, the optimizer creates a temporary table from the initial table scan with only the specific columns needed. It is then executed through a nested loop statement in a temporary table. When he knows he will need every column of the table (Select *), he can run the nested loop statement directly from the table scan, because the result set of the scan will be completely joined to the top table.
In addition to this, your request has a couple of other possible problems:
LEFT JOIN TBSFA_DAT_CUST C ON A.CUST_ID = B.CUST_ID
you are not joining anything here, you are joining the whole table on every record. Does it mean a.cust_id = c.cust_id
either 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 <> ''
source to share