Slow SQL query due to inner and left join?

Can someone explain this behavior or how to get around it?

If you run this query:

select * from TblA left join freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key] inner join DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID 

It will be very very slow.

If you change this request to use two internal joins instead of the left join, it will be very fast. If you change it to use two left joins instead of an inner join, it will be very fast.

You can observe the same behavior if the sql table variable is used instead of the freetexttable variable.

A performance problem occurs anytime you have a table variable (or freetexttable) and a table in another database directory, where one is in the inner join and the other is in the left join.

Does anyone know why this is slow or how to speed it up?

+4
source share
4 answers

The general rule is that OUTER JOINs increase the number of rows in the result set, while INNER JOINs decrease the number of rows in the result set. Of course, there are many scenarios where the opposite is also true, but it rather works as it does not. What do you want to do for performance, since the size of the result set (working set) should be as small as possible.

Since both compounds coincide in the first table, changing the order does not affect the accuracy of the results. Therefore, you probably want to do an INNER JOIN before a LEFT JOIN:

 SELECT * FROM TblA INNER JOIN DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID LEFT JOIN freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key] 

As a practical matter, the query optimizer should be smart enough to compile to use the faster option, no matter what order you specify for the connections. However, it’s good practice to pretend that you have a dumb query optimizer and that the query operations are in order. This helps future maintainers identify potential errors or assumptions about the nature of the tables.

Since the optimizer needs to rewrite things, this is probably not good enough to fully explain the behavior you see, so you still want to study the execution plan used for each query, and possibly add an index as suggested earlier. This is still a good principle to learn.

+7
source

What you usually have to do is turn on the Show Actual Execution Plan option, and then look carefully at what causes the slowdown. (hover over each connection to see details). You want to make sure that you are getting an index search, not a table scan.

I would suggest what happens is that SQL is forced to pull everything from one table into memory in order to make one of the joins. Sometimes changing the order you join the tables will also help.

+3
source

Specify the field that you use to make the connection.

A good rule is to assign an index to any commonly mentioned foreign or candidate keys .

0
source

Putting freetexttable(TblB, *, 'query') into a temporary table can help if it is called repeatedly in the execution plan.

0
source

Source: https://habr.com/ru/post/1276573/


All Articles