SQL Server LEFT JOIN taking up 5 seconds

Question

I have this query which only delivers my desired result when its executed as a LEFT JOIN. On some cases it also works as INNER JOIN but based on feedback the LEFT JOIN query delivers way better search results.

Unfortunately it takes up to 5 seconds. Even when executed directly on the database.

Have a look at my query - Is there something messed up completely that it takes so long?

SELECT 
    Kundendaten$.ID, Kundendaten$.firmenname1, Kundendaten$.firmenname2, 
    Kundendaten$.strasse, Kundendaten$.uid, Kundendaten$.lkw12t, 
    Kundendaten$.lkw3t, Kundendaten$.gesperrt, Kundentyp$.ext_kdnr, 
    Kundentyp$.Kundentyp_ID 
FROM 
    Kundendaten$ 
LEFT JOIN 
    Kundentyp$ ON Kundendaten$.ID = Kundentyp$.Kunden_ID 
WHERE 
    Kundentyp$.ext_kdnr LIKE '%ufo%' OR
    Kundendaten$.firmenname1 LIKE '%ufo%' OR 
    Kundendaten$.firmenname2 LIKE '%ufo%' OR 
    Kundendaten$.suchwort LIKE '%ufo%' OR 
    Kundendaten$.gehr_kundennummer LIKE '%ufo%' OR 
    Kundendaten$.kundenummer LIKE '%ufo%' OR 
    Kundendaten$.uid LIKE '%ufo%' OR 
    Kundendaten$.ID LIKE '%ufo%'
ORDER BY 
    Kundendaten$.ID ASC 
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

I am no MSSQL-Server-Expert, but are you sure that the performance is swallowed in the JOIN part? I'd guess that the main issue is with all the LIKE operators and missing fulltext indexes on the fields. Please try querying without the join and see, if the performance issue is still there. — Dominik Sandjaja, Sep 22 '15 at 06:54
The problem isn't with the LEFT JOIN but with those LIKE on the WHERE clause. — Thanos Markou, Sep 22 '15 at 06:55
I assume the `INNER JOIN` is faster because it greatly reduces the amount of records. Like already said, the main issue is probably with all the `LIKE` operators. The `INNER JOIN` just has to search through less text. — Edwin Stoteler, Sep 22 '15 at 06:56
When I remove the `LEFT JOIN` and leave the rest as it is it gets executed instantly. So it must be something with the join :/ — noa-dev, Sep 22 '15 at 06:58
@noa-dev How many records are there in `Kundendaten$` and how many in `Kundentyp$`. If `Kundentyp$` contains more records then the `LIKE` operators have to search through more text. It could also be that `Kundentyp$.ext_kdnr` contains very long texts. — Edwin Stoteler, Sep 22 '15 at 07:01
Like Edwin said, it's the amount of data. If you have a very large table Kundendaten$ and a very small table Kundentyp$ then you will get a very small result table for an inner join but the whole table Kundendaten$ for the left join. On these result the where clause will filter the return data. The like search is expensive in combination with the or statements. So filtering a small table with that is much faster than in the large table. — Tjasun, Sep 22 '15 at 07:04
I have now tested to search for LIKE without the %% on both ends and it now gets executed instantly even with the LEFT JOIN. The Kundendaten& has 6500 entries and the Kundentyp around 1500. Sadly I cant really leave out the %% in the search since I can't guarantee the user to always search for the exact keyword — noa-dev, Sep 22 '15 at 07:04

score 3 · Accepted Answer · edited May 23 '17 at 10:26

Either try Full-text search(FTS) when you want search this type of data.

Still you want, then try this, you can add your where condition in left join.. on... condition like

SELECT 
    Kundendaten$.ID, Kundendaten$.firmenname1, Kundendaten$.firmenname2, Kundendaten$.strasse, Kundendaten$.uid, Kundendaten$.lkw12t, Kundendaten$.lkw3t, Kundendaten$.gesperrt, Kundentyp$.ext_kdnr, Kundentyp$.Kundentyp_ID 
FROM Kundendaten$ 
 --this below condition, will filter the data first and then join and then where condition.
LEFT JOIN Kundentyp$ ON Kundendaten$.ID = Kundentyp$.Kunden_ID and  Kundentyp$.ext_kdnr LIKE '%ufo%' 
WHERE 
Kundendaten$.firmenname1 LIKE '%ufo%' OR 
Kundendaten$.firmenname2 LIKE '%ufo%' OR 
Kundendaten$.suchwort LIKE '%ufo%' OR 
Kundendaten$.gehr_kundennummer LIKE '%ufo%' OR 
Kundendaten$.kundenummer LIKE '%ufo%' OR 
Kundendaten$.uid LIKE '%ufo%' OR 
Kundendaten$.ID LIKE '%ufo%'
ORDER BY Kundendaten$.ID ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

When I used FTS

