Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Long query with Table Spool Expand / Collapse
Author
Message
Posted Friday, September 9, 2005 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 1:42 PM
Points: 11, Visits: 82
I'm having trouble with this query. It seems to be running for much longer than should be needed. I'm not familiar with "Table Spools", what causes them or how to resolve them.

Below is my query and the plan text. Thanks in advance.


StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @last_x_days int
SELECT @last_x_days = 7

DECLARE @date smalldatetime
SELECT @date = getdate()

--Next, fill with active users (users that have logged in w/i the past x days)
UPDATE #branch_users
SET greenlight = 1
WHERE branchid in (
SELECT db.branchid
FROM appian.dbo.users USERS
JOIN dealerinfo.dbo.v_DealerBranches DB ON DB.dlrcode = USERS.dlrcode
JOIN dealernet.dbo.v_loginlog LL ON LL.auid = appian.dbo.udf_A

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Update(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001B2]), SET: ([#branch_users].[greenlight]=1))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Left Semi Join, OUTER REFERENCES: ([#branch_users].[branchid]))
|--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001B2]))
|--Row Count Spool
|--Nested Loops(Inner Join, WHERE: ([appian].[dbo].[udf_AUID]([login_log].[user_id])=[appian].[dbo].[udf_AUID]([USERS].[user_id])))
|--Nested Loops(Inner Join, WHERE: ([DlrCodes].[DlrCode]=Convert([USERS].[dlrcode])))
| |--Nested Loops(Inner Join)
| | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Branches].[PK_Branches]), SEEK: ([Branches].[BranchID]=[#branch_users].[branchid]) ORDERED FORWARD)
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[DlrID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[RegionID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrCodes].[DlrPrefix]))
| | | | |--Index Spool(SEEK: ([DlrCodes].[BranchID]=[#branch_users].[branchid]))
| | | | | |--Clustered Index Scan(OBJECT: ([dealerinfo].[dbo].[DlrCodes].[PK_DlrCodes]))
| | | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[DlrPrefixes].[PK_DlrPrefixes]), SEEK: ([DlrPrefixes].[DlrPrefix]=[DlrCodes].[DlrPrefix]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Regions].[PK_Regions]), SEEK: ([Regions].[RegionId]=[DlrPrefixes].[RegionID]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[Dealers].[PK_Dealers]), SEEK: ([Dealers].[DlrID]=[DlrPrefixes].[DlrID]) ORDERED FORWARD)
| |--Table Spool
| |--Clustered Index Scan(OBJECT: ([appian].[dbo].[users].[PK_users] AS [USERS]), WHERE: (Convert([USERS].[is_active])=1))
|--Table Spool
|--Clustered Index Scan(OBJECT: ([appian].[dbo].[login_log].[PK_login_log]), WHERE: ((([login_log].[site_id]='MSDNET' OR [login_log].[site_id]='PMDNET') AND datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day

(21 row(s) affected)


Post #218575
Posted Friday, September 9, 2005 1:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

Re-write as an EXISTS, rather than IN sub-query:

UPDATE #branch_users
SET greenlight = 1
FROM #branch_users As B
WHERE EXISTS (
  SELECT *
  FROM appian.dbo.users USERS
    JOIN dealerinfo.dbo.v_DealerBranches DB ON DB.dlrcode = USERS.dlrcode
    JOIN dealernet.dbo.v_loginlog LL ON LL.auid = appian.dbo.udf_A
  WHERE DB.branchid = B.branchid
)

Post #218578
Posted Friday, September 9, 2005 2:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 1:42 PM
Points: 11, Visits: 82
That doesn't make any difference in the query performance.
The query is still performing TABLE SPOOLS and LAZY SPOOLS.
Any other ideas?


StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @last_x_days int
SELECT @last_x_days = 7
DECLARE @date smalldatetime
SELECT @date = getdate()

--NEXT, FILL TEMP TABLE WITH ACTIVE USERS (USERS THAT HAVE LOGGED IN WITHIN THE PAST x DAYS)
UPDATE #branch_users
SET greenlight = 1
FROM #branch_users As B
WHERE EXISTS (
SELECT *
FROM appian.dbo.users USERS
JOIN dealerinfo.dbo.v_DealerBranches DB ON
DB.dlrcode = USERS.dlrcode
JOIN dealernet.dbo.v_loginlog LL ON
LL.auid = appian.dbo.udf_AUID(USERS.user_id)
WHERE DB.branchid = B.branchid
AND (USERS.is_active = 1) -- and users.approved=1)
AND ( datediff(d,LL.dt_login,@date) <= @last_x_days
AND datediff(d,@date,LL.dt_login) < 1))

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Update(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9]), SET: ([#branch_users].[greenlight]=1))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Left Semi Join, OUTER REFERENCES: ([B].[branchid]))
|--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9] AS [B]))
|--Row Count Spool
|--Nested Loops(Inner Join, WHERE: ([appian].[dbo].[udf_AUID]([login_log].[user_id])=[appian].[dbo].[udf_AUID]([USERS].[user_id])))
|--Nested Loops(Inner Join, WHERE: ([DlrCodes].[DlrCode]=Convert([USERS].[dlrcode])))
| |--Nested Loops(Inner Join)
| | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Branches].[PK_Branches]), SEEK: ([Branches].[BranchID]=[B].[branchid]) ORDERED FORWARD)
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[DlrID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[RegionID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrCodes].[DlrPrefix]))
| | | | |--Index Spool(SEEK: ([DlrCodes].[BranchID]=[B].[branchid]))
| | | | | |--Clustered Index Scan(OBJECT: ([dealerinfo].[dbo].[DlrCodes].[PK_DlrCodes]))
| | | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[DlrPrefixes].[PK_DlrPrefixes]), SEEK: ([DlrPrefixes].[DlrPrefix]=[DlrCodes].[DlrPrefix]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Regions].[PK_Regions]), SEEK: ([Regions].[RegionId]=[DlrPrefixes].[RegionID]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[Dealers].[PK_Dealers]), SEEK: ([Dealers].[DlrID]=[DlrPrefixes].[DlrID]) ORDERED FORWARD)
| |--Table Spool
| |--Clustered Index Scan(OBJECT: ([appian].[dbo].[users].[PK_users] AS [USERS]), WHERE: (Convert([USERS].[is_active])=1))
|--Table Spool
|--Clustered Index Scan(OBJECT: ([appian].[dbo].[login_log].[PK_login_log]), WHERE: ((([login_log].[site_id]='MSDNET' OR [login_log].[site_id]='PMDNET') AND datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day

(21 row(s) affected)
Post #218609
Posted Friday, September 9, 2005 3:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

Probably best to post the DDL of all tables/views involved.

What size is the loginlog table ? It has a clustered index scan which migth be costly depending on its size.

 

Post #218618
Posted Friday, September 9, 2005 3:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 1:42 PM
Points: 11, Visits: 82
What's the best way to get the DDL for you?

The Loginlog table contains 2,025,274 records.
Post #218621
Posted Friday, September 9, 2005 3:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

Just select the objects in EM and generate a SQL script, setting the options to script keys & indexes.

A table of 2+ million rows being scanned is definitely not desirable. Finding a way to get the optimiser to perform an index seek on the loginlog table is bound to improve things.

 

Post #218625
Posted Saturday, September 10, 2005 11:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886

David,

According to your profiler trace, the pepper doesn't turn to fly specs until the appian.dbo.udf_A User Defined Function hits the fan... that's where your problem is and you should probably post the code for THAT.  If you don't think so, tell me where in the script you submitted that dealer regions are found and the IsActive flag is interrogated...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #218686
Posted Sunday, September 11, 2005 4:01 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353

Clause in your UDF appian.dbo.udf_A

"WHERE ... datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day"

eliminates all indexes and makes server scan whole table and return all rows, because it cannot predict result of the calculation.

Change it to

[login_log].[dt_login]>= datediff(day, - @last_x_day, Convert([@date]))

and make sure you have clustered index on column dt_login.

Post #218715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse