janmechtel

SQL nested query execution plan

Nov 30th, 2011
76
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT r.id, h.user_id, s.Id, s.Name  FROM log_rawdata r  LEFT JOIN log_analyzerevents a ON a.log_rawdata_id=r.id  LEFT JOIN log_header h ON r.log_head_id=h.id  LEFT JOIN ShortcutSet s ON SUBSTRING(r.gui_element_name,14,100) = s.Id  WHERE gui_element_name LIKE 'Notification %'  AND event_type=9  AND h.user_id IN (936)
  2.   |--Nested Loops(Left Outer Join, WHERE:([Expr1009]=[veodin].[dbo].[ShortcutSet].[Id] as [s].[Id]))
  3.        |--Parallelism(Gather Streams)
  4.        |    |--Hash Match(Left Outer Join, HASH:([r].[id])=([a].[log_rawdata_id]), RESIDUAL:([veodin].[dbo].[log_analyzerevents].[log_rawdata_id] as [a].[log_rawdata_id]=[veodin].[dbo].[log_rawdata].[id] as [r].[id]))
  5.        |         |--Bitmap(HASH:([r].[id]), DEFINE:([Bitmap1011]))
  6.        |         |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[id]))
  7.        |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([r].[log_head_id], [Expr1010]) WITH UNORDERED PREFETCH)
  8.        |         |              |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,substring([veodin].[dbo].[log_rawdata].[gui_element_name] as [r].[gui_element_name],(14),(100)),0)))
  9.        |         |              |    |--Clustered Index Scan(OBJECT:([veodin].[dbo].[log_rawdata].[ci_azure_fixup_dbo_log_rawdata] AS [r]),  WHERE:([veodin].[dbo].[log_rawdata].[event_type] as [r].[event_type]=(9) AND [veodin].[dbo].[log_rawdata].[gui_element_name] as [r].[gui_element_name] like 'Notification %') ORDERED FORWARD)
  10.        |         |              |--Clustered Index Seek(OBJECT:([veodin].[dbo].[log_header].[PK_log_header] AS [h]), SEEK:([h].[id]=[veodin].[dbo].[log_rawdata].[log_head_id] as [r].[log_head_id]),  WHERE:([veodin].[dbo].[log_header].[user_id] as [h].[user_id]=(936)) ORDERED FORWARD)
  11.        |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([a].[log_rawdata_id]))
  12.        |              |--Clustered Index Scan(OBJECT:([veodin].[dbo].[log_analyzerevents].[PK__log_anal__3213E83F4974805D] AS [a]), WHERE:(PROBE([Bitmap1011],[veodin].[dbo].[log_analyzerevents].[log_rawdata_id] as [a].[log_rawdata_id],N'[IN ROW]')))
  13.        |--Table Spool
  14.             |--Clustered Index Scan(OBJECT:([veodin].[dbo].[ShortcutSet].[PK_ShortcutSet] AS [s]))
  15.  
RAW Paste Data