UPDATED

This is sample data , check this. If left table does not have data, still it get the whole data from right table.

Declare @Kundendaten$  table (ID int , firmenname1 varchar(50), firmenname2 varchar(50), strasse varchar(50), uid int, lkw12t varchar(50), lkw3t varchar(50), gesperrt varchar(50))

insert into @Kundendaten$ values 
(1 , 'ufo1', '1', '2', 3, '4', '5' , '6' ),
(2 , 'uf2o', 'ufo1', '2', 4, '4', '5' , '6' ),
(3 , 'ufo3', '1', 'ufo2', 5, '4', '5' , '6' ),
(4 , 'ufo4', '1', '2', 6, '4', '5' , '6' ),
(5 , 'ufo5', '1', '2', 7, '4', '5' , '6' ),
(6 , 'abcd', '1', '2', 8, '4', '5' , '6' )

declare @Kundentyp$  table ( Kunden_ID int,ext_kdnr varchar(50), Kundentyp_ID int)
insert into @Kundentyp$ values 
(1 , 'ufo1' , 1 ),
(1 , 'ufo1' , 2 ),
(2 , 'uf2o' , 2 ),
(6 , 'abcd' , 2 ),
(5 , 'ufo5' , 2 )

--you can just execute without where condition to understand more that how LEFT JOIN work
select * 
FROM @Kundendaten$ k1 
LEFT JOIN @Kundentyp$ k2 ON k1.ID = k2.Kunden_ID and  k2.ext_kdnr LIKE '%ufo%' 
WHERE 
k1.firmenname1 LIKE '%ufo%' OR 
k1.firmenname2 LIKE '%ufo%' OR 
--k1.suchwort LIKE '%ufo%' OR 
--k1.gehr_kundennummer LIKE '%ufo%' OR 
--k1.kundenummer LIKE '%ufo%' OR 
k1.uid LIKE '%ufo%' OR 
k1.ID LIKE '%ufo%'

It should be noted that this changes the behaviour of the query, that is now it starts acting like a proper LEFT JOIN. — Jakub Kania, Sep 22 '15 at 07:09
Yes, the main result of join will effect and output of this will get lesser result and hence speed-up the result — Ajay2707, Sep 22 '15 at 07:12
Seems to seal the deal. My previous problem with the JOINing was also that IF the Kundentyp$.Kunden_ID was empty the whole query returned nothing. Now it seems to run fine. I'll give it a few testdrives :) — noa-dev, Sep 22 '15 at 07:13
Yes, only thing to note this will filter first so the data should be proper. other wise not give the result proper... thanks. — Ajay2707, Sep 22 '15 at 07:14
Okay. I was happy too early. It does work for the search for keywords that can be found in the Kundendaten$ table. But If i Look for an existing entry in the Kundentyp$.ext_kdnr Table I don't get any result. — noa-dev, Sep 22 '15 at 07:17
means in your second table, if data does not exist, then what you want. you want the first table entry only. pl. explain so give proper answer. — Ajay2707, Sep 22 '15 at 08:31
@Ajay2707 I understand. But I have created an entry in the second table to test the query and if I only look for that entry which is in the Kundentyp$.ext_kdnr column. The result stays empty — noa-dev, Sep 23 '15 at 05:17

Maverick · Answer 2 · 2015-09-22T07:12:15.663

The query is looking fine except the usage of LIKE clause.

The use of INNER JOIN or LEFT JOIN depends on the requirements. As @Edwin pointed out, using INNER JOIN will reduce the number of records scanned so it will be faster than LEFT JOIN.

The problem I see here isthe excess usage of LIKE clause. It will never use any index search and make the query slow.

if the fields like Kundendaten$.uid and Kundendaten$.ID are of numeric datatype, you can use the exact search for them. This will use indexes and will make the queries faster.

The other thing which you can use to improve the performance of the query is to restrict the number of records for scanning by using the LIKE search as starts with:

Kundentyp$.ext_kdnr LIKE 'ufo%' OR
Kundendaten$.firmenname1 LIKE 'ufo%' OR 
Kundendaten$.firmenname2 LIKE 'ufo%' OR 
Kundendaten$.suchwort LIKE 'ufo%' OR 
Kundendaten$.gehr_kundennummer LIKE 'ufo%' OR 
Kundendaten$.kundenummer LIKE 'ufo%' OR 
Kundendaten$.uid = 1234  OR 
Kundendaten$.ID = 1234

I wasn't one of the downvoters. But I can't really use it like that. I only receive one keyword as search entry. It can be a number or a string. — noa-dev, Sep 22 '15 at 07:14
@noa-dev: you might consider using two different search queries, depending on whether the user entered a string (then search text fields) or a number (then search id/number fields). — Andre, Sep 22 '15 at 07:19

SQL Server LEFT JOIN taking up 5 seconds

2 Answers2