Hello,

I wrote the following procedure:
PROCEDURE "ZWEI" ()
BEGIN

DECLARE StatCursor Cursor with return for stmt;
DECLARE EINGABECURSOR CURSOR FOR ESTMT;


DECLARE ERSTE INTEGER DEFAULT 0;
DECLARE ZWEITE INTEGER DEFAULT 0;

DECLARE EINGABE INTEGER DEFAULT 0;


Unprepare stmt;
                    
PREPARE STMT FROM'SELECT * FROM ZWEI';
OPEN STATCURSOR;

UNPREPARE ESTMT;
FETCH FIRST FROM STATCURSOR INTO ERSTE,ZWEITE;
WHILE NOT EOF(STATCURSOR) DO

PREPARE ESTMT FROM 'SELECT COUNT(*)FROM
                   (SELECT COUNT(Zahl) FROM TOTAL
                    WHERE ZAHL = '+CAST(ERSTE AS VARCHAR)+' OR ZAHL ='
                    +CAST(ZWEITE AS VARCHAR)+'
                    GROUP BY RUNDE
                    HAVING COUNT(ZAHL) >1)AS T';
  
OPEN EINGABECURSOR;

FETCH FROM EINGABECURSOR INTO EINGABE;
UPDATE STATCURSOR SET ZAHL_3 = EINGABE;
FETCH NEXT FROM STATCURSOR INTO ERSTE,ZWEITE;
END WHILE;


END


This works. The time for update is very high ---> 97,22 Sec.

ZWEI:       990 Rows
TOTAL:  11725 Rows

Is there a faster way?

TIA
Dieter

Dieter

I'm doing some (a lot) guessing here but since all you want it the number of rows generated try this



PROCEDURE "ZWEI" ()
BEGIN
DECLARE StatCursor Cursor for stmt; <<<<<<<<<< you don't need the return since you're not returning a cursor to the outside world
DECLARE EINGABECURSOR CURSOR FOR ESTMT;

DECLARE ERSTE INTEGER DEFAULT 0;
DECLARE ZWEITE INTEGER DEFAULT 0;

DECLARE EINGABE INTEGER DEFAULT 0;

Unprepare stmt;
                   
PREPARE STMT FROM'SELECT * FROM ZWEI';
OPEN STATCURSOR;

UNPREPARE ESTMT;

PREPARE ESTMT FROM 'SELECT COUNT(Zahl) FROM TOTAL
WHERE ZAHL = ? OR ZAHL = ?
GROUP BY RUNDE
HAVING COUNT(ZAHL) >1'';

FETCH FIRST FROM STATCURSOR INTO ERSTE,ZWEITE;
WHILE NOT EOF(STATCURSOR) DO
 
OPEN EINGABECURSOR USING ERSTE,ZWEITE ;

UPDATE STATCURSOR SET ZAHL_3 = ROWCOUNT(EINGABECURSOR); <<<<<<<<< since all you want is the count of rows
FETCH NEXT FROM STATCURSOR INTO ERSTE,ZWEITE;
END WHILE;

END

No idea if it will work since I have nothing I'd like to try it on, but if it does it will prevent the need to prepare ESTMT for each loop round ZWEI

Roy Lambert

Roy,

thanks for your answer.

Dieter





Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dieter

I'm doing some (a lot) guessing here but since all you want it the number of rows generated try this



PROCEDURE "ZWEI" ()
BEGIN
DECLARE StatCursor Cursor for stmt; <<<<<<<<<< you don't need the return since you're not returning a cursor to the outside world
DECLARE EINGABECURSOR CURSOR FOR ESTMT;

DECLARE ERSTE INTEGER DEFAULT 0;
DECLARE ZWEITE INTEGER DEFAULT 0;

DECLARE EINGABE INTEGER DEFAULT 0;

Unprepare stmt;
                   
PREPARE STMT FROM'SELECT * FROM ZWEI';
OPEN STATCURSOR;

UNPREPARE ESTMT;

PREPARE ESTMT FROM 'SELECT COUNT(Zahl) FROM TOTAL
WHERE ZAHL = ? OR ZAHL = ?
GROUP BY RUNDE
HAVING COUNT(ZAHL) >1'';

