Query is too slow (1 second, but needs to improve)

I have generated a very large query. Unfortunality this query is too slow for us. The query is 1 second, but it needs to improve too less than a few ms. I don't want duplicates of the result. So I need to put a distinct or something else on it, but this makes it slow. Does anyone knows a good solution for it?

Thank you in advance!

```
SELECT
"A"."ARTIKEL_ID",
"A"."MUTATOR",
"A"."DATUM_MUT",
"A"."ARTIKEL_NUMMER",
"A"."OMSCHRIJVING",
"A"."ZOEK_TERMEN",
"A"."AANVULLENDE_ARTIKEL_INFORMATIE",
"A"."EENHEID_ID",
"A"."AANTAL_IN_VERPAKKING",
"A"."BTW_SOORT_ID",
"A"."ACTIEF",
"A"."ENTREPOT",
IIF(EXISTS(
SELECT 1
FROM "KLANT_AANVRAAG_ALTERNATIEF" AS "KAA"
WHERE "KAA"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyklantaanvraagalternatieven,
IIF(EXISTS(
SELECT 1
FROM "VERKOOP_ORDER_REGEL" AS "VOR"
WHERE "VOR"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyverkooporderregels,
IIF(EXISTS(
SELECT 1
FROM "ARTIKEL_IMPA" AS "AI"
WHERE "AI"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyartikelimpas,
IIF(EXISTS(
SELECT 1
FROM "ARTIKEL_ISSA" AS "AI1"
WHERE "AI1"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyartikelissas,
IIF(EXISTS(
SELECT 1
FROM "LEVERANCIERS_ARTIKEL" AS "LA"
WHERE "LA"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyleveranciersartikel,
IIF(EXISTS(
SELECT 1
FROM "VOORRAAD_ARTIKEL" AS "VA"
WHERE "VA"."ARTIKEL_ID" = "A"."ARTIKEL_ID"), 1, 0) AS anyvoorraadartikel
FROM "ARTIKEL" AS "A"
INNER JOIN "LEVERANCIERS_ARTIKEL" AS "LA1" ON ("LA1"."ARTIKEL_ID" = "A"."ARTIKEL_ID")
INNER JOIN "ARTIKEL_IMPA" AS "AI2" ON ("AI2"."ARTIKEL_ID" = "A"."ARTIKEL_ID")
INNER JOIN "IMPA" AS "I" ON ("I"."IMPA_ID" = "AI2"."IMPA_ID")
WHERE UPPER( "I"."IMPANUMMER" || "I"."OMSCHRIJVING" ) CONTAINING '100'
UNION ALL
SELECT
"A"."ARTIKEL_ID",
"A"."MUTATOR",
"A"."DATUM_MUT",
"A"."ARTIKEL_NUMMER",
"A"."OMSCHRIJVING",
"A"."ZOEK_TERMEN",
"A"."AANVULLENDE_ARTIKEL_INFORMATIE",
"A"."EENHEID_ID",
"A"."AANTAL_IN_VERPAKKING",
"A"."BTW_S

Given the information provided, the query itself seems mostly to be correct. That is, it takes time because it is actually checking for a lot of information. The only part of it that looks improvable is the WHERE conditions:

WHERE UPPER( "I1"."ISSANUMMER" || "I1"."OMSCHRIJVING" ) CONTAINING '100'

WHERE UPPER( "A"."ARTIKEL_NUMMER" || "A"."OMSCHRIJVING" ||
        "A"."AANVULLENDE_ARTIKEL_INFORMATIE" || "A"."ZOEK_TERMEN" ) CONTAINING '100'

This condition looks like "I am looking for a 100 in either the article number, or the description, or the additional information, or ...". I guess the '100' number is just an example...

-
One thing can easily go wrong:

a.artikel_number = '987654321'
a.omschrijving = '007'

... would meet your condition, and I don't think this is what you're looking for.

-
The database won't be able to use any index to check for these conditions. This looks as if you're trying to look for words within several textual columns. If that were the case, you'd most probably would like to use FULLTEXT searches, and use fulltext indexes. Firebird doesn't offer (as of now) these facilities built-in. You have them in a number of other databases, such as PostgreSQL, MySQL (that you don't have to pay for), or Microsoft SQL Server or Oracle, to name a few.

-
If trying to use one of the add-ons for FTS or changing database is out of the question, most probably you would like to have some computed columns in your Firebird database:

search_column GENERATED ALWAYS AS 
      "ARTIKEL_NUMMER" || ',' || 
      "OMSCHRIJVING" || ',' || 
      "AANVULLENDE_ARTIKEL_INFORMATIE" || ',' ||
      "ZOEK_TERMEN"

and index that column.

This might (you'll have to try) slightly help and speedup the CONTAINING conditions.

Check also Improving '% LIKE %' Query on Firebird Database

StackExchange Database Administrators Q#160044, answer score: 4