1

We are working with firebird 2.5. I have the following structure.

TABLE ARTICLES(ARTNR INTEGER NOT NULL PRIMARY KEY
  , S1 INTEGER
  , S2 INTEGER ..... S10 INTEGER)

TABLE S1..S10(ID INTEGER NOT NULL PRIMARY KEY
  , DESCRIERE VARCHAR(40))

There are foreign keys from CATALOG to Sx for all 10 columns from catalog. ARTICLES.S1 has a foreign key TO S1.ID.

TABLE RAPORT_CLASIFICARE (
  ID             INTEGER NOT NULL PRIMARY KEY,
  IDOPERATOR     INTEGER NOT NULL,
  IDCLASA        INTEGER NOT NULL,
  VALUE_INTEGER  INTEGER NOT NULL
);

The RAPORT_CLASIFICARE table is used to store multiple ID's of the S1..S10 tables. (the user select this from an interface)
IDCLASA is the index of the S_table for example 1 for S1, 2 for S2 and so on.
The user can select one or multiple S'es maxim are 10.

The goal of the user selection is 'you select some "clasifications"' and the result is only the articles that cumulatively correspond to the selection.

For example I can select from S1 ID [3,4,5] and from S3 [9,13,22] and from S7 [1,2,3] and the result must be articles that have ARTICLES.S1 in (3,4,5) AND ARTICLES.S3 in (9,13,22) AND ARTICLES.S7 IN (1,2,3).

From code (we are working with Delphi) this is made by using LEFT JOIN

SELECT * 
FROM ARTICLES A
LEFT JOIN RAPORT_CLASIFICARE RC_1 ON ((RC_1.IDOPERATOR = :AIDOPERATOR) AND (RC_1.IDCLASA = 1) AND (RC_1.VALUE_INTEGER = A.S1))
LEFT JOIN RAPORT_CLASIFICARE RC_3 ON ((RC_3.IDOPERATOR = :AIDOPERATOR) AND (RC_3.IDCLASA = 3) AND (RC_3.VALUE_INTEGER = A.S3))
WHERE A.ARTNR > 0
AND (RC_1.ID IS NOT NULL)
AND (RC_3.ID IS NOT NULL)

The SQL is ok as result, but I want to create a stored procedure to do this. Is there a way to do this? I have find a solution to work "correct" but is impossible to use because the slow speed. All the columns are indexed. We are avoiding using IN clause because is extremely slow.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Popa Ovidiu-Razvan
  • 475
  • 1
  • 5
  • 22
  • 3
    If you already have the necessary SQL, exactly what problem do you have with writing the stored procedure? – Mark Rotteveel Jul 20 '20 at 09:00
  • making LEFT JOIN with 10 tables that are not necessary (only 3 are needed for example) make the SP basically impossible to work with. is very slow because this multiple left join that are not necessary. – Popa Ovidiu-Razvan Jul 20 '20 at 09:24
  • @PopaOvidiu-Razvan so, what is stopping you from removing the unnecessary joins? – Remy Lebeau Jul 20 '20 at 10:04
  • Have you tried doing a collection of UNIONs instead of the LEFT JOINS? – MartynA Jul 20 '20 at 10:04
  • 2
    Then dynamically construct the right statement and use [`execute statement`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-execstmt) or [`for execute statement`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-forexec) in your stored procedure. – Mark Rotteveel Jul 20 '20 at 10:04
  • Btw, I'm not sure this q is answerable without some sample data. Could you add some Delphi code to create and populate the tables? – MartynA Jul 20 '20 at 10:08
  • 1
    I don't see anything wrong about using 10 left joins, just make sure to have the adequate indexes to optimize them. In this case you don't need all the columns indexed, but a single composite index with the IDOPERATOR, IDCLASA and VALUE_INTEGER columns. By the way, why do you use LEFT JOINs instead of INNER JOINs ?. The RC_1.ID IS NOT NULL on the WHERE clause enforces that you have to have found some joined records, so an INNER JOIN would get the same result. – Marc Guillot Jul 20 '20 at 11:49
  • Tks alot. I use "execute statement" and INNER JOIN and the result is excellent. I dident know until now the possibility of using "execute statement". – Popa Ovidiu-Razvan Jul 21 '20 at 06:19
  • Good to hear! Consider self-answering your question. It might help future visitors as well. I might write an answer myself later this week if I can find the time. – Mark Rotteveel Jul 22 '20 at 13:21
  • @PopaOvidiu-Razvan What i have done in the past to handle very dynamic queries. In your case your stored procedure will need to return all the fields for all S1 - S10, if you prefer to return only the fields that are relevant, then i would create a SP that will generate the SQL Statement and return that to DELPHI client for execution. This works for us. This way the Business Logic remains in the Database and the client doesn't need to know about it. Just needs to call the SP to get back the SQL. – Ed Mendez Aug 06 '20 at 22:53

0 Answers0