List:MySQL on Win32« Previous MessageNext Message »
From:jbonnett Date:August 10 2004 11:58pm
Subject:RE: Calling a Stored Procedure crashing MySQL!!
View as plain text  
I have tried to unravell what you are trying to do and it seems to me you
may be able to achieve what you want without any cursors. As far as I can
tell what you are trying to do can be rewritten something like this

SELECT <?>
FROM t1 as x, t1 as y
WHERE x.f1 = y.f1
AND y.f2 > x.f2
AND y.f3 <= x.f3
AND x.f6 NOT IN ('CNL')
AND x.f7 <> '0'
AND x.f8 <> 'null'
AND y.f4 NOT IN ('CNL')
AND y.f5 IS NOT NULL
AND y.f6 <> 'null'

What you put at <?> will depend on what you want to do at "//do something
here" and which fields you need. You could perhaps make a cursor out of the
above query to do whatever you want.

Also I think f4 NOT IN ('CNL') is the same as f4 <> 'CNL' which probably
runs faster.

I am also suspicious that your table design may not be very good. It looks
to me that you have two different things stored in fields of the one table.
Life may be a lot simpler if there were two tables. While I don't understand
what you are doing, when I see things like f4 NOT IN ('CNL') and f6 NOT IN
('CNL') plus f6 <> 'null' and f8 <> 'null' I get the feeling that f4, f6 and
f8 may be storing multiple instances of the same sort of data in the one
table. This sort of thing violates first normal form. You will find SQL
works much better if you design your tables to at least 3rd normal form.

Anyway, there are some thoughts.

John Bonnett

-----Original Message-----
From: Amit_Wadhwa@stripped [mailto:Amit_Wadhwa@stripped] 
Sent: Wednesday, 11 August 2004 4:53 AM
To: win32@stripped
Subject: Calling a Stored Procedure crashing MySQL!!


Hi All,

Im a Newbie with Cursors,

What I basically need to do is:

Execute one SQL Statement:
Loop
    For every Record, execute a query based on results of the first query
            Loop
                   Check a Condition based on these results.
                    If (condition)
                        Execute Another Query
                    Else 
                        Execute Another Query
            End Loop
End Loop                       

Currently am using JDBC/Java Program to do the above in 10 Minutes for over
400,000 Records. But am experimenting with Cursors to see if it can be done
any faster.

The Procedure is given below.
It Stores Fine. But when I call it, my MySqld-NT crashes immediately.(...has
caused an error and needs to close)

Any Help is greatly Appreciated!

Regards,

Amit Wadhwa

CREATE PROCEDURE calculatehits()

BEGIN

DECLARE cur1 CURSOR FOR SELECT f1,f2,f3,f4 FROM t1 WHERE f5 IS NOT NULL AND
f6 NOT IN ('CNL') AND f7 <> '0' AND f8<> 'null' ; DECLARE a CHAR(16);
DECLARE b,c DATETIME; DECLARE d int(16); DECLARE e int(16); OPEN cur1;
REPEAT FETCH cur1 INTO a, b,c,d; SELECT f9 INTO @e FROM t1 WHERE f1= @a and
f2 > @b And f3 <= @c AND f4 NOT IN ('CNL') AND f5 IS NOT NULL AND f6 <>
'null'; IF d=e THEN //do something here END IF; UNTIL done END REPEAT; CLOSE
cur1;

END

Thread
Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
Re: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
RE: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!jbonnett11 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa11 Aug