Sorin


Try

IF(
EXISTS SELECT ID FROM Table1 WHERE MainId = SomeValue, TRUE,
IF(
EXISTS SELECT ID FROM Table2 WHERE MainId = SomeValue, TRUE,
IF(
EXISTS SELECT ID FROM Table3 WHERE MainId = SomeValue, TRUE,FALSE
)))

I think I've got the brackets right

That will give you a simple true/false result and should be a lot faster.

Roy Lambert [Team Elevate]

> IF(
> EXISTS SELECT ID FROM Table1 WHERE MainId = SomeValue, TRUE,
> IF(
> EXISTS SELECT ID FROM Table2 WHERE MainId = SomeValue, TRUE,
> IF(
> EXISTS SELECT ID FROM Table3 WHERE MainId = SomeValue, TRUE,FALSE
> )))

I'll try this but just for information
Is something wrong with my code?

Sorin

Sorin

>I'll try this but just for information
>Is something wrong with my code?

Yes, or at least I think so. I'm not technical enough to explain it properly. I think from previous posts of Tim's what happens is you're creating a result set which is a join of everything to everything.  This can be absolutely massive so 40 minutes and counting is not unrealistic. That's the really big problem.

A quick search on google for cartesian join (which is what you're producing) gave me

A cartesian join is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a cartesian join will return 100,000 rows. Something to be avoided!

Also you're carrying out unnecessary processing - if the ID is in table 1 there's no point in testing tables 2 or 3

Roy Lambert [Team Elevate]

> Yes, or at least I think so. I'm not technical enough to explain it
> properly. I think from previous posts of Tim's what happens is you're
> creating a result set which is a join of everything to everything.  This
> can be absolutely massive so 40 minutes and counting is not unrealistic.
> That's the really big problem.
>
> A quick search on google for cartesian join (which is what you're
> producing) gave me
>
> A cartesian join is a join of every row of one table to every row of
> another table. This normally happens when no matching join columns are
> specified. For example, if table A with 100 rows is joined with table B
> with 1000 rows, a cartesian join will return 100,000 rows. Something to be
> avoided!
>
> Also you're carrying out unnecessary processing - if the ID is in table 1
> there's no point in testing tables 2 or 3

Thanks Roy

Sorin,

<< I found that the statement "SELECT Id FROM Table1, Table2, Table3 WHERE
MainId = SomeValue"
run well when the result is empty but stuck when the result isn't empty. >>

You're generating a result set that is equivalent to the number of rows in
Table1 X the number of rows in Table2 X the number of rows in Table3.  So,
even if each table only had 1000 rows, the result set will contain 1 billion
rows.

*Always* use join conditions with multiple tables in an SQL statement,
unless you're absolutely sure that the number of rows is very small.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << I found that the statement "SELECT Id FROM Table1, Table2, Table3 WHERE
> MainId = SomeValue"
> run well when the result is empty but stuck when the result isn't empty.
>  >>
>
> You're generating a result set that is equivalent to the number of rows in
> Table1 X the number of rows in Table2 X the number of rows in Table3.  So,
> even if each table only had 1000 rows, the result set will contain 1
> billion rows.

Ok, I try to use Roy suggestion so I create that procedure:

CREATE PROCEDURE DeleteOrphan (IN "Id" VARCHAR)
BEGIN
  DECLARE CustomCursor CURSOR FOR Stmt;

  PREPARE Stmt FROM 'SELECT Id FROM tb1
     WHERE EXISTS (SELECT Id FROM tb1 WHERE MainId = ?)
     UNION SELECT Id FROM tb2
     WHERE EXISTS (SELECT Id FROM tb2 WHERE MainId = ?)
     UNION SELECT Id FROM tb3
     WHERE EXISTS (SELECT Id FROM tb3 WHERE MainId = ?);

     OPEN CustomCursor USING Id, Id, Id;

  IF EOF(CustomCursor) THEN
     PREPARE Stmt FROM 'DELETE FROM tbMain WHERE Id = ?';
     EXECUTE Stmt USING Id;
  END IF;
END

I want to delete the record from the tbMain table if the EOF is true.
I don't get any error but the expected record isn't deleted and it should.

Thanks
Sorin

Sorin


Reading the manual

When a result set cursor is first opened via the
OPEN statement, the cursor is always positioned so that the EOF function will return False unless the result set is empty, in which case both the EOF and the BOF functions will return True.


so I'm guessing your select statement generates a result.

Have you tried it in EDBManager to see what happens?


Roy Lambert [Team Elevate]

> Reading the manual
>
> When a result set cursor is first opened via the
> OPEN statement, the cursor is always positioned so that the EOF function
> will return False unless the result set is empty, in which case both the
> EOF and the BOF functions will return True.
>
>
> so I'm guessing your select statement generates a result.
>
> Have you tried it in EDBManager to see what happens?

Thanks Roy

Yes I try to run the query from the edbmanager and the result is empty.

Thanks
Sorin

Sorin

In that case no idea why its not working.


Try this

CREATE PROCEDURE DeleteOrphan (IN "Id" VARCHAR)
BEGIN
  DECLARE CustomCursor CURSOR FOR Stmt;
  DECLARE ItExists BOOLEAN;

  PREPARE Stmt FROM 'SELECT
IF(
EXISTS (SELECT ID FROM Table1 WHERE ID = ?), TRUE,
IF(
EXISTS (SELECT ID FROM Table2 WHERE ID = ?), TRUE,
IF(
EXISTS (SELECT ID FROM Table3 WHERE ID = ?), TRUE,FALSE
)))
INTO ?
FROM Contacts
range 1 to 1'


     OPEN CustomCursor USING Id, Id, Id, ItExsits;

  IF ItExists THEN
     PREPARE Stmt FROM 'DELETE FROM tbMain WHERE Id = ?';
     EXECUTE Stmt USING Id;
  END IF;
END

I'm not a 100% certain of the syntax but it should be pretty near

Roy Lambert

> CREATE PROCEDURE DeleteOrphan (IN "Id" VARCHAR)
> BEGIN
>   DECLARE CustomCursor CURSOR FOR Stmt;
>   DECLARE ItExists BOOLEAN;
>
>   PREPARE Stmt FROM 'SELECT
> IF(
> EXISTS (SELECT ID FROM Table1 WHERE ID = ?), TRUE,
> IF(
> EXISTS (SELECT ID FROM Table2 WHERE ID = ?), TRUE,
> IF(
> EXISTS (SELECT ID FROM Table3 WHERE ID = ?), TRUE,FALSE
> )))
> INTO ?
> FROM Contacts
> range 1 to 1'
>
>
>      OPEN CustomCursor USING Id, Id, Id, ItExsits;
>
>   IF ItExists THEN
>      PREPARE Stmt FROM 'DELETE FROM tbMain WHERE Id = ?';
>      EXECUTE Stmt USING Id;
>   END IF;
> END

Thanks Roy

The problem was on the parameter I call the procedure.
Now both procedures work.

I think I'll adopt your's (if you give copyright) looks cleaner but I don't
understand what "Contacts" stand for.
I replace it with "Table1" but it's seem like I can use any valid table?

Thanks
Sorin