How to find out how to speed-up nested query?

We have a table of events (95M rows,growing fast, MS SQL).

Simplified columns: 

id,
type,
user,
message

We often start with a simply query:

select *
from events e1
where type=9

and then nest it to find the event directly previous to that ones (the Max event id smaller than the current and same user id):

select *, (
 Select MAX(id) 
 from events e2 
 where e2.id <e1.id
 and e1.user=e2.user
) as id_of_the_last_event_before_type_9

from events e1
where type=9

These queries are very slow, my question is how can I learn about why they are slow?

Is there a better way to approach this (other DB?) ? Should I look into MapReduce scenario to speed up (query could be done per user and then joined)

Update (execution plan): http://pastebin.com/5CdPCe0S

From the execution plan I learned, that most of the query is spent on a LIKE condition. I will first try to create an index on that.

asked Nov 30 '11 at 13:45

Jan's gravatar image

Jan
26●2●2●6

edited Nov 30 '11 at 14:21

@Jan, the first step is to look at the execution plan. Here is some guide how to do that: http://use-the-index-luke.com/sql/explain-plan/sql-server/getting-an-execution-plan If you add that to your question, I'll have a look at it.

(Nov 30 '11 at 13:52) Markus Winand ♦♦

@Markus thanks for the tip, that give me something to investigate. 87% of cost are on a "clustered Index scan" where I select rows like that name LIKE 'Notification %'. I will try to find out how to speed that up

(Nov 30 '11 at 14:23) Jan
2 Answers:

I'll post your execution plan here, and add some comments:

|--Nested Loops(Left Outer Join, WHERE:([Expr1009]=[veodin].[dbo].[ShortcutSet].[Id] as [s].[Id]))
   |--Parallelism(Gather Streams)
   |    |--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]))
   |         |--Bitmap(HASH:([r].[id]), DEFINE:([Bitmap1011]))
   |         |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[id]))
   |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([r].[log_head_id], [Expr1010]) WITH UNORDERED PREFETCH)
   |         |              |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,substring([veodin].[dbo].[log_rawdata].[gui_element_name] as [r].[gui_element_name],(14),(100)),0)))
   |         |              |    |--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)
   |         |              |--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)
   |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([a].[log_rawdata_id]))
   |              |--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]')))
   |--Table Spool
        |--Clustered Index Scan(OBJECT:([veodin].[dbo].[ShortcutSet].[PK_ShortcutSet] AS [s]))

You are having three Clustered Index Scans. Remember that SCAN is "bad" in SQL Server Short Reference of Execution Plan operations. These are the obvious points to start with:

Clustered Index Scan(OBJECT:([log_rawdata].[ci_azure_fixup_dbo_log_rawdata] AS [r]),        
  WHERE:([log_rawdata].[event_type] as [r].[event_type]=(9)        
     AND [log_rawdata].[gui_element_name] as [r].[gui_element_name] like 'Notification %') ORDERED FORWARD)

The WHERE predicates tell you the columns for the index. In that case event_type, gui_element_name (in that order, because the prefixed liked is executed as "range scan" see Indexing LIKE)

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]')))

That is part of a Hash-Join. Without knowing the data, it is hard to tell if it is done because there is no proper index for other join methods, or because the result set is large enought so that hash-join makes sense. There is, of course, also an explaination how to index Hash-Joins.

Clustered Index Scan(OBJECT:([veodin].[dbo].[ShortcutSet].[PK_ShortcutSet] AS [s]))

It is also hard to tell, why exactly. Hard to believe there is no index on s.id?

Besides that, I wonder if there is an index on h.user_id? The whole join sequence might better start off there? But that depends on the data distribution as well.

The join on SUBSTRING is, of course, sub-optimal. A indexed, computed column could also help, but not for the join order as visible above.

Hope that gives you the right hints. Have a look at my Instant Coaching Service as well. That a very efficient way to join your application and data knowledge with my database experience to find the best solution quickly.

answered Dec 01 '11 at 07:55

Markus%20Winand's gravatar image

Markus Winand ♦♦
936●5●11●20

Awesome detail Markus, I feel bad for you to have spent this time before I even fixed all the obvious "missing" index. But now I am well equiped to prepare better next time we have a slower query.

(Dec 09 '11 at 15:01) Jan

Learn how to read the execution plan http://use-the-index-luke.com/sql/explain-plan/sql-server/getting-an-execution-plan

Then setup Indexes as needed. "Use the index Luke" >> indeed!

answered Nov 30 '11 at 15:31

Jan's gravatar image

Jan
26●2●2●6