FETCH FIRST FROM STATCURSOR INTO ERSTE,ZWEITE;
WHILE NOT EOF(STATCURSOR) DO
 
OPEN EINGABECURSOR USING ERSTE,ZWEITE ;

UPDATE STATCURSOR SET ZAHL_3 = ROWCOUNT(EINGABECURSOR); <<<<<<<<< since all you want is the count of rows
FETCH NEXT FROM STATCURSOR INTO ERSTE,ZWEITE;
END WHILE;

END

No idea if it will work since I have nothing I'd like to try it on, but if it does it will prevent the need to prepare ESTMT for each loop round ZWEI

Roy Lambert

Dieter
Did it work? Was it faster?

Roy Lambert

Roy,

no, not realy.

>>>>>UPDATE STATCURSOR SET ZAHL_3 = ROWCOUNT(EINGABECURSOR); <<<<<<<<< since all you want is the count of rows

this helps nothing.

I hope that I will find the brake

Dieter







Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dieter
Did it work? Was it faster?

Roy Lambert

Dieter

>
> Is there a faster way?
>

Does the SQL below (replacing fielda and fieldb with the fieldnames from
table Zwei) give you the correct counts to update?

SELECT fielda,fieldb,COUNT(*) AS tot FROM
(SELECT fielda,fieldb,total.runde,count(*) from Zwei
JOIN total ON total.zahl=Zwei.fielda or total.zahl=Zwei.fieldb
GROUP by fielda,fieldb,total.runde
HAVING COUNT(*) > 1) t1
GROUP BY fielda,fieldb

If it does then maybe the following is worth a try

UPDATE Zwei SET Zahl_3 =
(SELECT tot FROM
SELECT fielda,fieldb,COUNT(*) AS tot FROM
(SELECT fielda,fieldb,total.runde,count(*) from Zwei
JOIN total ON total.zahl=Zwei.fielda or total.zahl=Zweir.fieldb
GROUP by fielda,fieldb,total.runde
HAVING COUNT(*) > 1) t1
GROUP BY fielda,fieldb) t2
WHERE Zwei.fielda=t2.fielda and Zwei.fieldb=t2.fieldb)


John

oops

Just noticed I missed a left bracket before the second select in the second
query

John

John,

at first many thanks for your help!

The table "zwei" looks like this:

1 2
1 3
1 4
1 5
1 6

and so on till
......
44 45

thats 990 rows.

Now I will apdate the table "zwei" field zahl_3 with the table total.
Your example dosn't work, because this update the table Zahl_2 instead Zahl_3.

Sorry for my bad english.

Dieter













"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote:

oops

Just noticed I missed a left bracket before the second select in the second
query

John

Dieter

> Now I will apdate the table "zwei" field zahl_3 with the table total.
> Your example dosn't work, because this update the table Zahl_2 instead
Zahl_3.
>
> Sorry for my bad english.

Your english seems good to me

The query I posted updates field zahl_3 in table zwei.  The field/table
zahl_2 is not named in my SQL or in the scripts you posted before???  Does
the first query produce he correct counts from table total?

John

John,

PREPARE STMT FROM'SELECT * FROM ZWEI';
OPEN STATCURSOR;

UNPREPARE ESTMT;
FETCH FIRST FROM STATCURSOR INTO ERSTE,ZWEITE; <=====  (ZAHL_1,ZAHL_2)
WHILE NOT EOF(STATCURSOR) DO


With the followed code I will update the Field ZAHL_3.....


PREPARE ESTMT FROM 'SELECT COUNT(*)FROM
                  (SELECT COUNT(Zahl) FROM TOTAL
                   WHERE ZAHL = '+CAST(ERSTE AS VARCHAR)+' OR ZAHL ='
                   +CAST(ZWEITE AS VARCHAR)+'
                   GROUP BY RUNDE
                   HAVING COUNT(ZAHL) >1)AS T';


Dieter



The query I posted updates field zahl_3 in table zwei.  The field/table
zahl_2 is not named in my SQL or in the scripts you posted before???  Does
the first query produce he correct counts from table total?

John