KaneZ KaneZ - 4 months ago 11
SQL Question

SQL "Not In" clause on same table with concat key

I've got the following statement, that gives me the exact results I want to have. The problem is, that it takes about 20 minutes to execute:

select fallid, schreibdatum, fachloe, autor, titel from tbltext
where schreibdatum >= '01.01.2015'
and stornierer is null
and texttyp like 'Entl%'
and (titel like '%vorl.%' or titel like '%Vorl.%' or titel like '%vorläu%' or titel like '%Vorläu%')
and concat(fallid, fachloe) not in
(
select concat(fallid, fachloe) from tbltext
where schreibdatum >= '01.01.2015'
and stornierer is null
and texttyp like 'Entl%'
and (titel not like '%vorl.%' and titel not like '%Vorl.%' and titel not like '%vorläu%' and titel not like '%Vorläu%')
and (titel like '%Entlas%' or titel like '%entlas%')
);


I know that there are several parts that slows it down. I know the many likes are bad, but I dont see how to avoid that. In addition to that, I searched other questions for slow "not-in-clauses", but I don't know how to get my "concat-key" on the same table into a join or exists not clause.

Any help would be greatly appreciated.

Answer

There can be several approaches to make this query faster

  1. fire query in small intervals like monthly depending on how data is scattered and get the result instead doing it in only one query
  2. check for the indexes on the columns which you have used in where clause
  3. You can go for self join instead of using NOT IN clause

And to skip concatenation operation in where clause

you can try self join instead of using not in clause..like follows (query may not give expected result )

select tab1.fallid, tab1.schreibdatum, tab1.fachloe, tab1.autor, tab1.titel  from
(
select concat(fallid, fachloe) concatedTxt1, fallid, schreibdatum, fachloe, autor, titel from tbltext
where schreibdatum >= '01.01.2015'
and stornierer is null
and texttyp like 'Entl%'
and (titel not like '%vorl.%' and titel not like '%Vorl.%' and titel not like '%vorläu%' and titel not like '%Vorläu%')
and (titel like '%Entlas%' or titel like '%entlas%')
) tab1,
(
select concat(fallid, fachloe) concatedTxt2, fallid, schreibdatum, fachloe, autor, titel from tbltext
where schreibdatum >= '01.01.2015'
and stornierer is null
and texttyp like 'Entl%'
and (titel like '%vorl.%' or titel like '%Vorl.%' or titel like '%vorläu%' or titel like '%Vorläu%')
) tab2
where tab1.fallid =tab2.fallid and concatedTxt1 != concatedTxt2